SkillBuilders | Oracle 9i Administration | Appendix B: Oracle 9i Partitioning

Instructor's Notes

(If applicable)

Appendix B: Oracle 9i Partitioning

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

RANGE PARTITIONING

create table ProjState (
proj_code varchar2(10),
state char(2)
)
Partition By Range (state)
(
Partition lt_IN Values Less Than ('IN'),
Partition lt_MO Values Less Than ('MO'),
Partition lt_WY Values Less Than ('WY')
);

Partitions may reside in different tablespaces. The partitions may also have different STORAGE attributes if dictionary managed storage is in use.

Other Text:
(Examples or comments displayed on slide, if any).

Partition By Range (state)
( Partition lt_IN Values Less Than ('IN'),
Partition lt_MO Values Less Than ('MO'),
Partition lt_WY Values Less Than ('WY')
);

Instructor's Notes

(If applicable)

Example:

CREATE TABLE orders (
order# number(12),
cust# number(9),
order_date date,
order_price number(10,2))
PARTITION BY RANGE (order_date) (
PARTITION old_orders VALUES LESS THAN (
to_date('01-JAN-1997','dd-mon-yyyy') ) TABLESPACE ts00,
PARTITION jan_orders VALUES LESS THAN (
to_date('01-FEB-1997','dd-mon-yyyy') ) TABLESPACE ts01,
PARTITION feb_orders VALUES LESS THAN (
to_date('01-MAR-1997','dd-mon-yyyy') ) TABLESPACE ts02 );

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

MAXVALUE is typically used in the last partition’s value list, and sorts higher than any other value, including NULL.
Example:

CREATE TABLE orders (
order# number(12),
cust# number(9),
order_date date,
order_price number(10,2))
PARTITION BY RANGE (order_date) (
PARTITION old_orders VALUES LESS THAN (
to_date('01-JAN-1997','dd-mon-yyyy') ) TABLESPACE ts00,
PARTITION jan_orders VALUES LESS THAN (
to_date('01-FEB-1997','dd-mon-yyyy') ) TABLESPACE ts01,
PARTITION feb_orders VALUES LESS THAN (MAXVALUE));

Supplemental Notes

Nulls are not allowed to be part of the VALUE_LIST
If Nulls are part of the data, the last partition better contain a MAXVALUE clause for that column.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Each partition can have its own storage attributes including its own tablespace which is recommended.

Other Text:
(Examples or comments displayed on slide, if any).

PARTITION BY RANGE (order_date) (
PARTITION old_orders VALUES LESS THAN (
to_date('01-JAN-1997','dd-mon-yyyy') )
STORAGE (INITIAL 500k NEXT 5K PCTINCREASE 0 )
TABLESPACE ts00,
PARTITION jan_orders VALUES LESS THAN (
to_date('01-FEB-1997','dd-mon-yyyy') )
STORAGE (INITIAL 250k NEXT 20k PCTINCREASE 10 )
TABLESPACE ts01,
PARTITION feb_orders VALUES LESS THAN (
to_date('01-MAR-1997','dd-mon-yyyy') )
STORAGE (INITIAL 100k NEXT 10k PCTINCREASE 10 )
TABLESPACE ts02 );

Instructor's Notes

(If applicable)

Comparison starts left to right. First unequal value breaks the tie:

(1997, 06, 03) is greater than (1997, 05, 31)
And smaller than (1998, 01, 01)

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE orders (
order# number(12)
, cust# number(9)
, order_year number(4)
, order_mon number(2)
, order_day number(2)
, order_price number(10,2) )
PARTITION BY RANGE (order_year, order_mon, order_day)
(PARTITION old_orders VALUES LESS THAN (1997, 01, 01)
,PARTITION jan_orders VALUES LESS THAN (1997, 02, 01)
,PARTITION feb_orders VALUES LESS THAN (1997, 03, 01)
,PARTITION mar_orders VALUES LESS THAN (1997, 04, 01));

Instructor's Notes

(If applicable)

Storage parameters are inherited from table-level defaults. Maintenance Restrictions:

Cannot Split, Drop, Merge

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE cust
(cust_no INTEGER PRIMARY KEY
,lastname VARCHAR2(20) NOT NULL
,firstname VARCHAR2(15) NOT NULL
,area_code VARCHAR2(3)
,phone VARCHAR2(8) )
STORAGE (INITIAL 100K NEXT 100K)
PARTITION BY HASH(cust_no)
( PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3, PARTITION p4 TABLESPACE ts4);

Instructor's Notes

(If applicable)

Assume ORDER_TAB table partitioned on ORDER_DATE

But often joined to CUST table

CUST is hash partitioned on CUST_NO

Better join performance by subpartitioning ORDER_TAB table on CUST_NO

SELECT * FROM cust, order_tab
WHERE cust.cust_no = order_tab.cust_no
AND order_date BETWEEN :x AND :y;

CREATE TABLE order_tab
( order_no INTEGER PRIMARY KEY
,cust_no INTEGER REFERENCES CUST
,order_date DATE NOT NULL )
STORAGE (INITIAL 20K NEXT 20K)
PARTITION BY RANGE(order_date)
SUBPARTITION BY HASH(cust_no)
SUBPARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION q1 VALUES LESS THAN ( to_date('04/01/2000','mm/dd/yyyy') ),
PARTITION q2 VALUES LESS THAN ( to_date('07/01/2000','mm/dd/yyyy') ),
PARTITION q3 VALUES LESS THAN ( to_date('10/01/2000','mm/dd/yyyy') ),
PARTITION q4 VALUES LESS THAN ( to_date('01/01/2001','mm/dd/yyyy') ) );

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

ORDER_TAB has 16 partitions

4 partitions, each with 4 subpartitions

Each partition given initial extent of 20K
Subpartitions given system names

SYS_SUBPnnn
Syntax does allow naming subpartitions
Can be referenced:

FROM table SUBPARTITION(name)

Supplemental Notes
Local indexes have same partition / subpartition structure

Local indexes are always equi-partitioned

CREATE INDEX order_tab_order_date ON order_tab(order_date) LOCAL;

SELECT partitioning_type, subpartitioning_type, partition_count,
def_subpartition_count
FROM user_part_indexes
WHERE table_name = 'ORDER_TAB’;

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

List Partitioning is useful if you have a specific list of values that your partitions can be broken into.
For the example in the slide…

Supplemental Notes
- To create a partition that will catch any values not listed in the table’s definition, use:
  - PARTITION otherids VALUES (NULL)

Where otherids can be any partition name.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE TABLE orders
(order_id NUMBER(5) PRIMARY KEY,
order_date TIMESTAMP,
cust_id NUMBER(5),
order_total NUMBER(17,2),
warehouse_id NUMBER(5))
PARTITION BY LIST (warehouse_id)
(PARTITION EAST VALUES (1,5,10),
PARTITION MIDWEST VALUES (2,4,7,9),
PARTITION SOUTH VALUES (3,6),
PARTITION WEST VALUES (8,11,12,13,14));

Instructor's Notes

(If applicable)

Values can be added or removed from a partition’s list with the ALTER TABLE command.

Supplemental Notes
- Removing a value from an existing list does not delete the rows from the table for this value. That must be done prior to the ALTER TABLE statement with the DELETE command.

DELETE FROM orders
where warehouse_id = 7;
ALTER TABLE orders
MODIFY PARTITION midwest
DROP VALUES (7);

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE orders
MODIFY PARTITION south
ADD VALUES (15);

ALTER TABLE orders
MODIFY PARTITION midwest
DROP VALUES (7);

Instructor's Notes

(If applicable)

create table customer

Other Text:
(Examples or comments displayed on slide, if any).

PARTITION BY RANGE (region)
SUBPARTITION BY LIST (state)
(PARTITION new_england VALUES LESS THAN (2)
(SUBPARTITION ct VALUES ('CT'),
. . .

Instructor's Notes

(If applicable)

TABLESPACE main_ts
PARTITION BY RANGE (region)
SUBPARTITION BY LIST (state)
(PARTITION new_england VALUES LESS THAN (2)
(SUBPARTITION ct VALUES ('CT'),
SUBPARTITION ma VALUES ('MA'),
SUBPARTITION ri VALUES ('RI'),
SUBPARTITION vt VALUES ('VT'),
SUBPARTITION nh VALUES ('NH'),
SUBPARTITION me VALUES ('ME')
),
PARTITION atlantic VALUES LESS THAN (3)
(SUBPARTITION nynj VALUES ('NY', 'NJ'),
SUBPARTITION pa VALUES ('PA'),
SUBPARTITION md VALUES ('MD'),
SUBPARTITION de VALUES ('DE'),
SUBPARTITION dc VALUES (DEFAULT)
),
PARTITION southeast VALUES LESS THAN (4)
(SUBPARTITION ga VALUES ('GA'),
SUBPARTITION sc VALUES ('SC'),
SUBPARTITION nc VALUES ('NC'),
SUBPARTITION stateless VALUES (NULL)
)
);

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Prior to Oracle9i, composite partitioning required the explicit specification of sub-partitions on each partition. A template allows properties of a subpartition to be defined once and then applied to every partition.

CREATE TABLE order_tab

The subpartition name is created from the the concatenation of the partition name and subpartition template name.
Templates can be used with range-list and range-hash composite partitioning.

Other Text:
(Examples or comments displayed on slide, if any).

SUBPARTITION TEMPLATE
(SUBPARTITION a TABLESPACE ts1,
SUBPARTITION b TABLESPACE ts2,
SUBPARTITION c TABLESPACE ts3,
SUBPARTITION d TABLESPACE ts4 )

Instructor's Notes

(If applicable)

Prior to Oracle9i, Index Organized Tables (IOT) could only be partitioned by the RANGE method. Oracle9i supports HASH partitioned IOTs.

See the supplied script PARTITIONED_IOT.SQL for a working example of this code.

Other Text:
(Examples or comments displayed on slide, if any).

create table ProjState
(proj_code varchar2(10)
,state char(2)
,city varchar2(30)
,primary key(proj_code, state) )
ORGANIZATION INDEX
INCLUDING state
overflow
Partition By hash (state)
partitions 50
store in (main_ts)
overflow store in (main_ts);

Instructor's Notes

(If applicable)

The effect of many maintenance operations on a partitioned table is marking a global index UNUSABLE. The new UPDATE GLOBAL INDEXES clause on the ALTER TABLE statement informs Oracle that any changes resulting from the maintenance operation are to be reflected in the index as part of the operation rather than marking it UNUSABLE. Not all maintenance operations support this clause and it is not supported for partitioned IOTs.

If we wanted to drop partition p3 from the comedians table that we created earlier and have the associated global indexes updated as part of the table maintenance operation rather than being marked UNUSABLE (and then having to rebuild them), we would simply need to add the UPDATE GLOBAL INDEXES clause to the ALTER TABLE statement, as shown in the slide.

Many maintenance operations are supported such as ADD, DROP, COALECSE and MOVE.

However, care should be exercised due to the performance tradeoffs of dynamic index maintenance vs. rebuild. See the Oracle9i Database Administrator’s Guide Release 2 (9.2), Chapter 17 for more information regarding the performance impact of the update global indexes clause.

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE comedians DROP PARTITION p3
UPDATE GLOBAL INDEXES;

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE order_tab ENABLE ROW MOVEMENT;
ALTER TABLE order_tab DISABLE ROW MOVEMENT;

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE orders ADD PARTITION apr_orders
VALUES LESS THAN ( 1997,05, 01 )
TABLESPACE ts04;

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE orders DROP PARTITION old_orders;

DELETE FROM orders WHERE order_year < 1997;
--OR DELETE FROM orders PARTITION (old_orders)
ALTER TABLE orders DROP PARTITION old_orders;

Instructor's Notes

(If applicable)

Corresponding local index partitions marked “unusable”

Must REBUILD
Check DBA_INDEXES.STATUS

All Global indexes marked unusable

Must REBUILD
Check DBA_INDEXES.STATUS

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE order_tab
MERGE PARTITIONS q1, q2 INTO PARTITION q1ANDq2;

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

C:\>exp system/manager tables=dave.orders:old_orders

C:\>imp system/manager file=expdat.dmp fromuser=dave
tables=orders:old_orders ignore=y

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

ALTER TABLE ORDERS RENAME PARTITION old_orders
TO p1996_orders;

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

SELECT partition_count, partitioning_key_count,
def_tablespace_name, def_pct_free
FROM sys.dba_part_tables
WHERE owner = 'DAVE'
AND table_name = 'ORDERS’;

PARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME DEF_PCT_FREE
--------------- ---------------------- ------------------- ------------
4 3 USER_DATA 10

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

SELECT column_name, column_position
FROM sys.dba_part_key_columns
WHERE owner = 'DAVE'
AND name = 'ORDERS';
ORDER BY 2;
COLUMN_NAME COLUMN_POSITION
------------------------------ ---------------
ORDER_YEAR 1
ORDER_MON 2
ORDER_DAY 3

Instructor's Notes

(If applicable)

Other Text:
(Examples or comments displayed on slide, if any).

select tablespace_name,
table_name,
partition_name,
high_value
from sys.dba_tab_partitions
order by partition_position;
TABLESPACE_NAME TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------ ------------- ---------------- -------------
USER_DATA ORDERS OLD_ORDERS 1997, 01, 01
USER_DATA ORDERS JAN_ORDERS 1997, 02, 01
USER_DATA ORDERS FEB_ORDERS 1997, 03, 01
USER_DATA ORDERS MAR_ORDERS 1997, 04, 01