Partitioning is one
of the most sought after options for data warehousing. Almost all Oracle data
warehouses use partitioning to improve the performance of queries and also to
ease the day-to-day maintenance complexities. Starting with 11G, more partitioning
options have been provided and these should reduce the burden of the DBA to a
great extent.
This article should
be helpful to DBAs and Developers who work primarily in a data warehousing
environment. The new features provided by Oracle in 11G should enhance the
partitioning options and provide more flexibility for partition usage and
maintenance.
Table partitioning
was first introduced in Oracle version 8.0 and Oracle was the first RDBMS
vendor to support physical partitioning. Both SQL Server(2000) and DB2 provide
only logical partitions (using the UNION ALL views) while SQL Server 2005
supports physical partitioning though not directly (implemented through
partition functions). The partitioning option in Oracle was overwhelmingly
received by the user community as it improved the performance, manageability
and availability of applications and most importantly, DSS applications. The
wide appreciation of this feature has resulted in frequent enhancements through
subsequent releases.
The following table lists
the partitioning features (high level) that have evolved over each
version/release:
Oracle Database Version
|
Partitioning Features
|
8.0.5
|
Introduced Range Partitioning
|
8i
|
Introduced Hash and composite Range-Hash
partitioning.
|
9i
|
Introduced List Partitioning, Composite
Range-List partitioning.
|
10G
|
Introduced Range, List and Hash partitioning
of Index Organized tables. Also introduced other composite partitioning
options.
|
11G
|
Introduced partition extensions:
-Interval partitioning -REF partitioning -Virtual Column-based partitioning -Introduced Partition Advisor. |
Partitioning Strategies and Extensions at a Glance
The
following table gives a conceptual overview of all available basic partitioning
strategies in Oracle Database 11g:
In addition
to the available partitioning strategies, Oracle Database 11g provides the
following partitioning
extensions:
Table partitioning methods
Table partitioning already exists since 8i, but a lot of new possibilities were added since 11g. The different types of partitioning are:
Range partitioning
The table is divided in ranges, typically used for date
ranges. This is beneficial when the filters using inbetween, greater than or
less than. It is able to skip all partitions not in the range.
,client_id NUMBER ,order_date DATE) PARTITION BY RANGE(order_date) (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy')) ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')) ,PARTITION orders2013 VALUES LESS THAN (MAXVALUE));
Note : When inserting a record that does not belong to any of the partitions, an ORA-14400 error will be raised. If you create a partition with
“MAXVALUE”, it will be used as default partition. New partitions can be added
or dropped manually when needed, or they can be created automatically with
interval partitioning.
,client_id NUMBER ,order_date DATE) PARTITION BY RANGE(order_date) INTERVAL (NUMTOYMINTERVAL(1,'year')) (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy')),PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')));
List partitioning
A limited set of possible values is given; rows containing
the same value are grouped. This can be used for columns with few distinct
values, when all values are known, like department or country. It can be
beneficial to group values together that are combined in filters.
To avoid the ORA-14400 error, one can use the
“DEFAULT”-keyword.
,name VARCHAR2(50) ,country VARCHAR2(2)) PARTITION BY LIST(country) (PARTITION clients_benelux VALUES ('BE','NE','LU') ,PARTITION clients_uk VALUES ('UK'),PARTITION clients_other VALUES (DEFAULT));
Hash partitioning
A value is hashed, and random distribution occurs. This is
used with many distinct values, when there are no searches on ranges. The
advantages can be a more evenly distribution than with range partitioning.
,name VARCHAR2(50) ,country VARCHAR2(2)) PARTITION BY HASH(name)PARTITIONS 5;
Composite partitioning
A partition can be subpartitioned by any of the previous
methods, in any combination since 11g. This will allow more queries to benefit
from table partitioning. Partition pruning will occur when filtering on both
keys or only one of the keys.
,name VARCHAR2(50) ,country VARCHAR2(2)) PARTITION BY LIST(country) SUBPARTITION BY HASH(name) SUBPARTITIONS 5 (PARTITION clients_benelux VALUES ('BE','NE','LU') ,PARTITION clients_uk VALUES ('UK'),PARTITION clients_other VALUES (DEFAULT));
Refpartitioning
Partitioning is also possible on parent and child
relations. When the parent table is partitioned, the child table can be
partitioned based on the foreign key. For instance when the “ORDERS” table is
partitioned on “ORDER_DATE”, the “ORDER_LINES” table can be partitioned on the
order date as well, without storing the actual value in the “ORDER_LINES”
table. This is very beneficial when joining the two tables on the foreign key.
,order_date DATE) PARTITION BY RANGE(order_date) (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy')) ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')) ,PARTITION orders2013 VALUES LESS THAN (MAXVALUE)); CREATE TABLE order_lines (order_line_id NUMBER PRIMARY KEY ,order_id NUMBER NOT NULL ,line VARCHAR2(50) ,CONSTRAINTo_ol_fk FOREIGN KEY (order_id) REFERENCES orders_range(order_id))PARTITION BY REFERENCE (o_ol_fk);
Extended Composite Partitioning
In previous releases of Oracle, composite partitioning
was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1
extends this to allow the following composite partitioning schemes:
- Range-Hash
(available since 8i)
- Range-List
(available since 9i)
- Range-Range
- List-Range
- List-Hash
- List-List
Interval partitioning, described below, is a form of range
partitioning, so the previous list also implies the following combinations:
- Interval-Hash
- Interval-List
- Interval-Range
The follow code provides an example of one of the new composite
partitioning schemes. First we create a table with List-Hash composite
partitioning.
CREATE TABLE list_hash_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);
Next we populate it with some data, which we expect to be spread
throughout the subpartitions.
DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;
INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');
Finally, we query the
USER_TAB_SUBPARTITIONS view to see if the data
has been distributed across the subpartitions.COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01 7
LIST_HASH_TAB PART_AA PART_AA_02 13
LIST_HASH_TAB PART_BB PART_BB_01 10
LIST_HASH_TAB PART_BB PART_BB_02 10
4 rows selected.
SQL>
The query shows the data has been split into the two
partitions based on the vale of the
CODE column, then hashed
between the subpartitions.
Interval Partitioning
Interval partitioning is an extension of range
partitioning, where the system is able to create new partitions as they are
required. The
PARTITION
BY RANGE clause is used in the normal way to identify the transition point
for the partition, then the new INTERVAL clause used to
calculate the range for new partitions when the values go beyond the existing
transition point.
The following code shows an example of a table using
interval partitioning.
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
Querying the
USER_TAB_PARTITIONS view shows there is
only a single partition.EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');11g
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
Provided we insert data with a created_date value less than
'01-NOV-2007' the data will be placed in the existing partition and no new
partitions will be created.
INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
If we add data beyond the range of the existing partition, a new
partition is created.
INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
11g
2 rows selected.
SQL>
If we insert data for two months after the current largest
transition point, only the required partition is created, not the intervening
partitions.
INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 rows selected.
SQL>
Notice that a partition to hold the December (less than 1st
January 2008) data has not been created. If we insert data for this time
period, the missing partition is created.
INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P46 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following restrictions apply to interval partitioned tables:
- Interval
partitioning is restricted to a single partition key that must be a
numerical or date range.
- At
least one partition must be defined when the table is created.
- Interval
partitioning is not supported for index-organized tables.
- You
cannot create a domain index on an interval partitioned table.
- Interval
partitioning can be used as the primary partitioning mechanism in
composite partitioning, but it can't be used at the subpartition level.
- A
MAXVALUEpartition cannot be defined for an interval partitioned table. - NULL
values are not allowed in the partition column.
System Partitioning
As you would expect, system partitioning allows large
tables to be broken down into smaller partitions, but unlike other partitioning
schemes, the database has no control over the placement of rows during insert
operations. The following example shows the creation of a system partitioned
table.
CREATE TABLE system_partitioned_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY SYSTEM
(
PARTITION part_1,
PARTITION part_2
);
The partition must be explicitly defined in all insert statements
or an error is produced.
INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method
SQL>
The
PARTITION clause is used to
define which partition the row should be placed in.INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------- ----------
SYSTEM_PARTITIONED_TAB PART_1 1
SYSTEM_PARTITIONED_TAB PART_2 1
2 rows selected.
SQL>
Notice that the
HIGH_VALUE for the partitions is
blank.
The
PARTITION clause is optional for
update and delete statements, but omitting this clause will force all
partitions to be scanned, since there is no way perform automatic partition
pruning when the database has no control over row placement. When the PARTITION clause is used, you
must be sure to perform the operation against the correct partition.SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;
0 rows deleted.
SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;
0 rows deleted.
SQL>
The
PARTITION clause can also be used
in queries to target specific partitions.SELECT COUNT(*)
FROM system_partitioned_tab PARTITION (part_1);
COUNT(*)
----------
1
1 row selected.
SQL>
System partitioning gives you many of the advantages of
partitioning, but leaves the decision of how the data is partitioned to the
application layer.
Conditions and restrictions on system partitioning
include:
- If
you specify the
PARTITION BY SYSTEMclause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn". - If
you specify
PARTITION BY SYSTEM PARTITIONS nclause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1. - System
partitioning is not available for index-organized tables or a table that
is part of a cluster.
- System
partitioning can play no part in composite partitioning.
- You
cannot split a system partition.
- System
partitioning cannot be specified in a
CREATE TABLE ... AS SELECTstatement. - To
insert data into a system-partitioned table using an INSERT INTO ... AS
subquery statement, you must use partition-extended syntax to specify the
partition into which the values returned by the subquery will be inserted.
Reference Partitioning
Reference partitioning allows tables related by foreign
keys to be logically equi-partitioned. The child table is partitioned using the
same partitioning key as the parent table without having to duplicate the key
columns. Partition maintenance operations performed on the parent table are
reflected on the child table, but no partition maintenance operations are
allowed on the child table.
The following code contains a partitioned parent table
and a dependent reference partitioned child table.
CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);
CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);
Child records that foreign key to rows in the first partition of
the parent table should be placed in the first partition of the child table. So
we insert two rows into the first partition and one row into the second of the
parent table. We then insert three rows into the child table, with one foreign
keyed to a row in the first partition and two foreign keyed to a row in the
second partition of the master table.
INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');
EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');
We now expect the parent table to have 2 records in the 2007
partition and 1 in the 2008 partition, while the child table should have 1 row
in the 2007 partition and 2 rows in the 2008 partition. The following query
confirms out expectation.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB PART_2007 1
CHILD_TAB PART_2008 2
PARENT_TAB PART_2007 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARENT_TAB PART_2008 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M 1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following conditions and restrictions apply to reference partitioning:
- The
child table must specify a referential integrity constraint defined on the
table being created. This constraint must be in
ENABLE VALIDATE NOT DEFERRABLEstate (the default) and refer to a primary or unique key on the parent table. - The
foreign key columns referenced in constraint must be NOT NULL.
- The
constraint cannot use the
ON DELETE SET NULLclause. - The
parent table referenced must be an existing partitioned table. All
partitioning methods except interval partitioning are supported.
- The
foreign key cannot contain any virtual columns.
- The
referenced primary key or unique constraint on the parent table cannot
contain any virtual columns.
- Reference
partitioning cannot be used for index-organized tables, external tables,
or domain index storage tables.
- A
chain of reference partitioned tables can be created, but constraint used
can't be self-referencing.
- The
ROW MOVEMENTsetting for both tables must match. - Reference
partitioning cannot be specified in a
CREATE TABLE ... AS SELECTstatement.
Virtual Column-Based Partitioning
Oracle 11g supports the concept of virtual columns on
tables. These virtual columns are not physically stored in the table, but
derived from data in the table. These virtual columns can be used in the
partition key in all basic partitioning schemes. The example below creates a
table that is list partitioned on a virtual column that represents the first
letter in the username column of the table.
CREATE TABLE users (
id NUMBER,
username VARCHAR2(20),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(username), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);
The following code inserts two rows into each partition defined in
the table.
INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');
INSERT INTO users (id, username) VALUES (1, 'Burty Basset');
INSERT INTO users (id, username) VALUES (1, 'Harry Hill');
INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');
INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');
INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');
INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');
INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
The following query shows the data was distributed as expected.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
USERS PART_A_G 'A', 'B', 'C', 'D', 'E', 'F', 'G' 2
USERS PART_H_N 'H', 'I', 'J', 'K', 'L', 'M', 'N' 2
USERS PART_O_U 'O', 'P', 'Q', 'R', 'S', 'T', 'U' 2
USERS PART_V_Z 'V', 'W', 'X', 'Y', 'Z' 2
4 rows selected.
SQL>
e.g.: Adding a
virtual column to the table ACCOUNTS:
SQL>CREATE TABLE ACCOUNTS
(acc_no number(10) not null,
acc_name varchar2(50) not null,
acc_loc varchar2(5),
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)));
Using the virtual
column as the partitioning key:
SQL>CREATE TABLE accounts
(acc_no number(10) not null,
acc_name varchar2(50) not null,
acc_loc varchar2(5),
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)))
partition by list (acc_branch);
Single Partition Transportable for
Oracle Data Pump
Oracle 11g allows single table partitions to be
transported between databases, rather than requiring the whole table to be
transported. To show this in action we need to create two tablespaces to hold
the table partitions and give the test use a quota on these tablespaces.
CONN sys/password@db11g AS SYSDBA
CREATE TABLESPACE transport_test_ts_1
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_1'
SIZE 128K AUTOEXTEND ON NEXT 128K;
CREATE TABLESPACE transport_test_ts_2
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_2'
SIZE 128K AUTOEXTEND ON NEXT 128K;
ALTER USER test
QUOTA UNLIMITED ON transport_test_ts_1
QUOTA UNLIMITED ON transport_test_ts_2;
CONN test/test@db11g
Next, we create and populate a test partitioned table.
CREATE TABLE transport_test_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT transport_test_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
TABLESPACE transport_test_ts_1,
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
TABLESPACE transport_test_ts_2
);
INSERT INTO transport_test_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO transport_test_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO transport_test_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO transport_test_tab VALUES (4, 'FOUR', '4 FOUR', ADD_MONTHS(SYSDATE,12));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST_TAB');
The following query shows that each partition is on a separate
tablespace and contains some data.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN tablespace_name FORMAT A20
SELECT table_name, partition_name, tablespace_name, num_rows
FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
TRANSPORT_TEST_TAB PART_2007 TRANSPORT_TEST_TS_1 2
TRANSPORT_TEST_TAB PART_2008 TRANSPORT_TEST_TS_2 2
2 rows selected.
SQL>
Any tablespaces we wish to transport must be made read only.
CONN sys/password@db11g AS SYSDBA
ALTER TABLESPACE transport_test_ts_1 READ ONLY;
When we perform the data pump export, we can specify the
individual partition we wish to export using the following syntax.
tables=schema.table:partition transportable=always
Notice the ":partition" section of the
TABLES parameter. The TRANSPORTABLE parameter indicates
that we wish to capture just the metadata for the partiton.
We can now run the Data Pump export using the following
command.
expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
The output is displayed below.
$ expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
Export: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:40:45
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=test.transport_test_tab:part_2007
transportable=always directory=data_pump_dir dumpfile=part_2007.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/DB11G/dpdump/part_2007.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANSPORT_TEST_TS_1:
/u01/app/oracle/oradata/DB11G/tt_ts_1
Datafiles required for transportable tablespace USERS:
/u01/app/oracle/oradata/DB11G/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:40:55
$
To simulate transporting the tablespace, we will drop the existing
table and tablespaces. Notice, that we don't actually remove the datafile associated
with the first tablespace, as this is the datafile containing our transportable
partition.
CONN sys/password@db11g AS SYSDBA
DROP TABLE test.transport_test_tab;
DROP TABLESPACE transport_test_ts_1 INCLUDING CONTENTS;
DROP TABLESPACE transport_test_ts_2 INCLUDING CONTENTS AND DATAFILES;
We can now import the dump file to snap in our transportable
partition using the
PARTITION_OPTIONS=DEPARTITION parameter setting,
which converts all partitions into standalone table segments.impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
The expected output is displayed below.
$ impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
Import: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:47:04
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** partition_options=departition
dumpfile=part_2007.dmp transport_datafiles=/u01/app/oracle/oradata/DB11G/tt_ts_1
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "TEST"."TRANSPORT_TEST_PK" ON "TEST"."TRANSPORT_TEST_TAB" ("ID") PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550 INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."TRANSPORT_TEST_TAB" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type
INDEX:"TEST"."TRANSPORT_TEST_PK" creation failed
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 16:47:07
$
The table segment is named using a combination of the table and
partition name, so dependent objects fail because they are referencing the
wrong table name. We can see the new segment using the following query.
CONN test/test@db11g
EXEC DBMS_STATS.gather_schema_stats(USER);
COLUMN table_name FORMAT A30
COLUMN tablespace_name FORMAT A20
SELECT table_name, tablespace_name, partitioned, num_rows
FROM user_tables;
TABLE_NAME TABLESPACE_NAME PAR NUM_ROWS
------------------------------ -------------------- --- ----------
TRANSPORT_TES_PART_2007 TRANSPORT_TEST_TS_1 NO 2
1 row selected.
SQL>
The
Partition Advisor
Oracle 11g also
provides Partition Advisor, that supports generating partitioning
recommendations similar to what was provided in 10G for materialized views,
materialized view logs and indexes. In fact, partition advisor is a part of the
SQL Access Advisor in oracle 11g. This advisor helps to generate
recommendations that will show the anticipated performance gains that will
result if the partitions are implemented. It also generates the script for
creating the efficient partitions which can be manually submitted to Oracle
through SQL*Plus or can be queued through Enterprise Manager.
With the extension of partitioning advice, customers not only can
get recommendation specifically for partitioning but also a more comprehensive holistic
recommendation of SQL Access Advisor, improving the collective performance of
SQL statements overall.
The Partition Advisor, integrated into the SQL Access Advisor, is
part of Oracle's Tuning Pack, an extra licensable option. It can be used from
within Enterprise Manager or via a command line interface.
Enhanced Statistics Collection for
Partitioned Objects
Oracle 11g includes improvements to statistics
collection for partitioned objects so untouched partitions are not rescanned.
This significantly increases the speed of statistics collection on large tables
where some of the partitions contain static data. Where partition exchange load
(PEL) is used to add data to the a table, only the newly added partition must
be scanned to update the global statistics.
Partitioning Indexes
There are two basic types of partitioned index.
- Local - All index entries in a single
partition will correspond to a single table partition (equipartitioned).
They are created with the LOCAL keyword and support partition
independance. Equipartioning allows oracle to be more efficient whilst
devising query plans.
- Global - Index in a single partition may
correspond to multiple table partitions. They are created with the GLOBAL
keyword and do not support partition independance. Global indexes can only
be range partitioned and may be partitioned in such a fashion that they look
equipartitioned, but Oracle will not take advantage of this structure.
Both types of indexes can be subdivided further.
- Prefixed - The partition key is the leftmost
column(s) of the index. Probing this type of index is less costly. If a
query specifies the partition key in the where clause partition pruning is
possible, that is, not all partitions will be searched.
- Non-Prefixed - Does not support partition
pruning, but is effective in accessing data that spans multiple
partitions. Often used for indexing a column that is not the tables
partition key, when you would like the index to be partitioned on the same
key as the underlying table.
Local Prefixed Indexes
Assuming the INVOICES table is range partitioned on
INVOICE_DATE, the followning are examples of local prefixed indexes.
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Oracle will generate the partition names and build the
partitions in the default tablespace using the default size unless told
otherwise.
Local Non-Prefixed Indexes
Assuming the INVOICES table is range partitioned on
INVOICE_DATE, the following example is of a local non-prefixed index. The
indexed column does not match the partition key.
CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Global Prefixed Indexes
Assuming the INVOICES table is range partitioned on
INVOICE_DATE, the followning examples is of a global prefixed index.
CREATE INDEX invoices_idx ON invoices (invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);
Note that the partition range values must be specified.
The GLOBAL keyword means that Oracle can not assume the partition key is the
same as the underlying table.
Global Non-Prefixed Indexes
Oracle does not support Global Non Prefixed indexes.
Partitioning Existing Tables
The
ALTER
TABLE ... EXCHANGE PARTITION ... syntax can be used to
partition an existing table, as shown by the following example. First we must
create a non-partitioned table to act as our starting point.CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);
INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;
Next we create a new partitioned table with a single partition to
act as our destination table.
CREATE TABLE my_table_2 (
id NUMBER,
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));
Next we switch the original table segment with the partition
segment.
ALTER TABLE my_table_2
EXCHANGE PARTITION my_table_part
WITH TABLE my_table
WITHOUT VALIDATION;
We can now drop the original table and rename the partitioned
table.
DROP TABLE my_table;
RENAME my_table_2 TO my_table;
Finally we can split the partitioned table into multiple
partitions as required and gather new statistics.
ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE);
The following query shows that the partitioning process is
complete.
COLUMN high_value FORMAT A20
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ -------------------- ----------
MY_TABLE MY_TABLE_PART_1 3 2
MY_TABLE MY_TABLE_PART_2 MAXVALUE 2
2 rows selected.
I hope you all have enjoyed reading this article. Comments are welcome....
No comments:
Post a Comment