Partitioning
1.) Global index always prefixed
2.) global index only be created on range and list partitioning
3.) global index can be partitioned or non-partitioned
4.) global index is good for OLTP where users looks for response.
create index test_id on test(c1)
global partition by hash(c1) --> not allowed to use any other column
partitions 4 store in (part)
Local Index
=========
1.) Local index can be prefixed or non-prefixed
2.) local index always be created on partition key by default.
3.) local index can be partition** and non-partitioned.
**
SQL> create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1 values less than (10),
5 partition ti2 values less than (100)); --> Not allowed in local
partition ti1 values less than (10),
*
ERROR at line 4:
ORA-14010: this physical attribute may not be specified for an index partition
1 create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1,
5* partition ti2)
SQL> /
create index lc_test_idx on test(c3) --> partition must be same as tables partitions
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
1 create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1,
5 partition ti2,
6 partition ti3,
7 partition ti4
8* )
SQL> /
Index created.
Reference Partitioning
=================
1.) The referenced table can be automatically partitioned if the primary table is partitioned with the primary key and is used into reference table.
2.) The foreign key reference column must be set to NOT NULL, otherwise you have error.
1 create table refp (c1 number, c2 number, c3 number, c4 number, constraint c1_pk primary key (c1))
2 partition by range(c1)
3 (
4 partition p1 values less than (10),
5 partition p2 values less than (100),
6 partition p3 values less than (1000),
7* partition p4 values less than (10000))
SQL> /
Table created.
SQL>
SQL> create table refp2 (c1 number, c5 number, c6 number, constraint c1_fk foreign key (c1) references refp(c1))
2 partition by reference(c1_fk);
partition by reference(c1_fk) --> not null is missing
*
ERROR at line 2:
ORA-14652: reference partitioning foreign key is not supported
1 create table refp2 (c1 number not null, c5 number, c6 number, constraint c1_fk foreign key (c1) references refp(c1))
2* partition by reference(c1_fk)
SQL> /
Table created.
Composite partitioning=================
1.) range - hash
create table rangehash (prod_id number, cust_id number, time_id date, ord_id number)
tablespace part
partition by range (time_id) subpartition by hash(cust_id)
subpartitions 8 store in (ts1,ts2,ts3,ts4)
(
partition rh1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1,
partition rh2 values less than (to_date('01-jul-2012','DD-MON-YYYY')) tablespace ts2,
partition rh3 values less than (to_date('01-oct-2012','DD-MON-YYYY')) tablespace ts3,
partition rh4 values less than (to_date('01-jan-2013','DD-MON-YYYY')) tablespace ts4
)
create tablespace ts1 datafile '/oradata/dr/pr/ts01.dbf' size 10m;
create tablespace ts2 datafile '/oradata/dr/pr/ts02.dbf' size 10m;
create tablespace ts3 datafile '/oradata/dr/pr/ts03.dbf' size 10m;
create tablespace ts4 datafile '/oradata/dr/pr/ts04.dbf' size 10m;
2.)Range-List
create table rangelist (tx date, state varchar2(5))
partition by range (tx) subpartition by list(state)
(partition rl1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1
(
subpartition rl1north values ('HP','UP','MP') tablespace ts1,
subpartition rl1east values ('WB','TR','NG') tablespace ts2,
subpartition rl1west values ('MH','GA','GJ') tablespace ts3,
subpartition rl1south values ('KA','KR','TN') tablespace ts4
),
partition rl2 values less than (to_date('01-Jul-2012','DD-MON-YYYY')) tablespace ts2
(
subpartition rl2north values ('HP','UP','MP') tablespace ts1,
subpartition rl2east values ('WB','TR','NG') tablespace ts2,
subpartition rl2west values ('MH','GA','GJ') tablespace ts3,
subpartition rl2south values ('KA','KR','TN') tablespace ts4
),
partition rl3 values less than (to_date('01-OCT-2012','DD-MON-YYYY')) tablespace ts3
(
subpartition rl3north values ('HP','UP','MP') tablespace ts1,
subpartition rl3east values ('WB','TR','NG') tablespace ts2,
subpartition rl3west values ('MH','GA','GJ') tablespace ts3,
subpartition rl3south values ('KA','KR','TN') tablespace ts4
),
partition rl4 values less than (to_date('01-JAN-2013','DD-MON-YYYY')) tablespace ts4
(
subpartition rl4north values ('HP','UP','MP') tablespace ts1,
subpartition rl4east values ('WB','TR','NG') tablespace ts2,
subpartition rl4west values ('MH','GA','GJ') tablespace ts3,
subpartition rl4south values ('KA','KR','TN') tablespace ts4
)
)enable row movement;
3.)Interval Range - Hash
CREATE TABLE inthash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY hash(cust_id)
SUBPARTITION template
( SUBPARTITION p1 TABLESPACE ts1
, SUBPARTITION p2 TABLESPACE ts2
, SUBPARTITION p3 TABLESPACE ts3
, SUBPARTITION P4 TABLESPACE ts4
)
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
) PARALLEL;
Interval - Range partitions template
create table ir(id date, jd number)
partition by range(id) interval (numtodsinterval (1,'DAY'))
subpartition by range(jd)
subpartition template
(
subpartition irs1 values less than (10) tablespace ts1,
subpartition irs2 values less than (20) tablespace ts2,
subpartition irs3 values less than (30) tablespace ts3,
subpartition irs4 values less than (40) tablespace ts4
)
(
partition ir1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1,
partition ir2 values less than (to_date('01-jul-2012','DD-MON-YYYY')) tablespace ts2,
partition ir3 values less than (to_date('01-oct-2012','DD-MON-YYYY')) tablespace ts3,
partition ir4 values less than (to_date('01-jan-2013','DD-MON-YYYY')) tablespace ts4
);
Range - Hash with template
create table emp (deptno number, empname varchar2(20),salary number)
partition by range (deptno) subpartition by hash(empname)
subpartition template
(
subpartition es1 tablespace ts1,
subpartition es2 tablespace ts2,
subpartition es3 tablespace ts3,
subpartition es4 tablespace ts4
)
(
partition emp1 values less than (10),
partition emp2 values less than (50),
partition emp3 values less than (100),
partition emp4 values less than (maxvalue)
);
Multi-column partitioning
===================
create table supp (sup_id number, part_id number, quantity number)
partition by range (sup_id,part_id)
(
partition s1 values less than (20,100) tablespace ts1,
partition s2 values less than (30,150) tablespace ts2,
partition s3 values less than (maxvalue,maxvalue) tablespace ts3
) enable row movement;
virtual columns partitioning
====================
1.) virtual column partitioning cannot be used for PL/SQL calls
2.) All other partitioning are possible including composite.
create table vc (pid number, ps number, tps as (pid*ps))
partition by range (pid) subpartition by range(tps)
subpartition template
(
subpartition vss1 values less than (40) tablespace ts1,
subpartition vss2 values less than (60) tablespace ts2,
subpartition vss3 values less than (maxvalue) tablespace ts4
)
(partition vs1 values less than (10) tablespace ts1,
partition vs2 values less than (100) tablespace ts2,
partition vs3 values less than (1000) tablespace ts3
)
Note::- tps is virtual column
Deferred segment creation with partition
==============================
1.) 11gR2 new feature
2.) use segment creation deferred or segment creation immediate --> allocate one extent
Index Organized Table
=================
create table dept
(deptno number not null,
deptname varchar2(10),
de varchar2(10),
loc varchar2(10) not null,
constraint iot_pk primary key (deptno,loc)
)
organization index
including loc
overflow tablespace ts4
partition by range (deptno)
(partition d1 values less than (10) tablespace ts1,
partition d2 values less than (20) tablespace ts2,
partition dn values less than (maxvalue) tablespace ts3
);
Exchnage partitons,
===============
alter table <part_tab_name> exchange partition <partition_name> with table table <table_name>
without validation update global indexes;
Note: you cannot exchange the subpartitions with simple table, this require the simple table to be created into partition table same as your base table, then you can exchange.
Split Partiton,
==========
alter table <part_table_name> split partition <partition_name> at (split_values> into (partition <new_partition_name1>, partition <new_partition_name2>);
Merge Partition:
============alter table <part_table_name> merge partitions <partition_name1>,<partition_name2> into partition <new_partition_name> tablespace <tablespace_name>
1.) Global index always prefixed
2.) global index only be created on range and list partitioning
3.) global index can be partitioned or non-partitioned
4.) global index is good for OLTP where users looks for response.
create index test_id on test(c1)
global partition by hash(c1) --> not allowed to use any other column
partitions 4 store in (part)
Local Index
=========
1.) Local index can be prefixed or non-prefixed
2.) local index always be created on partition key by default.
3.) local index can be partition** and non-partitioned.
**
SQL> create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1 values less than (10),
5 partition ti2 values less than (100)); --> Not allowed in local
partition ti1 values less than (10),
*
ERROR at line 4:
ORA-14010: this physical attribute may not be specified for an index partition
1 create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1,
5* partition ti2)
SQL> /
create index lc_test_idx on test(c3) --> partition must be same as tables partitions
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
1 create index lc_test_idx on test(c3)
2 local
3 (
4 partition ti1,
5 partition ti2,
6 partition ti3,
7 partition ti4
8* )
SQL> /
Index created.
Reference Partitioning
=================
1.) The referenced table can be automatically partitioned if the primary table is partitioned with the primary key and is used into reference table.
2.) The foreign key reference column must be set to NOT NULL, otherwise you have error.
1 create table refp (c1 number, c2 number, c3 number, c4 number, constraint c1_pk primary key (c1))
2 partition by range(c1)
3 (
4 partition p1 values less than (10),
5 partition p2 values less than (100),
6 partition p3 values less than (1000),
7* partition p4 values less than (10000))
SQL> /
Table created.
SQL>
SQL> create table refp2 (c1 number, c5 number, c6 number, constraint c1_fk foreign key (c1) references refp(c1))
2 partition by reference(c1_fk);
partition by reference(c1_fk) --> not null is missing
*
ERROR at line 2:
ORA-14652: reference partitioning foreign key is not supported
1 create table refp2 (c1 number not null, c5 number, c6 number, constraint c1_fk foreign key (c1) references refp(c1))
2* partition by reference(c1_fk)
SQL> /
Table created.
Composite partitioning=================
1.) range - hash
create table rangehash (prod_id number, cust_id number, time_id date, ord_id number)
tablespace part
partition by range (time_id) subpartition by hash(cust_id)
subpartitions 8 store in (ts1,ts2,ts3,ts4)
(
partition rh1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1,
partition rh2 values less than (to_date('01-jul-2012','DD-MON-YYYY')) tablespace ts2,
partition rh3 values less than (to_date('01-oct-2012','DD-MON-YYYY')) tablespace ts3,
partition rh4 values less than (to_date('01-jan-2013','DD-MON-YYYY')) tablespace ts4
)
create tablespace ts1 datafile '/oradata/dr/pr/ts01.dbf' size 10m;
create tablespace ts2 datafile '/oradata/dr/pr/ts02.dbf' size 10m;
create tablespace ts3 datafile '/oradata/dr/pr/ts03.dbf' size 10m;
create tablespace ts4 datafile '/oradata/dr/pr/ts04.dbf' size 10m;
2.)Range-List
create table rangelist (tx date, state varchar2(5))
partition by range (tx) subpartition by list(state)
(partition rl1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1
(
subpartition rl1north values ('HP','UP','MP') tablespace ts1,
subpartition rl1east values ('WB','TR','NG') tablespace ts2,
subpartition rl1west values ('MH','GA','GJ') tablespace ts3,
subpartition rl1south values ('KA','KR','TN') tablespace ts4
),
partition rl2 values less than (to_date('01-Jul-2012','DD-MON-YYYY')) tablespace ts2
(
subpartition rl2north values ('HP','UP','MP') tablespace ts1,
subpartition rl2east values ('WB','TR','NG') tablespace ts2,
subpartition rl2west values ('MH','GA','GJ') tablespace ts3,
subpartition rl2south values ('KA','KR','TN') tablespace ts4
),
partition rl3 values less than (to_date('01-OCT-2012','DD-MON-YYYY')) tablespace ts3
(
subpartition rl3north values ('HP','UP','MP') tablespace ts1,
subpartition rl3east values ('WB','TR','NG') tablespace ts2,
subpartition rl3west values ('MH','GA','GJ') tablespace ts3,
subpartition rl3south values ('KA','KR','TN') tablespace ts4
),
partition rl4 values less than (to_date('01-JAN-2013','DD-MON-YYYY')) tablespace ts4
(
subpartition rl4north values ('HP','UP','MP') tablespace ts1,
subpartition rl4east values ('WB','TR','NG') tablespace ts2,
subpartition rl4west values ('MH','GA','GJ') tablespace ts3,
subpartition rl4south values ('KA','KR','TN') tablespace ts4
)
)enable row movement;
3.)Interval Range - Hash
CREATE TABLE inthash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY hash(cust_id)
SUBPARTITION template
( SUBPARTITION p1 TABLESPACE ts1
, SUBPARTITION p2 TABLESPACE ts2
, SUBPARTITION p3 TABLESPACE ts3
, SUBPARTITION P4 TABLESPACE ts4
)
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
) PARALLEL;
Interval - Range partitions template
create table ir(id date, jd number)
partition by range(id) interval (numtodsinterval (1,'DAY'))
subpartition by range(jd)
subpartition template
(
subpartition irs1 values less than (10) tablespace ts1,
subpartition irs2 values less than (20) tablespace ts2,
subpartition irs3 values less than (30) tablespace ts3,
subpartition irs4 values less than (40) tablespace ts4
)
(
partition ir1 values less than (to_date('01-apr-2012','DD-MON-YYYY')) tablespace ts1,
partition ir2 values less than (to_date('01-jul-2012','DD-MON-YYYY')) tablespace ts2,
partition ir3 values less than (to_date('01-oct-2012','DD-MON-YYYY')) tablespace ts3,
partition ir4 values less than (to_date('01-jan-2013','DD-MON-YYYY')) tablespace ts4
);
Range - Hash with template
create table emp (deptno number, empname varchar2(20),salary number)
partition by range (deptno) subpartition by hash(empname)
subpartition template
(
subpartition es1 tablespace ts1,
subpartition es2 tablespace ts2,
subpartition es3 tablespace ts3,
subpartition es4 tablespace ts4
)
(
partition emp1 values less than (10),
partition emp2 values less than (50),
partition emp3 values less than (100),
partition emp4 values less than (maxvalue)
);
Multi-column partitioning
===================
create table supp (sup_id number, part_id number, quantity number)
partition by range (sup_id,part_id)
(
partition s1 values less than (20,100) tablespace ts1,
partition s2 values less than (30,150) tablespace ts2,
partition s3 values less than (maxvalue,maxvalue) tablespace ts3
) enable row movement;
virtual columns partitioning
====================
1.) virtual column partitioning cannot be used for PL/SQL calls
2.) All other partitioning are possible including composite.
create table vc (pid number, ps number, tps as (pid*ps))
partition by range (pid) subpartition by range(tps)
subpartition template
(
subpartition vss1 values less than (40) tablespace ts1,
subpartition vss2 values less than (60) tablespace ts2,
subpartition vss3 values less than (maxvalue) tablespace ts4
)
(partition vs1 values less than (10) tablespace ts1,
partition vs2 values less than (100) tablespace ts2,
partition vs3 values less than (1000) tablespace ts3
)
Note::- tps is virtual column
Deferred segment creation with partition
==============================
1.) 11gR2 new feature
2.) use segment creation deferred or segment creation immediate --> allocate one extent
Index Organized Table
=================
create table dept
(deptno number not null,
deptname varchar2(10),
de varchar2(10),
loc varchar2(10) not null,
constraint iot_pk primary key (deptno,loc)
)
organization index
including loc
overflow tablespace ts4
partition by range (deptno)
(partition d1 values less than (10) tablespace ts1,
partition d2 values less than (20) tablespace ts2,
partition dn values less than (maxvalue) tablespace ts3
);
Exchnage partitons,
===============
alter table <part_tab_name> exchange partition <partition_name> with table table <table_name>
without validation update global indexes;
Note: you cannot exchange the subpartitions with simple table, this require the simple table to be created into partition table same as your base table, then you can exchange.
Split Partiton,
==========
alter table <part_table_name> split partition <partition_name> at (split_values> into (partition <new_partition_name1>, partition <new_partition_name2>);
Merge Partition:
============alter table <part_table_name> merge partitions <partition_name1>,<partition_name2> into partition <new_partition_name> tablespace <tablespace_name>
No comments:
Post a Comment