General | Partition Related Data Dictionary Objects |
indpart$ | indsubpart$ | partobj$ | tabpart$ | indpart_param$ | partlob$ | subpartcol$ | tabsubpart$ |
DBA, ALL, & USERS | dba_tab_cols | dba_ind_partitions | dba_ind_subpartitions | dba_lob_partitions | dba_lob_subpartitions | dba_subpartition_templates | dba_subpart_col_statistics | dba_subpart_histograms | dba_subpart_key_columns | dba_tab_partitions | dba_tab_subpartitions | | Note: Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables. | | Definitions | Partition | Decompose a table or index into smaller, more manageable pieces, called partitions. Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces. | Partition Key | Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of from 1 to 16 columns that determines the partition for each row. | Subpartition | Partitions created within partitions. They are just partitions themselves and there is nothing special about them. | Composite Partitioning | Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning. | Interval Partitioning | Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition. | Explain Plan PSTART/PSTOP Values |
KEY(I) | IN subquery | KEY(SQ) | Recursive subquery | | Invalidating Indexes | By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
- ADD (HASH)
- COALESCE (HASH)
- DROP
- EXCHANGE
- MERGE
- MOVE
- SPLIT
- TRUNCATE
| Global Index | A single index covering all partitions. | Hash Partitioning | Enables partitioning of data that does not lend itself to range or list partitioning. | -- To view the numbers Oracle uses for hashing: SELECT program, sql_hash_value, prev_hash_value FROM gv$session; | List Partitioning | Explicitly controls how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. | Local Index | Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. | Range Partitioning | Maps data to partitions based on ranges of partition key values that you establish for each partition. | Referential Partitioning | Data is mapped to partitions based on values defined in a referential constraint (foreign key) | Partitioning Pruning | Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access. Partition pruning is the skipping of unnecessary index and data partitions or subpartitions by a query. | | Demo Tablespaces | Create demo tablespaces | CREATE TABLESPACE part1 DATAFILE 'c: emp\part01.dbf' SIZE 10M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO ONLINE; CREATE TABLESPACE part2 DATAFILE 'c: emp\part02.dbf' SIZE 10M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO ONLINE; CREATE TABLESPACE part3 DATAFILE 'c: emp\part03.dbf' SIZE 10M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO ONLINE; CREATE TABLESPACE part4 DATAFILE 'c: emp\part04.dbf' SIZE 10M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO ONLINE; ALTER USER uwclass QUOTA UNLIMITED ON part1; ALTER USER uwclass QUOTA UNLIMITED ON part2; ALTER USER uwclass QUOTA UNLIMITED ON part3; ALTER USER uwclass QUOTA UNLIMITED ON part4; | Drop demo tablespaces | DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES; | | Table Partitions | Hash Partitioned Table | 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 (part1, part2, part3); desc hash_part SELECT table_name, tablespace_name, partitioned FROM user_tables; desc user_tab_partitions SELECT partition_name, tablespace_name FROM user_tab_partitions; | Interval-Numeric Range Partitioned Table Also possible are Interval-Hash and Interval-List | CREATE TABLE interval_part ( person_id NUMBER(5) NOT NULL, first_name VARCHAR2(30), last_name VARCHAR2(30)) PARTITION BY RANGE (person_id) INTERVAL (100) STORE IN (uwdata) ( PARTITION p1 VALUES LESS THAN (101)) TABLESPACE uwdata; desc interval_part SELECT table_name, tablespace_name, partitioned FROM user_tables; col high_value format a20 SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; INSERT INTO interval_part (person_id, first_name, last_name) VALUES (100, 'Dan', 'Morgan'); SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; INSERT INTO interval_part (person_id, first_name, last_name) VALUES (101, 'Heli', 'Helskyaho'); SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; INSERT INTO interval_part (person_id, first_name, last_name) VALUES (567, 'Tara', 'Havemeyer'); SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; | Interval-Date Range Partitioned Table | CREATE TABLE interval_date ( person_id NUMBER(5) NOT NULL, last_name VARCHAR2(30), dob DATE) PARTITION BY RANGE (dob) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN (uwdata) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2008-03-15','YYYY-MM-DD'))); INSERT INTO interval_date (person_id, last_name, dob) VALUES (1, 'Morgan', SYSDATE-365); INSERT INTO interval_date (person_id, last_name, dob) VALUES (2, 'Lofstrom', SYSDATE-365); INSERT INTO interval_date (person_id, last_name, dob) VALUES (3, 'Havemeyer', SYSDATE-200); INSERT INTO interval_date (person_id, last_name, dob) VALUES (4, 'Small', SYSDATE-60); INSERT INTO interval_date (person_id, last_name, dob) VALUES (5, 'Ellison', SYSDATE+60); col partition_name format a14 col tablespace_name format a15 col high_value format a85 SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; | List Partitioned Table (add tablespace names and change table name) | CREATE TABLE list_part ( deptno NUMBER(10), deptname VARCHAR2(20), quarterly_sales NUMBER(10,2), state VARCHAR2(2)) PARTITION BY LIST (state) ( PARTITION q1_northwest VALUES ('OR', 'WA') TABLESPACE part1, PARTITION q1_southwest VALUES ('AZ', 'CA', 'NM') TABLESPACEpart2, PARTITION q1_northeast VALUES ('NY', 'VT', 'NJ') TABLESPACEpart1, PARTITION q1_southeast VALUES ('FL', 'GA') TABLESPACE part2, PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part1, PARTITION q1_southcent VALUES ('OK', 'TX') TABLESPACEpart2); SELECT table_name, tablespace_name, partitioned FROM user_tables; SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; INSERT INTO list_part VALUES (10, 'A', 1000, 'OR'); INSERT INTO list_part VALUES (20, 'B', 1000, 'AZ'); INSERT INTO list_part VALUES (10, 'A', 1000, 'WA'); INSERT INTO list_part VALUES (20, 'B', 1000, 'WA'); INSERT INTO list_part VALUES (10, 'A', 1000, 'AZ'); INSERT INTO list_part VALUES (20, 'B', 1000, 'CA'); COMMIT; SELECT * FROM list_part; SELECT * FROM list_part PARTITION (q1_northwest); | Range Partitioned Table - By Date | 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) PARTITION BY RANGE (record_date) ( PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) TABLESPACE part1, PARTITION yr7 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) TABLESPACE part2, PARTITION yr8 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) TABLESPACE part3, PARTITION yr9 VALUES LESS THAN (MAXVALUE) TABLESPACE part4); SELECT table_name, tablespace_name, partitioned FROM user_tables; col part_name format a9 col tbsp_name format a9 SELECT partition_name PART_NAME, tablespace_name TBSP_NAME, high_value FROM user_tab_partitions WHERE table_name = 'RANGE_PART'; INSERT INTO range_part VALUES (1, 1, 1, SYSDATE-720); INSERT INTO range_part VALUES (2, 2, 2, SYSDATE-360); INSERT INTO range_part VALUES (3, 3, 3, SYSDATE-180); INSERT INTO range_part VALUES (4, 4, 4, SYSDATE); SELECT * FROM range_part; SELECT * FROM range_part PARTITION (yr0); SELECT * FROM range_part PARTITION (yr7); SELECT * FROM range_part PARTITION (yr8); SELECT * FROM range_part PARTITION (yr9); conn / as sysdba ALTER SESSION SET tracefile_identifier = 'range_part'; ALTER SESSION SET EVENTS '10128 trace name context forever, level 1'; SELECT * FROM uwclass.range_part PARTITION (yr8); ALTER SESSION SET SQL_TRACE=FALSE;
Trace file c:\oracle\product\diag dbms\orabase\orabase race\orabase_ora_2976_range_part.trc Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production q With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Windows XP Version V5.1 Service Pack 3 CPU : 1 - type 586, 1 Physical Cores Process Affinity : 0x00000000 Memory (Avail/Total): Ph:674M/2038M, Ph+PgF:2777M/3932M, VA:1275M/2047M Instance name: orabase Redo thread mounted by this instance: 1 Oracle process number: 24 Windows thread id: 2976, image: ORACLE.EXE (SHAD) *** 2008-12-17 23:24:21.421 *** SESSION ID:(137.1810) 2008-12-17 23:24:21.421 *** CLIENT ID:() 2008-12-17 23:24:21.421 *** SERVICE NAME:(SYS$USERS) 2008-12-17 23:24:21.421 *** MODULE NAME:(sqlplusw.exe) 2008-12-17 23:24:21.421 *** ACTION NAME:() 2008-12-17 23:24:21.421 partition pruning descriptor: type = 0, level = 1 flags = {single, known, set by parser, } partition mapping descriptor: partitioning method = range number of partitions = 4 number of partitioning keys = 1 partitioning columns = (4) partition pruning descriptor: type = 0, level = 1 flags = {single, known, set by parser, } partition mapping descriptor: partitioning method = range number of partitions = 4 number of partitioning keys = 1 partitioning columns = (4) partition pruning descriptor: type = 0, level = 1 flags = {single, known, set by parser, } partition mapping descriptor: partitioning method = range number of partitions = 4 number of partitioning keys = 1 partitioning columns = (4) partition pruning descriptor: type = 0, level = 1 flags = {single, known, set by parser, } partition mapping descriptor: partitioning method = range number of partitions = 4 number of partitioning keys = 1 partitioning columns = (4) *** 2008-12-17 23:24:42.453 Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [2, 2] index = 2 current partition: part# = 2, subp# = 1048576, abs# = 2 *** 2008-12-17 23:25:04.359 Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [2, 2] index = 2 current partition: part# = 2, subp# = 1048576, abs# = 2 *** 2008-12-17 23:25:53.437 Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [2, 2] index = 2 current partition: part# = 2, subp# = 1048576, abs# = 2 |
x | Range Partitioned Table - By Alpha | 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%') TABLESPACEpart1, PARTITION student_fl VALUES LESS THAN ('M%') TABLESPACEpart2, PARTITION student_mr VALUES LESS THAN ('S%') TABLESPACEpart3, PARTITION student_sz VALUES LESS THAN (MAXVALUE) TABLESPACEpart4); SELECT table_name, tablespace_name, partitioned FROM user_tables; SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; | Referential Partitioned Table | SELECT MIN(num_rows), MAX(num_rows) FROM all_tables WHERE num_rows IS NOT NULL; CREATE TABLE ref_parent ( table_name VARCHAR2(30), order_date DATE, num_rows NUMBER) PARTITION BY RANGE(num_rows) ( PARTITION num_rows1 VALUES LESS THAN (100) TABLESPACE part1, PARTITION num_rows2 VALUES LESS THAN (1000) TABLESPACEpart2, PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACEpart3, PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACEpart4); ALTER TABLE ref_parent ADD CONSTRAINT pk_ref_parent PRIMARY KEY (table_name) USING INDEX; desc ref_parent SELECT table_name, tablespace_name, partitioned FROM user_tables; SELECT partition_name, tablespace_name FROM user_tab_partitions; CREATE TABLE ref_child ( table_name VARCHAR2(30) NOT NULL, index_name VARCHAR2(30) NOT NULL, CONSTRAINT fk_ref_child_parent FOREIGN KEY(table_name) REFERENCES ref_parent(table_name)) PARTITION BY REFERENCE(fk_ref_child_parent); SELECT table_name, partitioning_type, ref_ptn_constraint_name FROM user_part_tables WHERE table_name LIKE 'REF%'; | Partition by System | CREATE TABLE syst_part ( tx_id NUMBER(5), begdate DATE) PARTITION BY SYSTEM ( PARTITION p1 TABLESPACE part1, PARTITION p2 TABLESPACE part2, PARTITION p3 TABLESPACE part3); INSERT INTO syst_part VALUES (1, SYSDATE-10); INSERT INTO syst_part PARTITION (p1) VALUES (1, SYSDATE-10); INSERT INTO syst_part PARTITION (p2) VALUES (2, SYSDATE); INSERT INTO syst_part PARTITION (p3) VALUES (3, SYSDATE+10); SELECT * FROM syst_part PARTITION (p2); | Partition by Virtual Column | CREATE TABLE vcol_part ( tx_id NUMBER(5), begdate DATE, enddate DATE, staylen NUMBER(5) AS (enddate-begdate)) PARTITION BY RANGE (staylen) INTERVAL (10) STORE IN (uwdata) ( PARTITION p1 VALUES LESS THAN (11)) TABLESPACE uwdata; desc vcol_part SELECT table_name, tablespace_name, partitioned FROM user_tables; col high_value format a20 SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; desc user_tab_cols SELECT column_name, virtual_column, data_default FROM user_tab_cols WHERE table_name = 'VCOL_PART'; INSERT INTO vcol_part (tx_id, begdate, enddate) VALUES (1, SYSDATE-5, SYSDATE); INSERT INTO vcol_part (tx_id, begdate, enddate) VALUES (2, SYSDATE-10, SYSDATE); INSERT INTO vcol_part (tx_id, begdate, enddate) VALUES (3, SYSDATE-15, SYSDATE); INSERT INTO vcol_part (tx_id, begdate, enddate) VALUES (4, SYSDATE-25, SYSDATE); COMMIT; SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions; EXPLAIN PLAN FOR SELECT * FROM vcol_part; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT * FROM vcol_part WHERE staylen < 11; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT * FROM vcol_part WHERE staylen BETWEEN 11 AND 20; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT * FROM vcol_part WHERE staylen >= 11; SELECT * FROM TABLE(dbms_xplan.display); | | Composite Partitions | Composite Partitioned Table - By Range And Hash | CREATE TABLE composite_rng_hash ( cust_id NUMBER(10), cust_name VARCHAR2(25), amount_sold NUMBER(10,2), time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY HASH(cust_id) SUBPARTITION TEMPLATE( SUBPARTITION sp1 TABLESPACE part1, SUBPARTITION sp2 TABLESPACE part2, SUBPARTITION sp3 TABLESPACE part3, SUBPARTITION sp4 TABLESPACE part4) ( PARTITION sales_pre05 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')), PARTITION sales_2005 VALUES LESS THAN(TO_DATE('01/01/2006','DD/MM/YYYY')), PARTITION sales_2006 VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')), PARTITION sales_2007 VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')), PARTITION sales_2008 VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')), PARTITION sales_future VALUES LESS THAN(MAXVALUE)); set linesize 121 col table_name format a20 SELECT table_name, partitioned, secondary FROM user_tables; desc user_tab_partitions col partition_name format a15 col spc format 99999 col high_value format a50 SELECT table_name, partition_name, composite, subpartition_count SPC, high_value FROM user_tab_partitions; desc user_tab_subpartitions col subpartition_name format a20 SELECT table_name, partition_name, subpartition_name, subpartition_position FROM user_tab_subpartitions; desc user_subpartition_templates col high_bound format a20 SELECT subpartition_name, tablespace_name, high_bound FROM user_subpartition_templates; conn sh/sh GRANT select ON sales TO uwclass; GRANT select on customers TO uwclass; conn uwclass/uwclass INSERT INTO composite_rng_hash SELECT c.cust_id, c.cust_first_name || ' ' || c.cust_last_name, s.amount_sold, s.time_id FROM sh.sales s, sh.customers c WHERE s.cust_id = c.cust_id AND rownum < 250001; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_PRE05'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2005'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2006'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2007'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_2008'); exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', 'SALES_FUTURE'); SELECT table_name, partition_name, num_rows FROM user_tab_partitions; SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; exec dbms_stats.gather_table_stats(USER, 'COMPOSITE_RNG_HASH', GRANULARITY=>'ALL'); SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; set long 1000000 select dbms_metadata.get_ddl('TABLE', 'COMPOSITE_RNG_HASH'); | Composite Partitioned Table - By Range And List | 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 part1, SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2, SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) ( PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')), PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')), PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')), PARTITION future VALUES LESS THAN(MAXVALUE)); desc composite_rng_list SELECT table_name, partition_name, composite, high_value FROM user_tab_partitions; SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; | Composite Partitioned Table - By Range And Range | CREATE TABLE composite_rng_rng ( cust_id NUMBER(10), cust_name VARCHAR2(25), cust_state VARCHAR2(2), time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY RANGE (cust_id) SUBPARTITION TEMPLATE( SUBPARTITION original VALUES LESS THAN (1001) TABLESPACEpart1, SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACEpart2, SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACEpart3) ( PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')), PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')), PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')), PARTITION future VALUES LESS THAN (MAXVALUE)); desc composite_rng_rng SELECT table_name, partition_name, composite, high_value FROM user_tab_partitions; SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions; | Composite Partitioned Table - By List And Hash | CREATE TABLE composite_list_hash ( cust_id NUMBER(10), cust_name VARCHAR2(25), cust_state VARCHAR2(2), time_id DATE) PARTITION BY LIST(cust_state) SUBPARTITION BY HASH (cust_id) SUBPARTITION TEMPLATE( SUBPARTITION sp1 TABLESPACE part1, SUBPARTITION sp2 TABLESPACE part2, SUBPARTITION sp3 TABLESPACE part3, SUBPARTITION sp4 TABLESPACE part4) ( PARTITION west VALUES ('OR', 'WA'), PARTITION east VALUES ('NY', 'CT'), PARTITION cent VALUES ('IL', 'MN')); | Composite Partitioned Table - By List And List | CREATE TABLE composite_list_list ( cust_id NUMBER(10), cust_name VARCHAR2(25), cust_state VARCHAR2(2), time_id DATE) PARTITION BY LIST(cust_state) SUBPARTITION BY LIST (cust_id) SUBPARTITION TEMPLATE( SUBPARTITION beg VALUES (1,3,5) TABLESPACE part1, SUBPARTITION mid VALUES (2,4,6) TABLESPACE part2, SUBPARTITION end VALUES (7,8,9,0) TABLESPACE part3) ( PARTITION west VALUES ('OR', 'WA'), PARTITION east VALUES ('NY', 'CT'), PARTITION cent VALUES ('IL', 'MN')); | Composite Partitioned Table - By List And Range | CREATE TABLE composite_list_rng ( cust_id NUMBER(10), cust_name VARCHAR2(25), cust_state VARCHAR2(2), time_id DATE) PARTITION BY LIST(cust_state) SUBPARTITION BY RANGE (cust_id) SUBPARTITION TEMPLATE( SUBPARTITION original VALUES LESS THAN (1001) TABLESPACEpart1, SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACEpart2, SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACEpart3) ( PARTITION west VALUES ('OR', 'WA'), PARTITION east VALUES ('NY', 'CT'), PARTITION cent VALUES ('IL', 'MN')); | | Compressed Partitions | Partition Level Compression | Need syntax diagram | CREATE TABLE sales ( saleskey NUMBER, quarter NUMBER, product NUMBER, salesperson NUMBER, amount NUMBER(12,2), region VARCHAR2(10)) COMPRESS PARTITION BY LIST (region) ( PARTITION northwest VALUES ('NORTHWEST'), PARTITION southwest VALUES ('SOUTHWEST'), PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS, PARTITION southeast VALUES ('SOUTHEAST'), PARTITION western VALUES ('WESTERN')); SELECT table_name, tablespace_name, partitioned, compression FROM user_tables; SELECT partition_name, tablespace_name, high_value, compression FROM user_tab_partitions; | | Alter Table For Partitions | Moving Partitions Not composite | ALTER TABLE <table_name> MOVE PARTITION <partition_name> TABLESPACE <tablespace_name>; | SELECT table_name, partition_name, tablespace_name FROM user_tab_partitions; ALTER TABLE hash_part MOVE PARTITION sys_p26 TABLESPACE uwdata; ALTER TABLE list_part MOVE PARTITION q1_southcent TABLESPACE uwdata NOLOGGING; ALTER TABLE range_part MOVE PARTITION yr0 TABLESPACE uwdata; ALTER TABLE composite_rng_hash MOVE PARTITION sales_pre98 TABLESPACE uwdata; SELECT table_name, partition_name, tablespace_name FROM user_tab_partitions; | Moving Subpartitions | ALTER TABLE <table_name> MOVE SUBPARTITION <subpartition_name> TABLESPACE <tablespace_name>; | SELECT partition_name, subpartition_name, tablespace_name FROM user_tab_subpartitions WHERE TABLE_NAME = 'COMPOSITE_RNG_HASH'; ALTER TABLE composite_rng_hash MOVE SUBPARTITION sales_pre98_sp1 TABLESPACE uwdata PARALLEL (DEGREE 2); SELECT partition_name, subpartition_name, tablespace_name FROM user_tab_subpartitions WHERE TABLE_NAME = 'COMPOSITE_RNG_HASH'; | Merging Subpartitions List Only | ALTER TABLE <table_name> MERGE SUBPARTITIONS <subpartition_name> INTO SUBPARTITION <subpartition_name TABLESPACE <tablespace_name>; | ALTER TABLE composite_rng_hash MERGE SUBPARTITIONS sales_pre98_sp1, sales_pre98_sp2 INTO SUBPARTITION sales_pre98_sp12 TABLESPACE part1; 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 part1, SUBPARTITION sp2 VALUES (4, 5) TABLESPACE part2, SUBPARTITION sp3 VALUES (6, 7) TABLESPACE part3, SUBPARTITION sp4 VALUES (8, 9) TABLESPACE part4) ( 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)); col high_value format a20 SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; ALTER TABLE range_list MERGE SUBPARTITIONS sp98_sp1, sp98_sp2 INTO SUBPARTITION sp12 PARALLEL (DEGREE 2) TABLESPACE part1; SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; | Modify A Subpartition Template | ALTER TABLE <table_name> SET SUBPARTITION TEMPLATE ( SUBPARTITION <subpartition_name>, TABLESPACE<tablespace_name>, SUBPARTITION <subpartition_name>, TABLESPACE<tablespace_name>); | SELECT partition_name, subpartition_name, tablespace_name FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; ALTER TABLE range_list SET SUBPARTITION TEMPLATE ( SUBPARTITION sp1 VALUES (2, 3) TABLESPACE part1, SUBPARTITION sp2 VALUES (4, 5) TABLESPACE part2, SUBPARTITION sp3 VALUES (6, 7) TABLESPACE part3, SUBPARTITION sp4 VALUES (8, 9) TABLESPACE part4, SUBPARTITION sp5 VALUES (0, 1) TABLESPACE uwdata); col partition_name format a15 col high_value format a30 SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; ALTER TABLE range_list DROP PARTITION sf; ALTER TABLE range_list ADD PARTITION s02 VALUES LESS THAN(TO_DATE('01/01/2003','DD/MM/YYYY')); ALTER TABLE range_list ADD PARTITION sf VALUES LESS THAN(MAXVALUE); SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; set long 1000000 select dbms_metadata.get_ddl('TABLE', 'RANGE_LIST'); | Change The Tablespace Name For A Future Partition | ALTER TABLE <table_name> MODIFY DEFAULT ATTRIBUTES FOR PARTITION <partition_name> TABLESPACE <tablespace_name>; | select partition_name, tablespace_name, high_value from user_tab_partitions where table_name = 'RANGE_LIST'; ALTER TABLE range_list MODIFY DEFAULT ATTRIBUTES FOR PARTITION s98 TABLESPACEpart1; select partition_name, tablespace_name, high_value from user_tab_partitions where table_name = 'RANGE_LIST'; SELECT partition_name, subpartition_name, tablespace_name FROM user_tab_subpartitions WHERE table_name = 'RANGE_LIST'; | Modify A List Partitioned List | ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD VALUES (<values_list>); | SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions WHERE table_name = 'LIST_PART'; ALTER TABLE list_part MODIFY PARTITION q1_northcent ADD VALUES ('MI', 'OH'); SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions WHERE table_name = 'LIST_PART'; | Drop Values From A List Partitioned List | ALTER TABLE <table_name> MODIFY PARTITION <partition_name> DROP VALUES (<values_list>); | ALTER TABLE list_part MODIFY PARTITION q1_southwest DROP VALUES ('NM'); SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions WHERE table_name = 'LIST_PART'; | Convert A Partition Into A Stand-alone Table | ALTER TABLE <table_name> EXCHANGE PARTITION <partition_name> WITH TABLE <new_table_name> <including | excluding> INDEXES <with | without> VALIDATION EXCEPTIONS INTO <schema.table_name>; | SELECT table_name, partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'LIST_PART'; CREATE TABLE q1_northwest AS SELECT * FROM list_part WHERE 1=2; SELECT * FROM list_part; SELECT * FROM list_part PARTITION (q1_northwest); ALTER TABLE list_part EXCHANGE PARTITION q1_northwest WITH TABLE q1_northwest INCLUDING INDEXES WITHOUT VALIDATION EXCEPTIONS INTO uwclass.problems; SELECT * FROM q1_northwest; SELECT * FROM list_part; | Convert A Stand-alone Table Into A Partition | CREATE TABLE range_part ( rid NUMBER, col1 VARCHAR2(10), col2 VARCHAR2(100)) PARTITION BY RANGE(rid) ( partition p1 VALUES LESS THAN (1000), partition p3 VALUES LESS THAN (3000), partition pm VALUES LESS THAN (MAXVALUE)); CREATE TABLE new_part ( rid NUMBER, col1 VARCHAR2(10), col2 VARCHAR2(100)); INSERT /*+ APPEND ORDERED FULL(s1) USE_NL(s2) */ INTO new_part SELECT 3000 + TRUNC((rownum-1)/500,6), TO_CHAR(rownum), rpad('x',100) FROM sys.source$ s1, sys.source$ s2 WHERE rownum <= 100000; COMMIT; SELECT COUNT(*) FROM range_part; SELECT COUNT(*) FROM new_part; col high_value format a20 SELECT table_name, partition_name, high_value FROM user_tab_partitions; set timing on ALTER TABLE range_part EXCHANGE PARTITION pm WITH TABLE new_part; set timing off DROP TABLE range_part PURGE; DROP TABLE new_part PURGE; -- recreate and populate tables set timing on ALTER TABLE range_part EXCHANGE PARTITION pm WITH TABLE new_part WITHOUT VALIDATION; -- again drop the tables, recreate, and load them -- add some realistic constraints ALTER TABLE range_part ADD CONSTRAINT pk_range_part PRIMARY KEY(rid) USING INDEX LOCAL; ALTER TABLE new_part ADD CONSTRAINT pk_new_part PRIMARY KEY(rid) USING INDEX; set timing on ALTER TABLE range_part EXCHANGE PARTITION pm WITH TABLE new_part INCLUDING INDEXES WITHOUT VALIDATION; -- repeat again but this time do the following before the exchange ALTER TABLE range_part MODIFY PRIMARY KEY NOVALIDATE; ALTER TABLE new_part MODIFY PRIMARY KEY NOVALIDATE; set timing on ALTER TABLE range_part EXCHANGE PARTITION pm WITH TABLE new_part INCLUDING INDEXES WITHOUT VALIDATION; | Renaming A Partition | ALTER TABLE <table_name> RENAME PARTITION <existing_partition_name> TO <new_partition_name>; | SELECT table_name, partition_name FROM user_tab_partitions; ALTER TABLE range_list RENAME PARTITION sf TO sales_future; SELECT table_name, partition_name FROM user_tab_partitions; | Split Partition | ALTER TABLE <table_name> SPLIT PARTITION <partition_name> AT <range_definition> INTO ( PARTITION <first_partition>, PARTITION<second_partition>) UPDATE GLOBAL INDEXES; | SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name = 'RANGE_PART'; INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-1998'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-1999'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2000'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2001'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('15-MAR-2001'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('16-SEP-2001'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('20-DEC-2001'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2002'), 'A'); INSERT INTO range_part VALUES (1, 1, 1, TO_DATE('01-JAN-2003'), 'A'); COMMIT; col ph_comments format a10 SELECT * FROM range_part; SELECT * FROM range_part PARTITION (yr2a); ALTER TABLE range_part SPLIT PARTITION yr2 AT (TO_DATE('30-JUN-2001','DD-MON-YYYY')) INTO ( PARTITION yr2a, PARTITION yr2b) UPDATE GLOBAL INDEXES; SELECT * FROM range_part PARTITION (yr2a); SELECT * FROM range_part PARTITION (yr2b); SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name = 'RANGE_PART'; | Truncate A Partition | ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name> DROP STORAGE; | SELECT * FROM range_part PARTITION (yr2b); ALTER TABLE range_part TRUNCATE PARTITION yr2b DROP STORAGE; SELECT * FROM range_part PARTITION (yr2b); | Split An LOB Partition | ALTER TABLE <table_name> SPLIT PARTITION <partition_name> AT <split location> INTO ( PARTITION <new_partition_name> TABLESPACE<tablespace_name>" LOB <column_name> STORE AS (TABLESPACE <tablespace_name>), PARTITION <new_partition_name> LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name>); | ALTER TABLE print_media_part SPLIT PARTITION p2 AT (150) INTO ( PARTITION p2a TABLESPACE omf_ts1 LOB ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2), PARTITION p2b LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2)); | Add Partition And Specify BLOB/LOB Storage | ALTER TABLE <table_name> ADD PARTITION <new_partition_name> VALUES LESS THAN (MAXVALUE) LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name); | ALTER TABLE print_media_part ADD PARTITION p3 VALUES LESS THAN (MAXVALUE) LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2) LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACEomf_ts1); | | Index Partitions | Global Index Creation | CREATE INDEX <index_name> ON <table_name> <column_name_list>; | SELECT i.index_name, i.composite, i.partition_name, i.high_value FROM user_ind_partitions i, user_tab_partitions t WHERE i.partition_name = t.partition_name AND t.table_name = 'RANGE_PART'; SELECT partition_name FROM user_tab_partitions WHERE table_name = 'RANGE_PART'; CREATE INDEX gi_range_part_person_id ON range_part (person_id); SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'RANGE_PART'; DROP INDEX gi_range_part_person_id; | Local Index Creation and Partition Pruning Demo | CREATE INDEX <index_name> ON <table_name> <column_name_list> LOCAL; | CREATE INDEX li_range_part_person_id ON range_part (person_id) LOCAL; SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'RANGE_PART'; SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'RANGE_PART'; DROP INDEX li_range_part_person_id; CREATE INDEX li_range_part_person_id ON range_part (person_id) LOCAL ( PARTITION yr0 TABLESPACE part1, PARTITION yr1 TABLESPACE part2, PARTITION yr2a TABLESPACE part3, PARTITION yr2b TABLESPACE part4, PARTITION yr9 TABLESPACE uwdata); col tablespace_name format a15 SELECT ip.index_name, ip.partition_name, ip.tablespace_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'RANGE_PART'; SELECT * FROM range_part; SELECT * FROM range_part PARTITION (yr2a); EXPLAIN PLAN FOR SELECT * FROM range_part WHERE record_date BETWEEN TO_DATE('01-JAN-1998') ANDTO_DATE('31-JAN-1998'); SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT * FROM range_part WHERE record_date BETWEEN TO_DATE('01-JAN-1998') ANDTO_DATE('31-DEC-2000'); SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT * FROM range_part WHERE record_date BETWEEN TO_DATE('01-JAN-1999') ANDTO_DATE('31-DEC-2002'); SELECT * FROM TABLE(dbms_xplan.display); | Global Partition Index Creation | CREATE INDEX <index_name> ON <table_name> <column_name_list> GLOBAL PARTITION BY RANGE (partition_column_name_list) ( PARTITION <partition_name> VALUES <condition>); | DROP INDEX li_range_part_person_id; UPDATE range_part SET organization_id = ROWNUM; col ph_comments format a15 SELECT * FROM range_part; CREATE INDEX gi_range_part_person_id ON range_part (organization_id) GLOBAL PARTITION BY RANGE(organization_id) ( PARTITION p1 VALUES LESS THAN(4) TABLESPACE part1, PARTITION p2 VALUES LESS THAN(MAXVALUE) TABLESPACE part2); col high_value format a20 SELECT ip.index_name, ip.partition_name, ip.tablespace_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'RANGE_PART'; | Query for Unusable Indexes | SELECT index_name, partition_name, status FROM user_ind_partitions; | | Alter Table and Index For Partitions | Rebuild Local All Local Indexes On A Table | ALTER TABLE <table_name> MODIFY PARTITION <partition_name> REBUILD UNUSABLE LOCAL INDEXES; | CREATE INDEX li_range_part_person_id ON range_part (person_id) LOCAL; SELECT t.table_name, i.index_name, i.partition_name, i.status FROM user_ind_partitions i, user_tab_partitions t WHERE i.partition_name = t.partition_name; ALTER TABLE range_part MODIFY PARTITION yr0 REBUILD UNUSABLE LOCAL INDEXES; | Rebuild any unusable local index partitions associated with a hash partition at the specific composite partitioned table subpartition level | ALTER TABLE <table_name> MODIFY SUBPARTITION <subpartition_name> REBUILD UNUSABLE LOCAL INDEXES; | SELECT i.table_name, s.index_name, s.partition_name, s.status FROM user_ind_subpartitions s, user_indexes i WHERE s.index_name = i.index_name; ALTER TABLE composite_rng_hash MODIFY SUBPARTITION sales_1999_sp4 REBUILD UNUSABLE LOCAL INDEXES; | Rebuild (and move) a local partition index | ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace_name>; | col partition_name format a10 col tablespace_name format a20 SELECT i.table_name, s.index_name, s.tablespace_name, s.partition_name, s.status FROM user_ind_partitions s, user_indexes i WHERE s.index_name = i.index_name; ALTER INDEX li_range_part_person_id REBUILD PARTITION yr2 TABLESPACE uwdata; SELECT i.table_name, s.index_name, s.tablespace_name, s.partition_name, s.status FROM user_ind_partitions s, user_indexes i WHERE s.index_name = i.index_name; | | Drop Partition | Drop Partition | ALTER TABLE DROP PARTITION <partition_name> [ UPDATE GLOBAL INDEXES]; | SELECT table_name, partition_name FROM user_tab_partitions; ALTER TABLE range_list DROP PARTITION s2k UPDATE GLOBAL INDEXES; | | Demos | Partition Elimination | conn scott/tiger -- Create a list partitioned table CREATE TABLE partdemo ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7, 2), comm NUMBER(7, 2), deptno NUMBER(2)) partition by list(deptno)( partition p1 values (10,30) tablespace uwdata, partition p2 values (20,40) tablespace example); INSERT INTO partdemo SELECT * FROM scott.emp; set linesize 121 SELECT * FROM partdemo; SELECT * FROM partdemo PARTITION (p1); SELECT * FROM partdemo PARTITION (p2); -- Take the example tablespace OFFLINE to examine partition elimination conn / as sysdba ALTER TABLESPACE example OFFLINE; conn scott/tiger SELECT COUNT(*) FROM partdemo; SELECT COUNT(*) FROM partdemo WHERE deptno = 10; SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 19; SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 20; SELECT COUNT(*) FROM partdemo WHERE deptno IN(10,30); conn / as sysdba ALTER TABLESPACE example ONLINE; | | Undocumented Partitioning | Found by Jonathan Lewis and noted here | SELECT DISTINCT TBL$OR$IDX$PART$NUM(BBUKDW.VISIT_TX, 0, CALENDAR_DT) FROM ( SELECT D.CALENDAR_DT CALENDAR_DT FROM BBUKDW.JPL_DAY D WHERE D.FINANCIAL_WEEK_ID>=200218 AND D.FINANCIAL_WEEK_ID <=200222) ORDER BY 1; | |