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 |