Database Design

System partitioned tables

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Thursday, 18 March 2010 22:52
Last Updated on Friday, 25 October 2013 16:23
Published on Thursday, 18 March 2010 22:52
Written by Guy Lambregts
Hits: 4950

System Partitioned tables

 

See also Partitioned Tables Overview

 

New partition possibility from 11G R1 onwards  : System Partitioning.

 

System partitioning can be useful useful when

 

1. there is no obvious partition key

2. the table is even though very huge, you would like to breakdown the table into pieces

3. the partition is / can be referenced at the application coding level

 

An example ( in practice the partitioned tables contain more fields, I just use this example to show the DDL )


SQL> create table TABLE_TS1 (
     TS1_SHORT_DESCRIPTION varchar2(20) not null,
     TS1_LONG_DESCRIPTION  varchar2(1000) not null )
     partition by system
     ( partition TS1_P1 tablespace PM1,
       partition TS1_P2 tablespace PM2,
       partition TS1_P3 tablespace PM3 )  

 

SQL> insert into TABLE_TS1 values ('Oracle','Oracle is a well known database');

insert into TABLE_TS1 values ('Oracle','Oracle is a well known database')
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

SQL> insert into TABLE_TS1 partition (TS1_P1) values ('Oracle','Oracle is a well known database');

1 row created.

SQL> commit;

 

Some Remarks

 

1. For DML one needs to hard code the partition ( see above ) 

2. For select one doesn' t need to hard code the partition. However if for select statements the partition isn' t referenced, then every partition is accessed, there is thus no transparent partition pruning ( only achieved by hard coding the partition ).

3. Non-unique local indexes - B-Tree and bitmap - are supported

4. Unique local indexes - B-Tree and bitmap - are NOT SUPPORTED

5. Unique global indexes - B-Tree and bitmap - are supported.

6. Non-unique global indexes - B-Tree and bitmap - are supported.

7. Split partition is NOT SUPPORTED

8. CTAS - Create Table as - is NOT SUPPORTED

9. insert into <table> as <select query> is NOT SUPPORTED