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, 
