Explain Oracle Partitioning. How it is helpfull in DWH env?

Oracle Partitioning

Oracle partitioning an option of oracle database can enhance the managebility, performance and availability.

Partitioning allow a table index & index organized table to be subdivided into smaller pieces. Each piece of database object is called a partition. Each partition has its own name and may optionally have its own storage.

Partitioning for the Managebility

We can divide Large TABLE or INDEX in smaller piece through partition option, we can take backup partition wise so it is reduce the maintanace, we can delete one partition from the table instead delete full table data.

Partitioning for the Performance

When large table is partitioned then we get performance benefits, suppose we have table with partitioned with range as per monthly data, so when we query particular month data then query search partition partition for the result instead of full table.

Partitioning for the Availability

When large table is partitioned and suppose of the partition is unavailable due to same reason then still we can query and all other partition is available for database.

Oracle 8i

Oracle partition introduce with Oracle 8 version and Oracle 8i provide three partition method.

1. Range partitioning

2. Hash partitoning

3. Composite partitioning

1. Range partitioning

Use range partitioning to map rows to partitions based on ranges of columns values. means we create partition based on range like month wise, year wise, day wise etc.

CREATE TABLE sales

( invoice_no NUMBER,

sale_year INT NOT NULL )

PARTITION BY RANGE (sale_year)

( PARTITION sales_q1 VALUES LESS THAN (1999),

PARTITION sales_q2 VALUES LESS THAN (2000),

PARTITION sales_q3 VALUES LESS THAN (2001),

PARTITION sales_q4 VALUES LESS THAN (2002));

We can use any column for "partitioning key" when partition on based. in above example is use sale_year based, all values under 1999 year will insert in partition "sales_q1".

2. Hash partitioning

When large table is not fit for range partition and we want better performance then we can use HASH partition.

In hash partition we can define no. of partition which oracle will create based on their algorithm. or when can store each partition on separate tablespace.

SQL> create table sales_test1

( sales_id number, sales number)

PARTITION BY HASH (sales_id) PARTITIONS 4;

Table created.

Above table create 4 partition for the SALES_ID column.

create table sales_test2

( sales_id number, sales number)

PARTITION BY HASH (sales_id)

( PARTITION p1 tablespace tbs1,

PARTITION p2 tablespace tbs2);

Above table created with two partition and each partition will store on separate tablespace.

3. Composite Range-Hash partitioning

Basically this is combination of RANGE and HASH partitioning option. In this first we partition table through RANGE and subdivide with HASH partitioning.

create table sales_test3

(sales_id number, sales number,sales_year number)

PARTITION BY RANGE (sales_year)

SUBPARTITION BY HASH (sales)

SUBPARTITIONS 2 STORE in ( tbs1, tbs2)

(PARTITION p1 values less than (1999),

PARTITION p2 values less than (2000))

Above table is first partition by RANGE on sales_year column then SUBPARTITION by HASH on SALES column with 2 partition and each on store in separate tablespace.

Oracle 9i (9.0.1)

Oracle Introduce one more feature in series of partitioning.

4. LIST partitioning

Up to now we see range, hash & composite partitioning option.

Through list partitiong we can explicit control on rows to map with partitions.

Suppose we have large table their one column is for color status and rows inserted based on that column, color column have three distinct values that is "blue,red,black". now we want to create partition on this distinct values, In this condition RANGE or HASH partition option will not work. so we use LIST partition

create table color_test

(colorid number,color char(10))

PARTITION BY LIST (color)

(PARTITION c1 values ('BLUE'),

PARTITION c2 values ('RED'),

PARTITION c3 values ('BLACK'));

Above table created with LIST partition based on COLOR column which explicity map with partition.

Oracle9i (9.2.0)

Oracle introduce one more feature in series of partitioning

5. Composite RANGE-LIST partitioning

It is same like composite RANGE-HASH partitioning method.

First table partition by RANGE then subpartition by LIST method.

create table sales_test4

( salesid number, sales number, status varchar2(30), salesdate date)

PARTITION BY RANGE (salesdate)SUBPARTITION BY LIST (status)(PARTITION q1_0001 VALUES LESS THAN (to_date('01-APR-2008','DD-MON-YYYY'))

(SUBPARTITION q1_0001_a VALUES ('A'),

SUBPARTITION q1_0001_b VALUES ('B'),

SUBPARTITION q1_0001_c VALUES ('C')))

Above table first partition by RANGE Method on salesdate column then subpartition by LIST on status column.

Oracle10g (10.1.0)

In 10g release 1 no new method is introduce but oracle enhancement existing method as follows

1. Partition management enhanced with OEM.

2. We can use LIST/HASH/RANGE partition method for Index Organized table.

3. LOB columns are supported all types of partition method

4. Oracle now automatically maintains global indexes when DDL operation are executed against IOT's tables

Oracle10g (10.2.0)

10gr2 again no new feature introdure but oracle enhanced all existing features for better performance, managebility.

1. Online Redefinition of a single partition is now possible

Oracle provides a mechanism to move a partition or to take other changes to the partition's physical structure without significantly affecting the availibility of the partition of DML. The mechanism is called Online table redefinition.

2. Increased maximum number of partitions per object, before it was 64k-1 now it is 1024k-1.

Oracle11g (11.1.0)

Oracle 11g comes with some more new feature for partitioning

6. Interval partitioning

7. Reference partitioning

8. Composite Range-Range partitioning

9. Composite List-Range partitioning

10. Composite List-Hash partitioning

11. Composite List-List partitioning

12. System partitioning

13. Virtual column partitioning

1. Interval partitioning

In range partition we have to explicity define range for rows to map with partition. And if any new inserted row is not mapped with existing partition range then we getting ORA-14400 inserted partition key does not map to any partition to prevent this error we can use INTERVAL partition. which create SYSTEM generated names partition when new rows not mapped with any existing partition.

SQL> create table TEST

2 ( no number ,

3 name varchar2(20))

4 PARTITION BY RANGE (no)

5 (partition p1 values less than (100),

6 partition p2 values less than (200));
Table created.

SQL> insert into test values (100,'a');
1 row created.

SQL> insert into test values (200,'b');

insert into test values (200,'b')

*

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

To prevent above error we can use INTERVAL partition option like below

SQL> create table TEST1

2 ( no number,

3 name varchar2(20))

4 PARTITION BY RANGE(no) INTERVAL (100)

5 (partition p1 values less than (100),

6 partition p2 values less than (200));
Table created.

SQL> insert into test1 values (100,'a');
1 row created.

SQL> insert into test1 values (200,'b');
1 row created.

SQL> select table_name,partition_name

2 from user_tab_partitions

3 where table_name='TEST1';
TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

TEST1 P1

TEST1 P2

TEST1 SYS_P41

Now this time no error come and one new partition automatically created with SYSTEM generated name.

7. Reference partitioning

This is new feature and very good benefits from this.

Suppose we have two tables 1. parent and 2. child

Parent table

create table parent

( no number primary key, name varchar2(20))

PARTITION BY RANGE (no)

(partition p1 values less than (100));

Child table

create table child

(childno number NOT NULL,

cname varchar2(20),

constraint fkno foreign key (childno) references parent(no) )

PARTITION BY REFERENCE (fkno);

Parent table is partition by RANGE method on NO Column and if we want same manner partition on child table but it is not possible becuase child table DON'T have NO column so prevent this problem we can use REFERENCE by partition option like above.

NOTE: foreign key column in child table must be NOT NULL otherwise we are getting this error: ORA-14652: reference partitioning foreign key is not supported

8. System partitioning

One more cool feature is system partitioning, when table is not fit for any partition method then we can use system partitioning becuase system partition is not use any PARTITION KEY (range,hash,list). it is just divide table in physical way.

create table test

( no number,name varchar2(20))

PARTITION BY SYSTEM

( partition p1 tablespace tbs1,

partition p2 tablespace tbs2);

Note that there is no partition key or boundaries, table physically divide in two way.

SQL> insert into test values (1,'T');

insert into test values (1,'T')

*

ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs ontables partitioned by the System method

Why we are getting above error, becuase there is no partition key so we need to always mention partition name where rows insert.

SQL> insert into test partition (p1) values (1,'T');
1 row created.

NOTE: for delete or update we don't have to provide partition but when statement like below we need to specify partition name otherwise statement search full table for specific row.

SQL> delete test partition (p1) where no=1

9. Virtual column partitioning

It is very good feature becuase we can create partition on column which column doesn't exist in table.

create table result

( id number,

subject varchar2(20),

totalmarks number,

obtainmarks number);

In above table there is four column 1. id ,2. subject 3. totalmarks 4. obtainmarks and 5. is percentage (which doesn't exists) but it is calculated based on totalmarks and obtainmarks columns and we want partition based on parcentage column, In this case we can use VIRTUAL partition option.

create table result

( id number,

subject varchar2(20),

totalmarks number,

obtainmarks number,

percentage number

generated always as

( obtainmarks/totalmarks*100

) virtual )

partition by RANGE(percentage)

(partition plow values less than ('50'),

partition pmedium values less than ('70'),

partition phigh values less than ('100'))

Now inserting some rows and check

SQL> begin

2 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'a',100,90);

3 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'b',100,70);

4 insert into result (id,subject,totalmarks,obtainmarks) values (1001,'c',100,50);

5 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'a',100,90);

6 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'b',100,70);

7 insert into result (id,subject,totalmarks,obtainmarks) values (1002,'c',100,50);

8 end;

PL/SQL procedure successfully completed.

SQL> select * from result;
ID SUBJECT TOTALMARKS OBTAINMARKS PERCENTAGE

---------- -------------------- ---------- ----------- ----------

1001 c 100 50 50

1002 c 100 50 50

1001 a 100 90 90

1001 b 100 70 70

1002 a 100 90

90

1002 b 100 70 70
6 rows selected.