Range Partitioning:
This is most suitable for storing historical data when we
have a fixed lower limit and upper limit partitioning key to define a
particular range and specify the different tablespaces in which a specific
range of values are to be stored. The most suitable candidate for this type of
partitioning are table with DATE type partitioning key like any historical
table which have data relevant over specific period of time.
For example if we have a table that has a range partition
defined on the ORDER_DATE column as follows:
1. Records ordered between 1-Jan-2014 to 31-Mar-2014 will go
to TS1
2. Records ordered between 1-Apr-2014 to 30-Jun-2014 will go
to TS2
3. Records ordered between 1-Jul-2014 to 30-Sep-2014 will go
to TS3
4. Records ordered between 1-Oct-2014 to 31-Dec-2014 will go
to TS4
A query that retrieves the orders placed for the month of
August will now access only the partition stored in TS3 and not the others
thereby speeding up the query operation. This is also revered to as Partition
Pruning.
CREATE TABLE ORDERS_RANGE
(order_id NUMBER(6), cust_id NUMBER, time_id DATE, quantity_ord NUMBER(3))
PARTITION BY RANGE
(time_id)
(
PARTITION orders_q1 VALUES
LESS THAN (TO_DATE( ' 2014/04/01 ' ,'YYYY/MM/DD')) TABLESPACE TS1,
PARTITION orders_q2 VALUES
LESS THAN (TO_DATE( ' 2014/07/01 ' ,'YYYY/MM/DD')) TABLESPACE TS2,
PARTITION orders_q3 VALUES
LESS THAN (TO_DATE( ' 2014/10/01 ' ,'YYYY/MM/DD')) TABLESPACE TS3,
PARTITION orders_q4 VALUES
LESS THAN (TO_DATE( ' 2015/01/01 ' ,'YYYY/MM/DD')) TABLESPACE TS4
);
DECLARE
order_id number :=1;
cust_id number :=100;
order_date date := TO_DATE('01-JAN-2014','DD-MON-YYYY');
quantity_ordered
number := 10;
BEGIN
for I in 1..50
loop
insert into orders_range
values(order_id+1,cust_id+5,order_date+15,quantity_ordered+1);
commit;
end loop;
end;
explain plan for select * from orders_range where time_id =
to_date('11-AUG-2014', 'dd-mon-yyyy');
select * from table(dbms_xplan.display);
SQL> explain plan for select * from orders_range where
time_id = to_date('11-AUG-2014', 'dd-mon-yyyy');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2263234945
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Tim
e | Pstart| Pstop
|
--------------------------------------------------------------------------------
-----------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 |
48 | 2 (0)| 00:
00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 |
48 | 2 (0)| 00:
00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | ORDERS_RANGE | 1 |
48 | 2 (0)| 00:
00:01 | 3 | 3 |
--------------------------------------------------------------------------------
-----------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("TIME_ID"=TO_DATE(' 2014-08-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'
))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement
(level=2)
18 rows selected.
Interval partitioning is a variation of range partitioning
in which new partitions are created automatically by oracle when data is
inserted beyond a point in time. For example the following will create a
partitioned table for each of the four defined time interval.
CREATE TABLE ORDERS_INTERVAL
(order_id NUMBER(6), cust_id NUMBER, time_id DATE,
quantity_ord NUMBER(3))
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p0 VALUES LESS THAN
(TO_DATE('2014/04/01','YYYY/MM/DD'))tablespace TS1,
PARTITION p1 VALUES LESS THAN
(TO_DATE('2014/07/01','YYYY/MM/DD')) tablespace TS2,
PARTITION p2 VALUES LESS THAN
(TO_DATE('2014/10/01','YYYY/MM/DD')) tablespace TS3,
PARTITION p3 VALUES LESS THAN
(TO_DATE('2015/01/01','YYYY/MM/DD')) tablespace TS4)
/
SQL> SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME,
HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_INTERVAL'
TABLE_NAME
PARTITION_NAME TABLESPACE_NAME
--------------- --------------
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
ORDERS_INTERVAL P0 TS1
TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P1 TS2
TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P2 TS3
TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P3 TS4
TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
Insert two new records with date value later that
01-JAN-2015:
SQL> INSERT INTO ORDERS_INTERVAL
VALUES(100,100,TO_DATE('01-FEB-2015','DD-MON-YYYY'),200);
1 row created.
SQL> INSERT INTO ORDERS_INTERVAL
VALUES(200,200,TO_DATE('01-MAR-2015','DD-MON-YYYY'),300);
1 row created.
SQL> COMMIT;
Commit complete.
If we check the partition entiries in the
USER_TAB_PARTITIONS table we will see that two new partitions have been created
for the two new records with separate months:
SQL> SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME,
HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_INTERVAL';
TABLE_NAME
PARTITION_NAME TABLESPACE_NAME
--------------- --------------
------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
ORDERS_INTERVAL P0 TS1
TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P1 TS2
TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P2 TS3
TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL P3 TS4
TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL SYS_P21 SYSTEM
TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS_INTERVAL SYS_P22 SYSTEM
TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
No comments:
Post a Comment