Step by step test 11g new partition methods

DB version: 11.1.0.6

–new partition methods, ** are tested
**interval partition
**referencial partition
**virtual column partition
**system partition

*other new composite partitions
*Range-Range
*List-Range
*List-Hash
*List-List
*Interval-Range
*Interval-Hash
*Interval-List
*partition advisor, part of the SQL Access Advisor
*partition mode data pump, tables=[schema].[table_name].[partiton_name], PARTITION_OPTIONS = {NONE | DEPARTITION | MERGE}


–**interval partition, a kind of range partition

–use a sale table as example
create table sales
(prod_id        NUMBER,
cust_id        NUMBER,
time_id        DATE
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’)) STORE IN (USERS,DATA)
( PARTITION p1 VALUES LESS THAN (TO_DATE(‘2007-1-1’, ‘YYYY-MM-DD’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(‘2008-11-15’, ‘YYYY-MM-DD’))
);
–p1 and p2 are different wide partition
–2008-11-15 is the transition point
–abover it each partition will be created with the same width 1 month

–let’s add data
INSERT INTO SALES VALUES (1,1,TO_DATE(‘2006-09-02′,’YYYY-MM-DD’));
INSERT INTO SALES VALUES (2,2,TO_DATE(‘2008-10-14′,’YYYY-MM-DD’));
INSERT INTO SALES VALUES (2,2,TO_DATE(‘2008-11-08′,’YYYY-MM-DD’));
INSERT INTO SALES VALUES (3,3,TO_DATE(‘2008-12-16′,’YYYY-MM-DD’));
INSERT INTO SALES VALUES (3,3,TO_DATE(‘2008-12-26′,’YYYY-MM-DD’));
commit;

–check partitions available
T@ONASM>SELECT PARTITION_NAME,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’SALES’

PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————–
P1                             USERS
P2                             USERS
SYS_P41                        DATA

T@ONASM>select * from SALES PARTITION (p1);

PROD_ID    CUST_ID TIME_ID
———- ———- ——————
1          1 02-SEP-06  –each data before 2007-1-1 in this partition

T@ONASM>select * from SALES PARTITION (p2);

PROD_ID    CUST_ID TIME_ID
———- ———- ——————
2          2 14-OCT-08
2          2 08-NOV-08  –each data before 2008-11-15 in this partition, it is predefined

T@ONASM>select * from SALES PARTITION (SYS_P41);

PROD_ID    CUST_ID TIME_ID
———- ———- ——————
3          3 16-DEC-08
3          3 26-DEC-08  –they are in the same partition

–we can also use another approach “partition for” to access interval partion
T@ONASM>select * from sales partition for(TO_DATE(‘2008-12-20′,’YYYY-MM-DD’));

PROD_ID    CUST_ID TIME_ID
———- ———- ——————
3          3 16-DEC-08
3          3 26-DEC-08

–check the partition value range
T@ONASM>select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_NAME=’SALES’;
PARTITION_NAME                 HIGH_VALUE
—————————— ——————————————————————————–
P1                             TO_DATE(‘ 2007-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
P2                             TO_DATE(‘ 2008-11-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P41                        TO_DATE(‘ 2009-01-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
–The intermediate partition is not created

–let’s add a partition in the hole betweeen P2 and SYS_P41
T@ONASM>INSERT INTO SALES VALUES (3,3,TO_DATE(‘2008-12-14′,’YYYY-MM-DD’));

–check value range again
T@ONASM>select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_NAME=’SALES’;
PARTITION_NAME                 HIGH_VALUE
—————————— ——————————————————————————–
P1                             TO_DATE(‘ 2007-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
P2                             TO_DATE(‘ 2008-11-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P41                        TO_DATE(‘ 2009-01-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P61                        TO_DATE(‘ 2008-12-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

T@ONASM>SELECT PARTITION_NAME,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’SALES’;
PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————–
P1                             USERS
P2                             USERS  –the predefined partition are always using the first tablespace
SYS_P41                        DATA   –you can see tablespace usage start with round-robin fashion
SYS_P61                        USERS

–The system-generated partition name can be changed
ALTER TABLE SALES RENAME PARTITION SYS_P41 to p_200901;

–an existing partitioned table can be altered to an interval-partitioned table
ALTER TABLE customers SET INTERVAL (NUMTOYMINTERVAL (1, ‘MONTH’));

–interval partition limitations:
—  can only use 1 partition key column, NUMBER or DATA type only
—  at least one partition must be specified
—  not support index-organized tables
—  MAXVALUES is not allowed
—  partition key cannot have null values
—  cannot manually add partitions
—  cannot used with reference partition


–**reference partition
–using orders and order_items as example

CREATE TABLE orders
( order_id           NUMBER(12),
order_date         DATE,
customer_id        NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
( PARTITION Q1_2008 VALUES LESS THAN (TO_DATE(‘2008-04-01′,’YYYY-MM-DD’)),
PARTITION Q2_2008 VALUES LESS THAN (TO_DATE(‘2008-07-01′,’YYYY-MM-DD’)),
PARTITION Q3_2008 VALUES LESS THAN (TO_DATE(‘2008-10-01′,’YYYY-MM-DD’)),
PARTITION Q4_2008 VALUES LESS THAN (TO_DATE(‘2009-01-01′,’YYYY-MM-DD’))
);

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);

–check the partitions
T@ONASM>SELECT SEGMENT_NAME,PARTITION_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (‘ORDERS’,’ORDER_ITEMS’);

SEGMENT_NAME         PARTITION_NAME
——————– ——————————
ORDERS               Q1_2008
ORDERS               Q2_2008
ORDERS               Q3_2008
ORDERS               Q4_2008
ORDER_ITEMS          Q1_2008  –exactly what we expected
ORDER_ITEMS          Q2_2008
ORDER_ITEMS          Q3_2008
ORDER_ITEMS          Q4_2008

8 rows selected.

–insert data to test
insert into orders values (1,TO_DATE(‘2008-03-03′,’YYYY-MM-DD’),1);
insert into orders values (2,TO_DATE(‘2008-06-03′,’YYYY-MM-DD’),2);
insert into orders values (3,TO_DATE(‘2008-09-03′,’YYYY-MM-DD’),3);
insert into orders values (4,TO_DATE(‘2008-12-03′,’YYYY-MM-DD’),4);
insert into order_items values (1,1,1,1,1);
insert into order_items values (2,2,2,2,2);
insert into order_items values (3,3,3,3,3);
insert into order_items values (4,4,4,4,4);
commit;

–check data in partition
T@ONASM>select * from order_items partition (Q1_2008);
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
———- ———— ———- ———- ———-
1            1          1          1          1
T@ONASM>select * from order_items partition (Q2_2008);
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
———- ———— ———- ———- ———-
2            2          2          2          2
T@ONASM>select * from order_items partition (Q3_2008);
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
———- ———— ———- ———- ———-
3            3          3          3          3
T@ONASM>select * from order_items partition (Q4_2008);
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
———- ———— ———- ———- ———-
4            4          4          4          4
–it is exactly as expected

–reference partition limitation: cannot used with interval partition
–benefit: avoids having to duplicate the partition-key column, partition-wise join
—         automatically maintain partitions at the same time


–**virtual column partition, all partition methods are supported using virtual columns.

–use a employee table as example
create table employee (
employee_id number,
name  varchar2(20),
title varchar2(40),
emp_year as (trunc(employee_id,-4)/10000)
)
PARTITION BY RANGE (emp_year) INTERVAL (1)
( PARTITION p1 VALUES LESS THAN (2006)
);

-let’s add data, suppose the first 4 digit of employee_id is represent year.
insert into employee(employee_id,name,title) values (20062023,’Alex’,’dba’);
insert into employee(employee_id,name,title) values (20070001,’Daniel’,’ceo’);
insert into employee(employee_id,name,title) values (20080201,’Susan’,’secretary’);
insert into employee(employee_id,name,title) values (20080520,’Tom’,’accountant’);
commit;

–let’s check the partition available
T@ONASM>select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_NAME=’EMPLOYEE’;

PARTITION_NAME                 HIGH_VALUE
—————————— ————————————————————–
P1                             2006
SYS_P81                        2007
SYS_P82                        2008
SYS_P83                        2009

–let’s check the data
T@ONASM>select * from employee partition (p1);

no rows selected

T@ONASM>select * from employee partition (SYS_P81);

EMPLOYEE_ID NAME                 TITLE                                      EMP_YEAR
———– ——————– —————————————- ———-
20062023 Alex                 dba                                            2006

T@ONASM>select * from employee partition (SYS_P82);

EMPLOYEE_ID NAME                 TITLE                                      EMP_YEAR
———– ——————– —————————————- ———-
20070001 Daniel               ceo                                            2007

T@ONASM>select * from employee partition (SYS_P83);

EMPLOYEE_ID NAME                 TITLE                                      EMP_YEAR
———– ——————– —————————————- ———-
20080201 Susan                secretary                                      2008
20080520 Tom                  accountant                                     2008

–good, it is just as expected!
–vitual column partition comments:
— vitual column stored as metadata only
— can be used in queries, DML, DDL statements
— can be indexed
— can have statistics collected on them
— limitation: cannot call PL/SQL function
—     Cannot update, insert to a virtual column
—     The virtual column cannot reference another virtual column
—     All columns referenced in the expression for the virtual column
—     must exist in the same table
—     The output of the column expression must be a scalar value


–**system partition, application-controlled partitioning, database do not control the data placement.
— an insertion into a system partitioned table without the explicit specification of a partition will fail.

CREATE TABLE credits (name varchar2(20), telephone number)
PARTITION BY SYSTEM
(
PARTITION p_good,
PARTITION p_fair,
PARTITION p_bad
);

INSERT INTO credits PARTITION (p_good) VALUES (‘Alex’,50002563);
INSERT INTO credits PARTITION (p_fair) VALUES (‘Susan’,67002587);
INSERT INTO credits PARTITION (p_bad) VALUES (‘Daniel’,85892095);

–if you don’t specify the partition name
T@ONASM>INSERT INTO credits VALUES (‘Bob’,12345678);
INSERT INTO credits VALUES (‘Bob’,12345678)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

–delete and update do not require partition syntax, but if specified, partition pruning is the benefit

–you can use bind variable to specify the partition name
T@ONASM>SELECT SUBOBJECT_NAME,OBJECT_ID FROM USER_OBJECTS WHERE object_name=’CREDITS’ AND OBJECT_TYPE=’TABLE PARTITION’;

SUBOBJECT_NAME                  OBJECT_ID
—————————— ———-
P_BAD                               12503
P_FAIR                              12502
P_GOOD                              12501

T@ONASM>var partition_id number
T@ONASM>exec :partition_id :=12501

PL/SQL procedure successfully completed.

T@ONASM>insert into credits partition(dataobj_to_partition(“CREDITS”, :partition_id)) values (‘Tom’,98765432);

1 row created.

T@ONASM>commit;

Commit complete.

T@ONASM>select * from credits partition (p_good);

NAME                  TELEPHONE
——————– ———-
Alex                   50002563
Tom                    98765432  –it is here

–it is not SYSTEM. It is actually do it yourself, 🙂

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to Step by step test 11g new partition methods

  1. neworacledba says:

    great job…post is very informative

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: