| General |
| Partition Related Data Dictionary Objects |
| indpart$ |
indsubpart$ |
partobj$ |
tabpart$ |
| indpart_param$ |
partlob$ |
subpartcol$ |
tabsubpart$ |
| DBA |
ALL |
USERS |
| dba_ind_partitions |
all_ind_partitions |
user_ind_partitions |
| dba_ind_subpartitions |
all_ind_subpartitions |
user_ind_subpartitions |
| dba_lob_partitions |
all_lob_partitions |
user_lob_partitions |
| dba_lob_subpartitions |
all_lob_subpartitions |
user_lob_subpartitions |
| dba_subpartition _templates |
all_subpartition _templates |
user_subpartition
_templates |
| dba_subpart_col _statistics |
all_subpart_col _statistics |
user_subpart_col
_statistics |
| dba_subpart_histograms |
all_subpart_histograms |
user_subpart_histograms |
| dba_subpart_key_columns |
all_subpart_key_columns |
user_subpart_key_columns |
| dba_tab_partitions |
all_tab_partitions |
user_tab_partitions |
| dba_tab_subpartitions |
all_tab_subpartitions |
user_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. |
| 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. |
| 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 sql_hash_value, prev_hash_value from g v$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. |
| Range Partitioning |
Maps data to partitions based on ranges of partition key values that you establish for each partition. |
| Composite Partitioning |
Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning. |
| 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 by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query. |
| 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
|
| |
| DemoTablespaces |
| Create demo tablespaces |
CREATE TABLESPACE part1
DATAFILE 'c:\temp\part01.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
CREATE TABLESPACE part2
DATAFILE 'c:\temp\part02.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
CREATE TABLESPACE part3
DATAFILE 'c:\temp\part03.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; |
| Drop demo tablespaces |
DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES; |
| |
| Table Partitions |
Hash Partitioned
Table |
CREATE TABLE professional_history (
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); |
List Partitioned
Table (add tablespace names and change table name) |
CREATE TABLE sales_by_region (
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 north,
PARTITION q1_southwest VALUES ('AZ', 'CA', 'NM')
TABLESPACE south,
PARTITION q1_northeast VALUES ('NY', 'VT', 'NJ')
TABLESPACE north,
PARTITION q1_southeast VALUES ('FL', 'GA')
TABLESPACE south,
PARTITION q1_northcent VALUES ('MN', 'WI')
TABLESPACE north,
PARTITION q1_southcent VALUES ('OK', 'TX')
TABLESPACE south); |
Range Partitioned
Table - By Date |
CREATE TABLE professional_history (
prof_history_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date DATE NOT NULL,
ph_comments VARCHAR2(2000))
PARTITION BY RANGE (record_date) (
PARTITION yr0
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION yr1
VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION yr2
VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION yr9
VALUES LESS THAN (MAXVALUE)
TABLESPACE <tablespace_name>); |
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%')
TABLESPACE <tablespace_name>,
PARTITION student_fl VALUES LESS THAN ('M%')
TABLESPACE <tablespace_name>,
PARTITION student_mr VALUES LESS THAN ('S%')
TABLESPACE <tablespace_name>,
PARTITION student_sz VALUES LESS THAN (MAXVALUE)
TABLESPACE <tablespace_name>); |
Composite Partitioned
Table - By Range And Hash |
CREATE TABLE sales_composite (
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE uwdata,
SUBPARTITION sp2 TABLESPACE user_data,
SUBPARTITION sp3 TABLESPACE uwdata,
SUBPARTITION sp4 TABLESPACE uwdata)
(PARTITION sales_jan2000
VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000
VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000
VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000
VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')),
PARTITION sales_may2000
VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')),
PARTITION sales_future
VALUES LESS THAN(MAXVALUE));
set linesize 140
SELECT table_name, partition_name, subpartition_name
FROM user_tab_subpartitions; |
| |
| Alter Table For Partitions |
| Moving Partitions |
ALTER TABLE <table_name>
MOVE PARTITION <partition_name>
TABLESPACE <tablespace_name>; |
ALTER TABLE parts
MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING; |
| Moving Subpartitions |
ALTER TABLE <table_name>
MOVE SUBPARTITION <subpartition_name>
TABLESPACE <tablespace_name>; |
ALTER TABLE scuba_gear
MOVE SUBPARTITION bcd_types
TABLESPACE tbs23 PARALLEL (DEGREE 2); |
| Merging Subpartitions |
ALTER TABLE <table_name>
MERGE SUBPARTITIONS <subpartition_name>
INTO SUBPARTITION <subpartition_name
TABLESPACE <tablespace_name>; |
ALTER TABLE quarterly_regional_sales
MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest
INTO SUBPARTITION q1_1999_west
TABLESPACE ts4; |
| Modify A Subpartition Template |
ALTER TABLE <table_name>
SET SUBPARTITION TEMPLATE (
SUBPARTITION <subpartition_name>, TABLESPACE <tablespace_name>,
SUBPARTITION <subpartition_name>, TABLESPACE <tablespace_name>); |
ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE (
SUBPARTITION e, TABLESPACE ts1,
SUBPARTITION f, TABLESPACE ts2,
SUBPARTITION g, TABLESPACE ts3,
SUBPARTITION h, TABLESPACE ts4); |
| Change The Tablespace Name For A Future Partition |
ALTER TABLE <table_name>
MODIFY DEFAULT ATTRIBUTES FOR PARTITION <partition_name>
TABLESPACE <tablespace_name>; |
ALTER TABLE emp
MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1; |
| Modify A List Partitioned List |
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
ADD VALUES (<values_list>); |
ALTER TABLE sales_by_region
MODIFY PARTITION region_south
ADD VALUES ('OK', 'KS'); |
| Drop Values From A List Partitioned List |
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
DROP VALUES (<values_list>); |
ALTER TABLE sales_by_region
MODIFY PARTITION region_south
DROP VALUES ('OK', 'KS'); |
| 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>; |
ALTER TABLE sales
EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
INCLUDING INDEXES
WITHOUT VALIDATION
EXCEPTIONS INTO uwclass.problems; |
| Renaming A Partition |
ALTER TABLE <table_name>
RENAME PARTITION <existing_partition_name>
TO <new_partition_name>; |
| ALTER TABLE employee RENAME PARTITION emp3 TO employee3; |
| Split Partition |
ALTER TABLE <table_name>
SPLIT PARTITION <partition_name>
AT <range_definition>
INTO (PARTITION <first_partition>, PARTITION <second_partition>)
UPDATE GLOBAL INDEXES; |
ALTER TABLE sales
SPLIT PARTITION sales_q1_2000
AT (TO_DATE('16-FEB-2000','DD-MON-YYYY'))
INTO (PARTITION q1a_2000, PARTITION q1b_2000)
UPDATE GLOBAL INDEXES; |
| Truncate A Partition |
ALTER TABLE <table_name>
TRUNCATE PARTITION <partition_name>
DROP STORAGE; |
ALTER TABLE employee
TRUNCATE PARTITION employee3
DROP STORAGE; |
| 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 (TABLESPACE omf_ts1); |
| |
| Index Partitions |
| Table For Index Partitioning |
CREATE TABLE employees (
employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (employee_id)
(
PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE pt1,
PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE pt2,
PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE pt3); |
| Local Index Creation |
CREATE INDEX <index_name>
ON <table_name> <column_name_list> LOCAL; |
CREATE INDEX ixl_employees_employee_id
ON employees (employee_id) LOCAL;
CREATE INDEX ixl_uwhistory
ON uwhistory (transdate, hid, servid, begdate)
LOCAL (
PARTITION p1 TABLESPACE uwidx01,
PARTITION p2 TABLESPACE uwidx02,
PARTITION p3 TABLESPACE uwidx03,
PARTITION p4 TABLESPACE uwidx04,
PARTITION p5 TABLESPACE uwidx05,
PARTITION p6 TABLESPACE uwidx01,
PARTITION p7 TABLESPACE uwidx02,
PARTITION p8 TABLESPACE uwidx03,
PARTITION p9 TABLESPACE uwidx04,
PARTITION p10 TABLESPACE uwidx05,
PARTITION p11 TABLESPACE uwidx01,
PARTITION p12 TABLESPACE uwidx02,
PARTITION p13 TABLESPACE uwidx03,
PARTITION p14 TABLESPACE uwidx04,
PARTITION p15 TABLESPACE uwidx05,
PARTITION p16 TABLESPACE uwidx01,
PARTITION p17 TABLESPACE uwidx02,
PARTITION p18 TABLESPACE uwidx03,
PARTITION p19 TABLESPACE uwidx04,
PARTITION p20 TABLESPACE uwidx05; |
| Global Index Creation |
CREATE INDEX <index_name>
ON <table_name> <column_name_list>; |
CREATE INDEX ix_employees_employee_id
ON employees(employee_id); |
| 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>); |
CREATE INDEX ixg_employees_employee_id
ON employees (employee_id)
GLOBAL PARTITION BY RANGE(department_id)
(
PARTITION p1 VALUES LESS THAN(5000)
TABLESPACE indx1,
PARTITION p2 VALUES LESS THAN(MAXVALUE)
TABLESPACE indx2); |
| |
| Alter Index For Partitions |
| Rebuild Local All Local Indexes On A Table |
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
REBUILD UNUSABLE LOCAL INDEXES; |
ALTER TABLE dept
MODIFY PARTITION p1
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; |
ALTER TABLE emp
MODIFY SUBPARTITION p3_s1
REBUILD UNUSABLE LOCAL INDEXES; |
| |
| Drop Partition |
| Drop Partition |
ALTER TABLE DROP PARTITION <partition_name>
UPDATE GLOBAL INDEXES; |
| ALTER TABLE DROP PARTITION p1 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; |
