ALL ABOUT ORACLE PARTITIONING

ALL ABOUT ORACLE PARTITIONING

The following lists the advantages of table partitioning:

1. Partitioning enables data management operations such as data loads, index creation and rebuilding and backup/recovery at the partition level rather than on the entire table. This results in a reduced time for these operations.
2. Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
3. Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations. Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
4. Partitioning increases the availability of mission-critical database if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery time and impact failures.
5. Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to partitioned tables without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

Partition Key:
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row.

• Consists of an ordered list of 1 to 16 columns
• Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID
• Can contain columns that are NULLable
Partitioned Tables:

Tables can be partitioned into up to 64,000 separate partitions.
Any table can be partitioned except those tables containing columns with LONG and LON RAW.

Partitioning Method:

Based on our requirement for Data archiving and data lifecycle management along with ease of administration, Range Partitioning will be the Best option.
But otherwise, there are the following partitioning methods available:
List Partitioning, Hash Partitioning, Composite Partitioning

List, Range, and Hash Partitioning

Range partitioning maps the data based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.

The following lists the step by step procedure to create a Range Partitioned Table and the indexes on it.

1. Create a partitioned test table PURCHASE_DOCUMENT_PARTITIONED from PURCHASE_DOCUMENT

To test the partitioning we will use the following syntax to create a new partitioned table:

create table EC1.PURCHASE_DOCUMENT_PARTITIONED
(
  ID                       NUMBER(11) not null,
  INVOICE_NO               VARCHAR2(50),
  PURCHASE_ID              NUMBER(11) not null,
  PURCHASEDOCUMENTTYPE_ID2 CHAR(7),
  DATA                     BLOB,
  SECRET                   VARCHAR2(20 CHAR),
  REIMBURSEMENT_ID         NUMBER(11),
  DOCUMENT_DATE            TIMESTAMP(6) not null,
  CTIME                    TIMESTAMP(6) default systimestamp not null,
  MTIME                    TIMESTAMP(6) default systimestamp not null,
  MINFO                    VARCHAR2(4000),
  MUSERACCOUNT_ID          NUMBER(15),
  CUSERACCOUNT_ID          NUMBER(15),
  IS_SIGNED                NUMBER(1) default 0 not null,
  DUE_DATE                 TIMESTAMP(6),
  PURCHASEDOCUMENTTYPE_ID  CHAR(3) not null,
  PAYMENTDELINQUENCY_LEVEL NUMBER(2) default 0 not null,
  VAT_ID                   VARCHAR2(30))
PARTITION BY RANGE (CTIME)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
PCTUSED 99 PCTFREE 1 STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
PCTUSED 40 PCTFREE 10 STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
)

2. Create dummy1, dummy2, dummy3

We have so far decided upon creating 3 partitions. Hence 3 dummy tables need to be created.

Create table ec1.dummy1 as select * from ec1.purchase_document 
where ctime >= to_date('01.01.2005','DD.MM.YYYY') and ctime = to_date('01.01.2006','DD.MM.YYYY') and ctime = to_date('01.01.2007','DD.MM.YYYY') and ctime = to_date('01.06.2009','DD.MM.YYYY') and ctime = to_date('01.06.2009','DD.MM.YYYY') and ctime <= to_date('05.06.2009','DD.MM.YYYY')

4. Creating primary key partitioned indexes.

Primary key partitioned index could be Local or Global.
Step 1: First create a unique index. We will try to create a local index.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

But we decide not to use ‘CTIME’ as a primary key but ‘ID’ as the primary key.

Drop Index EC1.PURCHASE_DOC_PK_IDX;

Rerun the local index creation with the index on ID.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ID)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

ERROR:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE Index
Hence we shall create either Global Partitioned Unique Index or Global Non Partitioned Unique Index in such a column as ID. Here we are using non partitioned Global Index. Global Partitioned index cannot be created on a non prefixed column.

CREATE UNIQUE INDEX EC1.PURCHASE_DOC_PK_IDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ID)
 GLOBAL
TABLESPACE CB_DATA_SLOW
pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Step 2: Second Create the Primary key on the same column. The Primary key will utilize the underlying index.

ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PARTITIONED_PK
PRIMARY KEY (ID);

5. Create the Remaining Unique Indexes.

 CREATE UNIQUE INDEX EC1.PURCHASE_DOCUMENT_UK_INDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED(INVOICE_NO, SECRET)
 GLOBAL
 TABLESPACE CB_DATA_SLOW
 pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PARTITIONED_UK
UNIQUE (INVOICE_NO, SECRET);

 CREATE UNIQUE INDEX EC1.PURCHASE_DOC_INVOICENO_UK_INDX 
 ON EC1.PURCHASE_DOCUMENT_PARTITIONED(INVOICE_NO, PURCHASEDOCUMENTTYPE_ID)
 GLOBAL
 TABLESPACE CB_DATA_SLOW
 pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
ALTER TABLE EC1.PURCHASE_DOCUMENT_PARTITIONED ADD CONSTRAINT PURCHASE_DOC_PART_INVOICENO_UK
UNIQUE (INVOICE_NO, PURCHASEDOCUMENTTYPE_ID);

6. Create the Foreign Keys and Checks.

alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PURCHASE_DOC_PART_FK_PURCHASE foreign key (PURCHASE_ID)
  references EC1.PURCHASE (ID);
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PURCHASE_DOC_PART_FK_TYPE foreign key (PURCHASEDOCUMENTTYPE_ID)
  references EC1.PURCHASEDOCUMENTTYPE (ID);
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint PUR_DOC_FK_REIMBURSEMENT foreign key (REIMBURSEMENT_ID)
  references EC1.REIMBURSEMENT (ID);
-- Create/Recreate check constraints 
alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  add constraint INVOICE_NO_DOCTYP_CHECK
  check ("INVOICE_NO" IS NOT NULL AND "PURCHASEDOCUMENTTYPE_ID" in ('PDN', 'RFP', 'CHB', 'INV', 'REF', 'REC', 'OCO', 'QUO', 'TST', 'TSR') OR "INVOICE_NO" IS NULL AND "PURCHASEDOCUMENTTYPE_ID" NOT in ('PDN', 'RFP', 'CHB', 'INV', 'REF', 'REC', 'OCO', 'QUO', 'TST', 'TSR'));

7. Build Local Partitioned Index on the ctime column which also happens to be a partitioned key.

CREATE INDEX EC1.PURCHASE_DOC_PARTITIONED_CTIME  ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

The syntax for creating global partitioned Index (although we don’t use it) is as followes:

CREATE INDEX EC1.PURCHASE_DOC_PARTITIONED_CTIME  ON EC1.PURCHASE_DOCUMENT_PARTITIONED (ctime)
     GLOBAL 
     PARTITION BY RANGE (CTIME)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
)

8. Build local index on the PURCHASE_ID and PURCHASEDOCUMENTTYPE_ID columns. (Non Prefixed)

create index EC1.PURCHASE_DOC_PART_PURCHASE on EC1.PURCHASE_DOCUMENT_PARTITIONED (PURCHASE_ID, PURCHASEDOCUMENTTYPE_ID)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
  TABLESPACE CB_DATA_SLOW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Alternatively Build Global Non Partitioned Index.

create index EC1.PURCHASE_DOC_PART_PURCHASE on EC1.PURCHASE_DOCUMENT_PARTITIONED (PURCHASE_ID, PURCHASEDOCUMENTTYPE_ID)
  GLOBAL
    TABLESPACE CB_DATA_SLOW
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

OR Global Partitioned Index (Prefixed)

create index EC1.PURCHASE_DOC_PARTITIONED_DATE on EC1.PURCHASE_DOCUMENT_PARTITIONED (DOCUMENT_DATE)
GLOBAL
PARTITION BY RANGE (DOCUMENT_DATE)
(
partition y2005 values less than (to_date('01.01.2006','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2006 values less than (to_date('01.01.2007','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2007 values less than (to_date('01.01.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2008q1 values less than (to_date('01.04.2008','DD.MM.YYYY') ) tablespace cb_data_slow
STORAGE(INITIAL 100M NEXT 5M minextents 1 maxextents unlimited),
partition y2010 values less than (MAXVALUE)
tablespace cb_data_med
STORAGE(INITIAL 100 M NEXT 2M minextents 1 maxextents unlimited)
);

NOTE: Remember that the column to be indexed and the partition key of the index should be same in case of Global Partitioned Index.
OR Local Partititioned Index (local non prefixed indexes)

create index EC1.PURCHASE_DOC_PARTITIONED_DATE on EC1.PURCHASE_DOCUMENT_PARTITIONED (DOCUMENT_DATE)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

create index EC1.PURCHASE_DOC__PART_RP on EC1.PURCHASE_DOCUMENT_PARTITIONED (REIMBURSEMENT_ID, PURCHASEDOCUMENTTYPE_ID)
  LOCAL
     (partition Y2005,
      partition Y2006,
      partition Y2007,
      partition Y2008Q1,
      partition Y2010)
      TABLESPACE CB_DATA_SLOW
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
       initial 64K
        minextents 1
         maxextents unlimited
      );

Few Points to remember:
Local Index could be prefixed or non prefixed.
Global index cannot be non prefixed and always prefixed.
Creation of Unique Local Index on a different column other than partitioned column is not possible. In those cases Global Indexes need to be used.
Use Local Indexes for Data Warehouse and DSS systems. Use Global Indexes for the OLTP system. Its easy to do maintenance on the local indexes than Global, as any changes to the partition structure, Global Indexes need be rebuilt.

9. Rename partition.

Alter table ec1.purchase_document_partitioned rename partition y2010 to maxvalue1;

10. Split Partition

11. Move the partition to different tablespace.

 Alter table EC1.PURCHASE_DOCUMENT_PARTITIONED
  Move Partition Y2006
  tablespace CB_DATA_SLOW

This invalidates the indexes. All global indexes (partitioned and non partitioned) are invalidated along with the partitioned column whose data was moved.
Run :

  Alter index EC1.PURCHASE_DOC_PARTITIONED_DATE 
  rebuild partition Y2010 online

Run alter table reduild index index_name online; for all non partitioned indexes on a partitioned tables.

12. Drop the partition. Check how the indexes are getting affected.
Dropping partition also invalidates the global indexes.

13. Run Gather Stats.

NOTE: Understanding PCTFREE and PCTUSED .

In a create table statement there is a clause for pctfree and pctused which are the block usage parameters. PCTUSED is a parameter which makes a block eligible for insert or update. Like the default setting for PCTUSED is 40%, the moment the block utilization drops to below 40% the block is relinked to the freelist. This implies that the block is eligible for further inserts or update until it reaches PCTFREE value. Lets say PCTFREE is 10%, The block continues to accept values until only 10% remain. This 10% is reserved for further growth of the block due to updates.

In a datawarehouse scenario where the block willl not be updated at all, the PCTFREE could be 0 and PCTUSED could be 100.

Some Glossary of SQLs:

Select * from dba_tab_partitions
where table_owner='EC1' 
     
Select * from dba_part_tables
where owner='EC1'

Select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

Select * from ec1.purchase_document_partitioned 
partition (Y2008q1)
where ID=2064811

SELECT *
FROM dba_ind_partitions
where index_owner='EC1'
 ORDER BY index_name, partition_name;

Select * from dba_part_indexes where owner='EC1';

Select * from dba_tables where table_name='PURCHASE_DOCUMENT'
select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

select table_name, partition_name, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, 
  PCT_FREE, PCT_USED, INI_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENT, 
  PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL 
  from dba_tab_partitions
  where table_name='PURCHASE_DOCUMENT_PARTITIONED'  

  Select * from dba_constraints where table_name='PURCHASE_DOCUMENT_PARTITIONED'
  
  Select * from dba_indexes where table_name='PURCHASE_DOCUMENT_PARTITIONED'

Advertisement