Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Globalization Support Guide 11g Release 1 (11.1)
Oracle® Database SQL Language Reference 11g Release 1 (11.1)
Oracle® Database VLDB and Partitioning Guide 11g Release 1 (11.1)
Partitioned Indexes: Global, Local, Prefixed and Non-Prefixed [ID 69374.1]
Creating & Adding Table and Index Partitions [ID 69715.1]
The objective from the 11G OCM Upgrade Exam is:
Create partitioned tables (including reference and interval partitioning)
Interval Partitioning
Interval Partitioning is described as follows:
Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
Reference Partitioning
Reference Partitioning is described as:
Reference partitioning allows the partitioning of two tables related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.
The benefit of this extension is that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency will also automatically cascade partition maintenance operations, thus making application development easier and less error-prone.
Emphasis Mine
But,
All basic partitioning strategies are available for reference Partitioning. Interval partitioning cannot be used with reference partitioning.
Emphasis Mine
Creating Partitions
From Creating Partitions,
When you create (or alter) a partitioned table, a row movement clause (either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT) can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.
Creating Range-Partitioned Tables and Global Indexes
The example given in Creating Range-Partitioned Tables and Global Indexes is reproduced as follows:
Create Tablespaces
Because I am using OMF (Oracle-Managed Files), I can use the simple version of the CREATE TABLESPACE command:
CREATE TABLESPACE tsa;
CREATE TABLESPACE tsb;
CREATE TABLESPACE tsc;
CREATE TABLESPACE tsd;
Create User
Using the CREATE USER command to create a test user, OCMPART:
CREATE USER ocmpart
DEFAULT TABLESPACE tsa
IDENTIFIED BY ********
QUOTA UNLIMITED ON tsa
QUOTA UNLIMITED ON tsb
QUOTA UNLIMITED ON tsc
QUOTA UNLIMITED ON tsd
ACCOUNT UNLOCK
;
Using the GRANT command, the appropriate privileges are granted to OCMPART:
GRANT CREATE SESSION, CREATE TABLE TO ocmpart;
Create Table
Using the CREATE TABLE command, I created the table as described in Creating Range-Partitioned Tables and Global Indexes using the OCMPART user:
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
STORAGE (INITIAL 100K NEXT 50K) LOGGING
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
TABLESPACE tsd
)
ENABLE ROW MOVEMENT;
Creating a Range-Partitioned Global Index
Using the CREATE INDEX command, a global index is created from the example described in Creating Range-Partitioned Tables and Global Indexes using the OCMPART user:
CREATE INDEX amount_sold_ix ON sales(amount_sold)
GLOBAL PARTITION BY RANGE(sale_month)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
However, this commands fails with the following error message:
GLOBAL PARTITION BY RANGE(sale_month) * ERROR at line 2: ORA-14038: GLOBAL partitioned index must be prefixed
The explanation is:
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index // which is illegal // *Action: If the user, indeed, desired to create a non-prefixed // index, it must be created as LOCAL; otherwise, correct the list // of key and/or partitioning columns to ensure that the index is // prefixed
Partitioned Indexes: Global, Local, Prefixed and Non-Prefixed [ID 69374.1] says that:
Global non-prefixed indexes are not supported. This means that the index partitioning key must always be the leftmost index column. Anything else will raise the error:
ORA-14038: GLOBAL partitioned index must be prefixed
Creating & Adding Table and Index Partitions [ID 69715.1] expresses the same thing as:
With Global indexes the index key must be the same as the partition key, this is known as a prefixed index (where the leftmost column in the index matches the leftmost column in the index's partition key). If you wish the index key to be different to the partition key i.e. a non prefixed index, then the index must be created a Local index or you will receive an 'ORA-14038 Global partitioned index must be prefixed'.
Emphasis Mine
I should have realised that SALE_MONTH was not part of the table definition. Let's try a slightly different definition by making the index column and the partitioning key the same:
CREATE INDEX amount_sold_ix ON sales(amount_sold)
GLOBAL PARTITION BY RANGE(amount_sold)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
Creating Interval-Partitioned Tables
Using the example from Creating Interval-Partitioned Tables,
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );
Creating Hash-Partitioned Tables and Global Indexes
Following the example in Creating Hash-Partitioned Tables and Global Indexes,
CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
STORAGE (INITIAL 10K)
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE tsa, PARTITION p2 TABLESPACE tsb,
PARTITION p3 TABLESPACE tsc, PARTITION p4 TABLESPACE tsd);
CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;
Creating a hash-partitioned global index,
CREATE INDEX hgidx ON dept (deptname) GLOBAL
PARTITION BY HASH (deptname)
(PARTITION p1 TABLESPACE tsa,
PARTITION p2 TABLESPACE tsb,
PARTITION p3 TABLESPACE tsc,
PARTITION p4 TABLESPACE tsd);
Creating List-Partitioned Tables
Following the example in Creating List-Partitioned Tables,
CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tsc
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50)
TABLESPACE tsb,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
NOLOGGING,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA'),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT)
);
Creating Reference-Partitioned Tables
The example given in Creating Reference-Partitioned Tables fails with the following message:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals
An extended explanation is:
// *Cause: An attempt was made to use a time/timestamp expression whose format // does not explicitly have time zone on a TIME/TIMESTAMP or // TIME/TIMESTAMP WITH TIME ZONE column. // *Action: Explicitly use TIME/TIMESTAMP WITH TIME ZONE literal.
So, I replaced the TO_DATE function calls with TO_TIMESTAMP_TZ ones and adding the time zone information which is not supported by TO_DATE format models:
I selected the time zone name of Australia/sydney from the list given in Time Zone Names.
CREATE TABLE orders
( order_id NUMBER(12),
order_date TIMESTAMP WITH LOCAL TIME ZONE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-APR-2005 Australia/Sydney','DD-MON-YYYY TZR')),
PARTITION Q2_2005 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JUL-2005 Australia/Sydney','DD-MON-YYYY TZR')),
PARTITION Q3_2005 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-OCT-2005 Australia/Sydney','DD-MON-YYYY TZR')),
PARTITION Q4_2005 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JAN-2006 Australia/Sydney','DD-MON-YYYY TZR'))
);
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);