Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource
consuming. At the same time, data access performance can reduce drastically for these
objects. Partitioning of tables and indexes can benefit the performance and
maintenance in several ways.

 Partition independance means backup and recovery operations can be

performed on individual partitions, whilst leaving the other partitons available.
 Query performance can be improved as access can be limited to relevant
partitons only.
 There is a greater ability for parallelism with more partitions.

All the examples shown here use the users tablespace for all partitions. In a real
situation it is likely that these partitions would be assigned to different tablespaces to
reduce device contention.

 Range Partitioning Tables

 Hash Partitioning Tables
 Composite Partitioning Tables
 Partitioning Indexes
 Local Prefixed Indexes
 Local Non-Prefixed Indexes
 Global Prefixed Indexes
 Global Non-Prefixed Indexes
 Partitioning Existing Tables

Related articles.

 Partitioning Enhancements In Oracle9i

 Hash Partitioned Global Indexes in Oracle 10g
 Partitioning Enhancements in Oracle Database 11g Release 1
 Partitioning an Existing Table using DBMS_REDEFINITION
 Partitioning an Existing Table using EXCHANGE PARTITION

Range Partitioning Tables

Range partitioning is useful when you have distinct ranges of data you want to store
together. The classic example of this is the use of dates. Partitioning a table using date
ranges allows all data of a similar age to be stored in same partition. Once historical
data is no longer needed the whole partition can be removed. If the table is indexed
correctly search criteria can limit the search to the partitions that hold data of a correct
age.

CREATE TABLE invoices

(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002',
'DD/MM/YYYY')) TABLESPACE users);

Hash Partitioning Tables

Hash partitioning is useful when there is no obvious range key, or range partitioning will
cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4,
8, 16...) and can be specified by the PARTITIONS...STORE IN clause.

CREATE TABLE invoices

(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);

Or specified individually.

CREATE TABLE invoices

(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

Composite Partitioning Tables

Composite partitioning allows range partitions to be hash subpartitioned on a different
key. The greater number of partitions increases the possiblities for parallelism and
reduces the chances of contention. The following example will range partition the table
on invoice_date and subpartitioned these on the invoice_no giving a totol of 32
subpartitions.

CREATE TABLE invoices

(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/YYYY')),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001',
'DD/MM/YYYY')),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001',
'DD/MM/YYYY')),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002',
'DD/MM/YYYY'));

Partitioning Indexes
There are two basic types of partitioned index.

 Local - All index entries in a single partition will correspond to a single table
partition (equipartitioned). They are created with the LOCAL keyword and
support partition independance. Equipartioning allows oracle to be more efficient
whilst devising query plans.
  Global - Index in a single partition may correspond to multiple table partitions.
They are created with the GLOBAL keyword and do not support partition
independance. Global indexes can only be range partitioned and may be
partitioned in such a fashion that they look equipartitioned, but Oracle will not
take advantage of this structure.

Both types of indexes can be subdivided further.

 Prefixed - The partition key is the leftmost column(s) of the index. Probing this
type of index is less costly. If a query specifies the partition key in the where
clause partition pruning is possible, that is, not all partitions will be searched.
 Non-Prefixed - Does not support partition pruning, but is effective in accessing
data that spans multiple partitions. Often used for indexing a column that is not
the tables partition key, when you would like the index to be partitioned on the
same key as the underlying table.

Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning
are examples of local prefixed indexes.

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL

(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

Oracle will generate the partition names and build the partitions in the default
tablespace using the default size unless told otherwise.

Local Non-Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following
example is of a local non-prefixed index. The indexed column does not match the
partition key.

CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL

(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
 PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);

Global Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning
examples is of a global prefixed index.

CREATE INDEX invoices_idx ON invoices (invoice_date)

GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001',
'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Note that the partition range values must be specified. The GLOBAL keyword means
that Oracle can not assume the partition key is the same as the underlying table.

Global Non-Prefixed Indexes

Oracle does not support Global Non Prefixed indexes.

Partitioning Existing Tables

The ALTER TABLE ... EXCHANGE PARTITION ... syntax can be used to partition an
existing table, as shown by the following example. First we must create a non-
partitioned table to act as our starting point.

CREATE TABLE my_table (

id NUMBER,
description VARCHAR2(50)
);

INSERT INTO my_table (id, description) VALUES (1, 'One');

INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;

Next we create a new partitioned table with a single partition to act as our destination
table.

CREATE TABLE my_table_2 (

id NUMBER,
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));

Next we switch the original table segment with the partition segment.

ALTER TABLE my_table_2

EXCHANGE PARTITION my_table_part
WITH TABLE my_table
WITHOUT VALIDATION;

We can now drop the original table and rename the partitioned table.

DROP TABLE my_table;

RENAME my_table_2 TO my_table;

Finally we can split the partitioned table into multiple partitions as required and gather
new statistics.

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)

INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE);

The following query shows that the partitioning process is complete.

COLUMN high_value FORMAT A20

SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE

NUM_ROWS
------------------------------ ------------------------------ ------------
-------- ----------
MY_TABLE MY_TABLE_PART_1 3
2
MY_TABLE MY_TABLE_PART_2 MAXVALUE
2

2 rows selected.

----

Oracle Partitioned Tables & Indexes

Version 11.1
General
indpart$ indsubpart$ partobj$ tabpart$
Parti
tion
indpart_param$ partlob$ subpartcol$ tabsubpart$
Rela DBA, ALL, & USERS
ted dba_tab_cols
Dat
dba_ind_partitions
a
Dicti dba_ind_subpartitions
onar dba_lob_partitions
y dba_lob_subpartitions
Obj
ects 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
Decompose a table or index into smaller, more manageable pieces, called partitions. Each
Parti partition of a table or index must have the same logical attributes, such as column names,
tion datatypes, and constraints, but each partition can have separate physical attributes such as
pctfree, pctused, and tablespaces.
Parti
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is
tion
a set of from 1 to 16 columns that determines the partition for each row.
Key
Sub
Partitions created within partitions. They are just partitions themselves and there is nothing special
parti
about them.
tion
Co
mpo
site Composite partitioning is a combination of other partitioning methods. Oracle currently supports
Parti range-hash and range-list composite partitioning.
tioni
ng
Inter
val Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions
Parti are defined by an interval. Interval partitions are automatically created by the database when data
tioni is inserted into the partition.
ng
Expl KEY(I) IN subquery
ain
Plan KEY(SQ) Recursive subquery
PST
ART
/PS
TOP
Valu
es
By default, the following operations on partitions on a heap-organized table mark all global
Inva indexes as unusable:
lidati
ng  ADD (HASH)
Inde  COALESCE (HASH)
xes  DROP
 EXCHANGE
 MERGE
 MOVE
 SPLIT
 TRUNCATE

Glo
bal
A single index covering all partitions.
Inde
x
Enables partitioning of data that does not lend itself to range or list partitioning.
Has
h -- To view the numbers Oracle uses for hashing:
Parti
tioni SELECT program, sql_hash_value, prev_hash_value
ng
FROM gv$session;
List
Parti Explicitly controls how rows map to partitions. You do this by specifying a list of discrete values for
tioni the partitioning key in the description for each partition.
ng
Separate indexes for each partition. A local index can be unique. However, in order for a local
Loc
index to be unique, the partitioning key of the table must be part of the index's key columns.
al
Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a
Inde
local index. Instead, new partitions are added to local indexes only when you add a partition to the
x
underlying table.
Ran
ge
Maps data to partitions based on ranges of partition key values that you establish for each
Parti
partition.
tioni
ng
Ref
eren
tial
Data is mapped to partitions based on values defined in a referential constraint (foreign key)
Parti
tioni
ng
Parti Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed
tioni and eliminates (prunes) unnecessary partitions or subpartitions from access. Partition pruning is
ng the skipping of unnecessary index and data partitions or subpartitions by a query.
Pru
ning

Demo Tablespaces
CREATE TABLESPACE part1
Cre DATAFILE 'c: emp\part01.dbf' SIZE 10M
ate
BLOCKSIZE 8192
dem
o EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
tabl SEGMENT SPACE MANAGEMENT AUTO
esp ONLINE;
ace
s 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;
Dro
p
dem DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;
o DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
tabl DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES;
esp DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES;
ace
s

Table Partitions
 CREATE TABLE hash_part (
Has prof_history_id NUMBER(10),
h
person_id NUMBER(10) NOT NULL,
Parti
tion organization_id NUMBER(10) NOT NULL,
ed record_date DATE NOT NULL,
Tabl prof_hist_comments VARCHAR2(2000))
e 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;
CREATE TABLE interval_part (
Inter person_id NUMBER(5) NOT NULL,
val-
first_name VARCHAR2(30),
Nu
meri last_name VARCHAR2(30))
c PARTITION BY RANGE (person_id)
Ran INTERVAL (100) STORE IN (uwdata) (
ge PARTITION p1 VALUES LESS THAN (101))
Parti TABLESPACE uwdata;
tion
ed
Tabl desc interval_part
e
SELECT table_name, tablespace_name, partitioned
Also FROM user_tables;
pos
sible col high_value format a20
are
Inter
val- SELECT partition_name, tablespace_name, high_value
Has FROM user_tab_partitions;
h
and INSERT INTO interval_part
Inter
val-
(person_id, first_name, last_name)
List 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;
CREATE TABLE interval_date (
Inter person_id NUMBER(5) NOT NULL,
val-
last_name VARCHAR2(30),
Dat
e dob DATE)
Ran PARTITION BY RANGE (dob)
ge INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
Parti STORE IN (uwdata) (
tion PARTITION p1 VALUES LESS THAN (TO_DATE('2008-03-15','YYYY-
ed
Tabl
MM-DD')));
e
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;
CREATE TABLE list_part (
List deptno NUMBER(10),
Parti
deptname VARCHAR2(20),
tion
ed quarterly_sales NUMBER(10,2),
Tabl state VARCHAR2(2))
e PARTITION BY LIST (state) (
(add PARTITION q1_northwest VALUES ('OR', 'WA') TABLESPACE part1,
tabl PARTITION q1_southwest VALUES ('AZ', 'CA',
esp
ace
'NM') TABLESPACEpart2,
nam PARTITION q1_northeast VALUES ('NY', 'VT',
es 'NJ') TABLESPACEpart1,
and PARTITION q1_southeast VALUES ('FL', 'GA') TABLESPACE part2,
cha PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part1,
nge
PARTITION q1_southcent VALUES ('OK',
tabl
e 'TX') TABLESPACE part2);
nam
e) 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);

CREATE TABLE range_part (
Ran
ge
prof_history_id NUMBER(10),
Parti person_id NUMBER(10) NOT NULL,
tion organization_id NUMBER(10) NOT NULL,
ed record_date DATE NOT NULL)
Tabl
PARTITION BY RANGE (record_date) (
e-
By PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-
Dat MON-YYYY'))
e 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
CREATE TABLE students (
Ran student_id NUMBER(6),
ge
student_fn VARCHAR2(25),
Parti
tion student_ln VARCHAR2(25),
ed PRIMARY KEY (student_id))
Tabl PARTITION BY RANGE (student_ln) (
e- PARTITION student_ae VALUES LESS
By THAN ('F%') TABLESPACE part1,
Alph
a
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) TABLESPACEpart4);

SELECT table_name, tablespace_name, partitioned

FROM user_tables;

SELECT partition_name, tablespace_name, high_value

FROM user_tab_partitions;
SELECT MIN(num_rows), MAX(num_rows)
Ref FROM all_tables
eren
tial WHERE num_rows IS NOT NULL;
Parti
tion CREATE TABLE ref_parent (
ed table_name VARCHAR2(30),
Tabl
order_date DATE,
e
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) 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%';
CREATE TABLE syst_part (
Parti tx_id NUMBER(5),
tion
begdate DATE)
by
Syst PARTITION BY SYSTEM (
em 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);

CREATE TABLE vcol_part (
Parti tx_id NUMBER(5),
tion
begdate DATE,
by
Virtu enddate DATE,
al staylen NUMBER(5) AS (enddate-begdate))
Colu PARTITION BY RANGE (staylen)
mn 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

Co
CREATE TABLE composite_rng_hash (
mpo cust_id NUMBER(10),
site cust_name VARCHAR2(25),
Parti amount_sold NUMBER(10,2),
tion time_id DATE)
ed
PARTITION BY RANGE(time_id)
Tabl
e- SUBPARTITION BY HASH(cust_id)
By SUBPARTITION TEMPLATE(
Ran
SUBPARTITION sp1 TABLESPACE part1,
ge
And SUBPARTITION sp2 TABLESPACE part2,
Has SUBPARTITION sp3 TABLESPACE part3,
h 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');
 CREATE TABLE composite_rng_list (
Co cust_id NUMBER(10),
mpo
cust_name VARCHAR2(25),
site
Parti cust_state VARCHAR2(2),
tion time_id DATE)
ed PARTITION BY RANGE(time_id)
Tabl SUBPARTITION BY LIST (cust_state)
e- SUBPARTITION TEMPLATE(
By
Ran
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part1,
ge SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part2,
And SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part3) (
List 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;
CREATE TABLE composite_rng_rng (
Co cust_id NUMBER(10),
mpo
cust_name VARCHAR2(25),
site
Parti cust_state VARCHAR2(2),
tion time_id DATE)
ed PARTITION BY RANGE(time_id)
Tabl SUBPARTITION BY RANGE (cust_id)
e- SUBPARTITION TEMPLATE(
By
Ran
SUBPARTITION original VALUES LESS
ge THAN (1001) TABLESPACE part1,
And SUBPARTITION acquired VALUES LESS
Ran THAN (8001) TABLESPACE part2,
ge 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;
CREATE TABLE composite_list_hash (
Co cust_id NUMBER(10),
mpo
cust_name VARCHAR2(25),
site
Parti cust_state VARCHAR2(2),
tion time_id DATE)
ed PARTITION BY LIST(cust_state)
Tabl SUBPARTITION BY HASH (cust_id)
e- SUBPARTITION TEMPLATE(
By
List
SUBPARTITION sp1 TABLESPACE part1,
And SUBPARTITION sp2 TABLESPACE part2,
Has SUBPARTITION sp3 TABLESPACE part3,
h SUBPARTITION sp4 TABLESPACE part4) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
CREATE TABLE composite_list_list (
Co cust_id NUMBER(10),
mpo
cust_name VARCHAR2(25),
site
Parti cust_state VARCHAR2(2),
tion time_id DATE)
ed PARTITION BY LIST(cust_state)
Tabl SUBPARTITION BY LIST (cust_id)
e- SUBPARTITION TEMPLATE(
By
List
SUBPARTITION beg VALUES (1,3,5) TABLESPACE part1,
And SUBPARTITION mid VALUES (2,4,6) TABLESPACE part2,
List SUBPARTITION end VALUES (7,8,9,0) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
CREATE TABLE composite_list_rng (
Co cust_id NUMBER(10),
mpo
cust_name VARCHAR2(25),
site
Parti cust_state VARCHAR2(2),
tion time_id DATE)
ed PARTITION BY LIST(cust_state)
Tabl SUBPARTITION BY RANGE (cust_id)
e- SUBPARTITION TEMPLATE(
By
SUBPARTITION original VALUES LESS
List
And THAN (1001) TABLESPACE part1,
Ran SUBPARTITION acquired VALUES LESS
ge THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN
(MAXVALUE) TABLESPACEpart3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));

Compressed Partitions
Need syntax diagram
Parti
tion CREATE TABLE sales (
Lev saleskey NUMBER,
el quarter NUMBER,
Co product NUMBER,
mpr
salesperson NUMBER,
essi
on 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

ALTER TABLE <table_name>
Mov MOVE PARTITION <partition_name>
ing
TABLESPACE <tablespace_name>;
Parti
tion SELECT table_name, partition_name, tablespace_name
s FROM user_tab_partitions;
Not ALTER TABLE hash_part
com
MOVE PARTITION sys_p26
posi TABLESPACE uwdata;
te
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;
ALTER TABLE <table_name>
Mov MOVE SUBPARTITION <subpartition_name>
ing
TABLESPACE <tablespace_name>;
Sub
parti SELECT partition_name, subpartition_name, tablespace_name
tion FROM user_tab_subpartitions
s
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';
ALTER TABLE <table_name>
Mer MERGE SUBPARTITIONS <subpartition_name>
ging
INTO SUBPARTITION <subpartition_name
Sub
parti TABLESPACE <tablespace_name>;
tion ALTER TABLE composite_rng_hash
s
MERGE SUBPARTITIONS sales_pre98_sp1, sales_pre98_sp2
List INTO SUBPARTITION sales_pre98_sp12
Only 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';
ALTER TABLE <table_name>
Mod SET SUBPARTITION TEMPLATE (
ify A
SUBPARTITION
Sub
parti <subpartition_name>, TABLESPACE <tablespace_name>,
tion SUBPARTITION
Tem <subpartition_name>, TABLESPACE<tablespace_name>);
plat
e 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');
ALTER TABLE <table_name>
Cha MODIFY DEFAULT ATTRIBUTES FOR PARTITION <partition_name>
nge
TABLESPACE <tablespace_name>;
The
Tabl select partition_name, tablespace_name, high_value
esp
from user_tab_partitions
ace
Na where table_name = 'RANGE_LIST';
me
For ALTER TABLE range_list
A MODIFY DEFAULT ATTRIBUTES
Futu FOR PARTITION s98 TABLESPACE part1;
re
Parti
tion 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';
ALTER TABLE <table_name>
Mod MODIFY PARTITION <partition_name>
ify A
ADD VALUES (<values_list>);
List
Parti SELECT partition_name, tablespace_name, high_value
tion FROM user_tab_partitions
ed
WHERE table_name = 'LIST_PART';
List

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';
ALTER TABLE <table_name>
Dro MODIFY PARTITION <partition_name>
p
DROP VALUES (<values_list>);
Valu
es ALTER TABLE list_part
Fro MODIFY PARTITION q1_southwest
mA
DROP VALUES ('NM');
List
Parti
tion SELECT partition_name, tablespace_name, high_value
ed FROM user_tab_partitions
List WHERE table_name = 'LIST_PART';
ALTER TABLE <table_name>
Con EXCHANGE PARTITION <partition_name>
vert
WITH TABLE <new_table_name>
A
Parti <including | excluding> INDEXES
tion <with | without> VALIDATION
Into EXCEPTIONS INTO <schema.table_name>;
A
Stan SELECT table_name, partition_name, num_rows
d- FROM user_tab_partitions
alon WHERE table_name = 'LIST_PART';
e
Tabl CREATE TABLE q1_northwest AS
e
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;

CREATE TABLE range_part (
Con rid NUMBER,
vert
col1 VARCHAR2(10),
A
Stan col2 VARCHAR2(100))
d- PARTITION BY RANGE(rid) (
alon partition p1 VALUES LESS THAN (1000),
e partition p3 VALUES LESS THAN (3000),
Tabl partition pm VALUES LESS THAN (MAXVALUE));
e
Into
A CREATE TABLE new_part (
Parti rid NUMBER,
tion 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;
ALTER TABLE <table_name>
Ren RENAME PARTITION <existing_partition_name>
ami TO <new_partition_name>;
ng A
Parti SELECT table_name, partition_name
tion FROM user_tab_partitions;

ALTER TABLE range_list RENAME PARTITION sf TO sales_future;

SELECT table_name, partition_name

FROM user_tab_partitions;
ALTER TABLE <table_name>
Split SPLIT PARTITION <partition_name>
Parti
AT <range_definition>
tion
INTO ( PARTITION <first_partition>, PARTITION<second_partiti
on>)
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';
ALTER TABLE <table_name>
Trun TRUNCATE PARTITION <partition_name>
cate
DROP STORAGE;
A
Parti SELECT * FROM range_part PARTITION (yr2b);
tion
ALTER TABLE range_part
TRUNCATE PARTITION yr2b
DROP STORAGE;

SELECT * FROM range_part PARTITION (yr2b);

ALTER TABLE <table_name>
Split SPLIT PARTITION <partition_name> AT <split location> INTO
An
( PARTITION <new_partition_name> TABLESPACE <tablespace_name
LOB
Parti >"
tion 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));
ALTER TABLE <table_name>
Add ADD PARTITION <new_partition_name> VALUES LESS THAN
Parti
(MAXVALUE)
tion
And LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name);
Spe
cify ALTER TABLE print_media_part
BLO ADD PARTITION p3 VALUES LESS THAN (MAXVALUE)
B/L LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2)
OB LOB (ad_sourcetext, ad_finaltext)
Stor
STORE AS (TABLESPACEomf_ts1);
age
Index Partitions
CREATE INDEX <index_name>
Glo ON <table_name> <column_name_list>;
bal
Inde SELECT i.index_name, i.composite, i.partition_name,
x i.high_value
Cre FROM user_ind_partitions i, user_tab_partitions t
atio
WHERE i.partition_name = t.partition_name
n
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;

CREATE INDEX <index_name>
Loc ON <table_name> <column_name_list> LOCAL;
al
Inde CREATE INDEX li_range_part_person_id
x ON range_part (person_id)
Cre LOCAL;
atio
n
and SELECT index_name, partitioned
Parti FROM user_indexes
tion WHERE table_name = 'RANGE_PART';
Pru
ning SELECT ip.index_name, ip.composite, ip.partition_name,
De
mo
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);

CREATE INDEX <index_name>
Glo ON <table_name> <column_name_list>
bal
GLOBAL PARTITION BY RANGE (partition_column_name_list) (
Parti
tion PARTITION <partition_name> VALUES <condition>);
Inde DROP INDEX li_range_part_person_id;
x
Cre
atio UPDATE range_part
n 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';
Que
ry
for
Unu SELECT index_name, partition_name, status
sabl FROM user_ind_partitions;
e
Inde
xes

Alter Table and Index For Partitions

ALTER TABLE <table_name>
Reb MODIFY PARTITION <partition_name>
uild
REBUILD UNUSABLE LOCAL INDEXES;
Loc
al CREATE INDEX li_range_part_person_id
All ON range_part (person_id)
Loc
LOCAL;
al
Inde
xes SELECT t.table_name, i.index_name, i.partition_name,
On i.status
A FROM user_ind_partitions i, user_tab_partitions t
Tabl WHERE i.partition_name = t.partition_name;
e
ALTER TABLE range_part
MODIFY PARTITION yr0
REBUILD UNUSABLE LOCAL INDEXES;
 ALTER TABLE <table_name>
Reb MODIFY SUBPARTITION <subpartition_name>
uild
REBUILD UNUSABLE LOCAL INDEXES;
any
unu
sabl
e
local
inde
x
parti
tion
s
ass
ocia
ted
with SELECT i.table_name, s.index_name, s.partition_name,
a
has
s.status
h FROM user_ind_subpartitions s, user_indexes i
parti WHERE s.index_name = i.index_name;
tion
at ALTER TABLE composite_rng_hash
the
MODIFY SUBPARTITION sales_1999_sp4
spe
cific REBUILD UNUSABLE LOCAL INDEXES;
com
posi
te
parti
tion
ed
tabl
e
sub
parti
tion
level
ALTER INDEX <index_name>
Reb REBUILD PARTITION <partition_name>
uild
TABLESPACE <new_tablespace_name>;
(and
mov col partition_name format a10
e) a col tablespace_name format a20
local
parti
tion SELECT i.table_name, s.index_name, s.tablespace_name,
inde s.partition_name, s.status
x 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
ALTER TABLE DROP PARTITION <partition_name> [ UPDATE GLOBAL
INDEXES];
Dro
p
SELECT table_name, partition_name
Parti FROM user_tab_partitions;
tion
ALTER TABLE range_list DROP PARTITION s2k UPDATE GLOBAL
INDEXES;

Demos
conn scott/tiger
Parti
tion
-- Create a list partitioned table
Elim
inati CREATE TABLE partdemo (
on 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

Fou
nd SELECT DISTINCT TBL$OR$IDX$PART$NUM(BBUKDW.VISIT_TX, 0,
by CALENDAR_DT)
Jon FROM (
atha SELECT D.CALENDAR_DT CALENDAR_DT
n
Lewi FROM BBUKDW.JPL_DAY D
s WHERE D.FINANCIAL_WEEK_ID>=200218
and AND D.FINANCIAL_WEEK_ID <=200222)
note ORDER BY 1;
d he
re