Oracle Partitioned Tables

Oracle Partitioned Tables

Oracle Partitioned TablesVersion 10.2 General
Partition Related Data Dictionary Objects indpart$indsubpart$partobj$tabpart$indpart_param$partlob$subpartcol$tabsubpart$ DBA, ALL, & USERSdba_tab_colsdba_ind_partitionsdba_ind_subpartitionsdba_lob_partitionsdba_lob_subpartitionsdba_subpartition_templatesdba_subpart_col_statisticsdba_subpart_histogramsdba_subpart_key_columnsdba_tab_partitionsdba_tab_subpartitionsNote: 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. DefinitionsPartitionDecompose 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 KeyEach 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.SubpartitionPartitions created within partitions. They are just partitions themselves and there is nothing special about them.Composite PartitioningComposite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning.Interval PartitioningInterval 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.
Invalidating IndexesBy 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 IndexA single index covering all partitions.Hash PartitioningEnables 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 PartitioningExplicitly 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 IndexSeparate 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 PartitioningMaps data to partitions based on ranges of partition key values that you establish for each partition.Referential PartitioningData is mapped to partitions based on values defined in a referential constraint (foreign key) Partitioning PruningOracle 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. Demo Tablespaces
Create demo tablespacesCREATE TABLESPACE part1
DATAFILE 'c:temppart01.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part2
DATAFILE 'c:temppart02.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part3
DATAFILE 'c:temppart03.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

CREATE TABLESPACE part4
DATAFILE 'c:temppart04.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 tablespacesDROP 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 TableCREATE 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-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, 'Jack', 'Cline');

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;
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') TABLESPACE part2,
PARTITION q1_northeast VALUES ('NY', 'VT', 'NJ') TABLESPACE part1,
PARTITION q1_southeast VALUES ('FL', 'GA') TABLESPACE part2,
PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part1,
PARTITION q1_southcent VALUES ('OK', 'TX') TABLESPACE part2);

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 DateCREATE 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,
ph_comments VARCHAR2(200))
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION yr1 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
TABLESPACE part2,
PARTITION yr2 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
TABLESPACE part3,
PARTITION yr9 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;
Range Partitioned Table - By AlphaCREATE 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 part1,
PARTITION student_fl VALUES LESS THAN ('M%') TABLESPACE part2,
PARTITION student_mr VALUES LESS THAN ('S%') TABLESPACE part3,
PARTITION student_sz VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

SELECT table_name, tablespace_name, partitioned
FROM user_tables;

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions;
Referential Partitioned TableSELECT 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) TABLESPACE part2,
PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACE part3,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

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 Virtual ColumnCREATE 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 HashCREATE 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_pre98
VALUES LESS THAN (TO_DATE('01/01/1998','DD/MM/YYYY')),
PARTITION sales_1998
VALUES LESS THAN(TO_DATE('01/01/1999','DD/MM/YYYY')),
PARTITION sales_1999
VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION sales_2000
VALUES LESS THAN(TO_DATE('01/01/2001','DD/MM/YYYY')),
PARTITION sales_2001
VALUES LESS THAN(TO_DATE('01/01/2002','DD/MM/YYYY')),
PARTITION sales_future
VALUES LESS THAN(MAXVALUE));

set linesize 121
col table_name format a20

desc user_tables

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;

INSERT INTO composite
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;

exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', 'SALES_PRE98');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', 'SALES_1998');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', 'SALES_1999');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', 'SALES_2000');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', 'SALES_2001');
exec dbms_stats.gather_table_stats(USER, 'COMPOSITE', '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', 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');
Composite Partitioned
Table - By Range And ListCREATE 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 RangeCREATE 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) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) 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_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; Compressed Partitions
Partition Level CompressionNeed syntax diagramCREATE 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 VVALUES ('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
MOVE PARTITION
TABLESPACE ;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
MOVE PARTITION sales_pre98
TABLESPACE uwdata;

SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions;Moving SubpartitionsALTER TABLE
MOVE SUBPARTITION
TABLESPACE ;SELECT partition_name, subpartition_name, tablespace_name
FROM user_tab_subpartitions
WHERE TABLE_NAME = 'COMPOSITE';

ALTER TABLE composite
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';
Merging Subpartitions

List OnlyALTER TABLE
MERGE SUBPARTITIONS
INTO SUBPARTITION TABLESPACE ;ALTER TABLE composite
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 TemplateALTER TABLE
SET SUBPARTITION TEMPLATE (
SUBPARTITION , TABLESPACE ,
SUBPARTITION , TABLESPACE );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 PartitionALTER TABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION
TABLESPACE ;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 TABLESPACE part1;

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 ListALTER TABLE
MODIFY PARTITION
ADD VALUES ();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 ListALTER TABLE
MODIFY PARTITION
DROP VALUES ();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 TableALTER TABLE
EXCHANGE PARTITION
WITH TABLE
INDEXES
VALIDATION
EXCEPTIONS INTO ;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;Renaming A PartitionALTER TABLE
RENAME PARTITION
TO ;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 PartitionALTER TABLE
SPLIT PARTITION
AT
INTO (PARTITION , 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 PartitionALTER TABLE
TRUNCATE PARTITION
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 PartitionALTER TABLE
SPLIT PARTITION AT INTO
(PARTITION TABLESPACE "
LOB STORE AS (TABLESPACE ),
PARTITION
LOB () STORE AS (TABLESPACE );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 StorageALTER TABLE
ADD PARTITION VALUES LESS THAN (MAXVALUE)
LOB () STORE AS (TABLESPACE 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 PartitionsGlobal Index CreationCREATE INDEX
ON ;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 DemoCREATE INDEX
ON 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') AND TO_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') AND TO_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') AND TO_DATE('31-DEC-2002');

SELECT * FROM TABLE(dbms_xplan.display);
Global Partition Index CreationCREATE INDEX
ON
GLOBAL PARTITION BY RANGE (partition_column_name_list)
(
PARTITION VALUES );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 IndexesSELECT index_name, partition_name, status
FROM user_ind_partitions; Alter Index For PartitionsRebuild Local All Local Indexes On A TableALTER TABLE
MODIFY PARTITION
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
MODIFY SUBPARTITION
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
MODIFY SUBPARTITION sales_1999_sp4
REBUILD UNUSABLE LOCAL INDEXES; Drop PartitionDrop PartitionALTER TABLE DROP PARTITION [UPDATE GLOBAL INDEXES];SELECT table_name, partition_name
FROM user_tab_partitions;

ALTER TABLE range_list DROP PARTITION s2k UPDATE GLOBAL INDEXES; Demos
Partition Eliminationconn 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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7364032/viewspace-17476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7364032/viewspace-17476/