Partition Table
Hash Partitioning:
A hash algorithm is applied to the partitioning key to determine the partition for a given row
SQL> create tablespace part_1
datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_1.DBF' SIZE 1M;
Tablespace created.
SQL> create tablespace part_2
datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_2.DBF' SIZE 1M;
Tablespace created.
SQL> create tablespace part_3
datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_3.DBF' SIZE 1M;
Tablespace created.
SQL> create tablespace part_4
datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_4.DBF' SIZE 1M;
Tablespace created.
SQL> grant create session,create table to alex;
Grant succeeded.
SQL> create table hash_part(
prof_history_id number(10),
person_id number(10) not null,
organization_id number(10) not null,
record_date date not null,
prof_hist_comments varchar2(2000))
partition by hash(prof_history_id)
partitions 3
store in(part_1,part_2,part_3);
Table created.
SQL> select partition_name,tablespace_name
from user_tab_partitions
where table_name='HASH_PART';
PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------
SYS_P21 PART_1
SYS_P22 PART_2
SYS_P23 PART_3
Range Partitioning:
Each partition is specified by a range of values of the partitioning key (for a table with a date column as the partitioning key, the 'January-2001' partition contains rows with the partitioning-key values from '01-JAN-2001' - '31-JAN-2001')
SQL> create table range_part(
prof_history_id number(10),
person_id number(10) not null,
organization_id number(10) not null,
record_date date not null,
prof_hist_comments varchar2(2000))
partition by range(record_date)(
PARTITION yr0 VALUES LESS THAN(TO_DATE('01-JAN-2000','DD-MON-YYYY'))
tablespace part_1,
PARTITION yr1 VALUES LESS THAN(TO_DATE('01-JAN-2001','DD-MON-YYYY'))
tablespace part_2,
PARTITION yr2 VALUES LESS THAN(TO_DATE('01-JAN-2002','DD-MON-YYYY'))
tablespace part_3,
PARTITION yr4 VALUES LESS THAN(MAXVALUE) tablespace part_4
);
Table created.
SQL> insert into range_part values(1,1,10,'21-JAN-1999','good');
1 row created.
SQL> insert into range_part values(1,1,10,'01-JAN-2000','good');
1 row created.
SQL> insert into range_part values(3,3,30,'21-MAR-2001','very good');
1 row created.
SQL> insert into range_part values(4,4,40,'21-APR-2002','BAD');
1 row created.
SQL> insert into range_part values(5,5,50,'17-APR-2009','FINE');
1 row created.
SQL> select * from range_part partition(yr0);
PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DA PROF_HIST_COMMENTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 10 21-JAN-99 good
SQL> select * from range_part partition(yr1);
PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DA PROF_HIST_COMMENTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 10 01-JAN-00 good
SQL> select * from range_part partition(yr2);
PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DA PROF_HIST_COMMENTS
-------------------------------------------------------------------------------------------------------------------------------------------------------------
3 3 30 21-MAR-01 very good
SQL> select * from range_part partition(yr4);
PROF_HISTORY_ID PERSON_ID ORGANIZATION_ID RECORD_DA PROF_HIST_COMMENTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------
4 4 40 21-APR-02 BAD
5 5 50 17-APR-09 FINE
SQL> select table_name,tablespace_name,partitioned
from user_tables
where table_name='RANGE_PART';
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
RANGE_PART YES
SQL> select partition_name,tablespace_name,high_value
from user_tab_partitions
where table_name='RANGE_PART';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------------------------------------------------------------------------
YR0 PART_1 TO_DATE(' 2000-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR1 PART_2 TO_DATE(' 2001-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR2 PART_3 TO_DATE(' 2002-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR4 PART_4 MAXVALUE
Move Partition:
SQL> alter table range_part
move partition yr2
tablespace part_4;
Table altered.
SQL> select partition_name,tablespace_name,high_value
from user_tab_partitions
where table_name='RANGE_PART';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------------------------------------------------------------------------
YR0 PART_1 TO_DATE(' 2000-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR1 PART_2 TO_DATE(' 2001-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR2 PART_4 TO_DATE(' 2002-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
YR4 PART_4 MAXVALUE
Range Partition by Alpha:
SQL> create table students(
student_id number(6),
student_fn varchar2(25),
student_ln varchar2(25),
primary key(student_id))
partition by range(student_ln)(
partition student_ae values less than('F%') tablespace part_1,
partition student_fl values less than('M%') tablespace part_2,
partition student_mr values less than('S%') tablespace part_3,
partition student_sz values less than(maxvalue) tablespace part_4
);
Table created.
SQL> SELECT table_name, tablespace_name, partitioned
FROM user_tables
WHERE TABLE_NAME='STUDENTS';
TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------------------------------------
STUDENTS YES
SQL> SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE Table_name='STUDENTS';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------------------------------------------------------------------------------------------
STUDENT_AE PART_1 'F%'
STUDENT_FL PART_2 'M%'
STUDENT_MR PART_3 'S%'
STUDENT_SZ PART_4 MAXVALUE
LIST PARTTITION TABLE:
Each partition is specified by a list of values of the partitioning key (for a table with a region column as the partitioning key, the 'North America' partition may contain values 'Canada', 'USA', and 'Mexico')
SQL> CREATE TABLE list_part (
deptno NUMBER(10),
deptname VARCHAR2(20) ,
quarterly_sales NUMBER(10,2),
state VARCHAR2(3))
PARTITION BY LIST (state)(
PARTITION q1_northwest VALUES ('DK', 'CH') TABLESPACE part_1,
PARTITION q1_southwest VALUES ('KL', 'BR', 'MM') TABLESPACE part_2,
PARTITION q1_northeast VALUES ('SYL', 'BOG', 'JP') TABLESPACE part_1,
PARTITION q1_southeast VALUES ('TN', 'GA') TABLESPACE part_2,
PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part_1,
PARTITION q1_southcent VALUES ('OK', 'TX') TABLESPACE part_2);
SQL> SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE Table_name='LIST_PART';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------------------------------------------------------------------------------------------
Q1_NORTHWEST PART_1 'DK', 'CH'
Q1_SOUTHWEST PART_2 'KL', 'BR', 'MM'
Q1_NORTHEAST PART_1 'SYL', 'BOG', 'JP'
Q1_SOUTHEAST PART_2 'TN', 'GA'
Q1_NORTHCENT PART_1 'MN', 'WI'
Q1_SOUTHCENT PART_2 'OK', 'TX'
6 rows selected.
SQL> insert into list_part values(10,'CSE',30,'DK');
1 row created.
SQL> insert into list_part values(20,'BBA',30,'SYL');
1 row created.
SQL> select * from list_part;
DEPTNO DEPTNAME QUARTERLY_SALES STA
------------------------------------------------------------------------------------
10 CSE 30 DK
20 BBA 30 SYL
SQL> select * from list_part partition(q1_northwest);
DEPTNO DEPTNAME QUARTERLY_SALES STA
-----------------------------------------------------------------------------------
10 CSE 30 DK
SQL> select * from list_part partition(q1_northeast);
DEPTNO DEPTNAME QUARTERLY_SALES STA
-----------------------------------------------------------------------------------
20 BBA 30 SYL
SUB PARTITION (RANGE & LIST):
Composite Range-Hash Partitioning: A combination of the Range and Hash partitioning technique. The table is first range-partitioned, and then each individual range-partition is further sub-partitioned using the hash partitioning technique. All sub-partitions for a given range partition together represent a logical subset of the data.
SQL> CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part_1,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part_2,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part_3) (
PARTITION pert_1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION pert_2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITION pert_3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION part_4 VALUES LESS THAN(MAXVALUE));
Table created.
SQL> SELECT table_name,partition_name,subpartition_name,high_value
FROM user_tab_subpartitions
WHERE TABLE_NAME='COMPOSITE_RNG_LIST';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
-------------------------------------------------------------------------------------------------------------------------
COMPOSITE_RNG_LIST PART_4 PART_4_WEST 'OR', 'WA'
COMPOSITE_RNG_LIST PART_4 PART_4_EAST 'NY', 'CT'
COMPOSITE_RNG_LIST PART_4 PART_4_CENT 'OK', 'TX'
COMPOSITE_RNG_LIST PERT_1 PERT_1_WEST 'OR', 'WA'
COMPOSITE_RNG_LIST PERT_1 PERT_1_EAST 'NY', 'CT'
COMPOSITE_RNG_LIST PERT_1 PERT_1_CENT 'OK', 'TX'
COMPOSITE_RNG_LIST PERT_2 PERT_2_WEST 'OR', 'WA'
COMPOSITE_RNG_LIST PERT_2 PERT_2_EAST 'NY', 'CT'
COMPOSITE_RNG_LIST PERT_2 PERT_2_CENT 'OK', 'TX'
COMPOSITE_RNG_LIST PERT_3 PERT_3_WEST 'OR', 'WA'
COMPOSITE_RNG_LIST PERT_3 PERT_3_EAST 'NY', 'CT'
COMPOSITE_RNG_LIST PERT_3 PERT_3_CENT 'OK', 'TX'
12 rows selected.
SQL>CREATE TABLE range_list(
cust_id NUMBER(10),
channel_id NUMBER(3),
amount_sold NUMBER(10,2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST(channel_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 VALUES (2,3) TABLESPACE part_1,
SUBPARTITION sp2 VALUES (4,5) TABLESPACE part_2,
SUBPARTITION sp3 VALUES (6,7) TABLESPACE part_3,
SUBPARTITION sp4 VALUES (8,9) TABLESPACE part_4)
(PARTITION sp98 VALUES LESS THAN(TO_DATE( '01/01/1998' ,'DD/MM/YYYY' )),
PARTITION s98 VALUES LESS THAN(TO_DATE( '01/01/1999' ,'DD/MM/YYYY' )),
PARTITION s99 VALUES LESS THAN(TO_DATE( '01/01/2000' ,'DD/MM/YYYY' )),
PARTITION s2K VALUES LESS THAN(TO_DATE( '01/01/2001' ,'DD/MM/YYYY' )),
PARTITION s01 VALUES LESS THAN(TO_DATE( '01/01/2002' ,'DD/MM/YYYY' )),
PARTITION sf VALUES LESS THAN(MAXVALUE) );
Table created.
SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value
FROM user_tab_subpartitions
WHERE table_name ='RANGE_LIST';
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------------------------------------------------------------------------------------------------------
S01 S01_SP1 PART_1 2, 3
S01 S01_SP2 PART_2 4, 5
S01 S01_SP3 PART_3 6, 7
S01 S01_SP4 PART_4 8, 9
S2K S2K_SP1 PART_1 2, 3
S2K S2K_SP2 PART_2 4, 5
S2K S2K_SP3 PART_3 6, 7
S2K S2K_SP4 PART_4 8, 9
S98 S98_SP1 PART_1 2, 3
S98 S98_SP2 PART_2 4, 5
S98 S98_SP3 PART_3 6, 7
S98 S98_SP4 PART_4 8, 9
S99 S99_SP1 PART_1 2, 3
S99 S99_SP2 PART_2 4, 5
S99 S99_SP3 PART_3 6, 7
S99 S99_SP4 PART_4 8, 9
SF SF_SP1 PART_1 2, 3
SF SF_SP2 PART_2 4, 5
SF SF_SP3 PART_3 6, 7
SF SF_SP4 PART_4 8, 9
SP98 SP98_SP1 PART_1 2, 3
SP98 SP98_SP2 PART_2 4, 5
SP98 SP98_SP3 PART_3 6, 7
SP98 SP98_SP4 PART_4 8, 9
24 rows selected.
Marge partition:
SQL> ALTER TABLE range_list
MERGE SUBPARTITIONS sp98_sp1, sp98_sp2 INTO SUBPARTITION sp12
TABLESPACE part_1;
Table altered.
SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value
FROM user_tab_subpartitions
WHERE table_name ='RANGE_LIST';
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------
S01 S01_SP1 PART_1 2, 3
S01 S01_SP2 PART_2 4, 5
S01 S01_SP3 PART_3 6, 7
S01 S01_SP4 PART_4 8, 9
S2K S2K_SP1 PART_1 2, 3
S2K S2K_SP2 PART_2 4, 5
S2K S2K_SP3 PART_3 6, 7
S2K S2K_SP4 PART_4 8, 9
S98 S98_SP1 PART_1 2, 3
S98 S98_SP2 PART_2 4, 5
S98 S98_SP3 PART_3 6, 7
S98 S98_SP4 PART_4 8, 9
S99 S99_SP1 PART_1 2, 3
S99 S99_SP2 PART_2 4, 5
S99 S99_SP3 PART_3 6, 7
S99 S99_SP4 PART_4 8, 9
SF SF_SP1 PART_1 2, 3
SF SF_SP2 PART_2 4, 5
SF SF_SP3 PART_3 6, 7
SF SF_SP4 PART_4 8, 9
SP98 SP98_SP3 PART_3 6, 7
SP98 SP98_SP4 PART_4 8, 9
SP98 SP12 PART_1 2, 3, 4, 5
23 rows selected.
Global index creation:
SQL> CREATE INDEX gi_range_list_cust_id ON range_list(cust_id);
Index created.
Local index creation:
SQL> CREATE INDEX li_range_list_cust_id
ON range_list(cust_id)
LOCAL;
Index created.
Truncate Partition:
SQL> insert into range_list values(1,2,199,'01-JAN-1997');
1 row created.
SQL> select * from range_list partition (sp98);
CUST_ID CHANNEL_ID AMOUNT_SOLD TIME_ID
---------- ---------- ----------- ---------
1 2 199 01-JAN-97
SQL> ALTER TABLE range_list
TRUNCATE PARTITION sp98
DROP STORAGE;
Table truncated.
SQL> select * from range_list partition (sp98);
no rows selected
Drop Partition:
SQL> ALTER TABLE range_list DROP PARTITION sp98 UPDATE GLOBAL INDEXES;
Table altered.
SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value
FROM user_tab_subpartitions
WHERE table_name ='RANGE_LIST';
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME HIGH_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------
S01 S01_SP1 PART_1 2, 3
S01 S01_SP2 PART_2 4, 5
S01 S01_SP3 PART_3 6, 7
S01 S01_SP4 PART_4 8, 9
S2K S2K_SP1 PART_1 2, 3
S2K S2K_SP2 PART_2 4, 5
S2K S2K_SP3 PART_3 6, 7
S2K S2K_SP4 PART_4 8, 9
S98 S98_SP1 PART_1 2, 3
S98 S98_SP2 PART_2 4, 5
S98 S98_SP3 PART_3 6, 7
S98 S98_SP4 PART_4 8, 9
S99 S99_SP1 PART_1 2, 3
S99 S99_SP2 PART_2 4, 5
S99 S99_SP3 PART_3 6, 7
S99 S99_SP4 PART_4 8, 9
SF SF_SP1 PART_1 2, 3
SF SF_SP2 PART_2 4, 5
SF SF_SP3 PART_3 6, 7
SF SF_SP4 PART_4 8, 9