http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/DBMSCRYPTOencryptaes128DBMSCRYPTOchaincbcDBMSCRYPTOpadpkcs5.htm - DBMS Crypto
http://www.dba-oracle.com/t_dbms_crypto.htm - crypto
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2007/03/01/0001.htm - crypto
Monday, February 13, 2012
http://www.oracle.com/technetwork/database/focus-areas/security/tde-faq-093689.html#A12001
TDE - Encryption using Wallet Manager
http://www.oracle-base.com/articles/11g/TablespaceEncryption_11gR1.php - Table space encryption
http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Obfuscation
http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Encryption - DBMS_OBFUSCATION_TOOLKIT
TDE - Encryption using Wallet Manager
http://www.oracle-base.com/articles/11g/TablespaceEncryption_11gR1.php - Table space encryption
http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Obfuscation
http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Encryption - DBMS_OBFUSCATION_TOOLKIT
Thursday, February 9, 2012
Syntax to add partitions dynamically
Dear Oracle experts,
In our system we have a requirement like...
we need to create a partitioned table with 3 partitions and each
partition contains data of only one day.
Moreover I need to store only the last 3 days data in those
partitions(need not bother about old data).
I mean, for eg. today is 30-apr-2011, i need to have 28/29/30-apr-2011
days data in the partitions of table.
Hope this is clear.
Please guide me the way to implement this.
Thanks.
Regards,
Madhu.
Best Answerby Larson E Apr 29, 2011
We do this by creating a table and partition on the date, so the partition
would be defined as:
alter table abc add partition abc_20110428 values less than ('29-APR-11');
alter table abc add partition abc_20110429 values less than ('30-APR-11');
alter table abc add partition abc_20110430 values less than ('01-MAY-11');
A job is created that runs each day. On this example, let's assume the job
runs at 1AM each nigh. So on May 2, data for 4/29 would be processed, then
remove the oldest partition, such as:
declare
my_date date;
my_datenum number;
my_partition varchar2(16);
my_date := to_char(sysdate-3);
do_processing (my_date);
-- remove old partition
my_partition := 'abc_' to_char(my_date, 'YYYYMMDD');
execute immediate 'alter table abc drop partition ' || my_partition || '
update global indexes';
-- now add my new partition for today
my_partition := 'abc_' || to_number(to_char(sysdate));
execute immediate 'alter table abc add partition ' || my_partition || '
values less than (''' || to_char(sysdate) || ''')';
-- schedule tomorrow's job
In our system we have a requirement like...
we need to create a partitioned table with 3 partitions and each
partition contains data of only one day.
Moreover I need to store only the last 3 days data in those
partitions(need not bother about old data).
I mean, for eg. today is 30-apr-2011, i need to have 28/29/30-apr-2011
days data in the partitions of table.
Hope this is clear.
Please guide me the way to implement this.
Thanks.
Regards,
Madhu.
Best Answerby Larson E Apr 29, 2011
We do this by creating a table and partition on the date, so the partition
would be defined as:
alter table abc add partition abc_20110428 values less than ('29-APR-11');
alter table abc add partition abc_20110429 values less than ('30-APR-11');
alter table abc add partition abc_20110430 values less than ('01-MAY-11');
A job is created that runs each day. On this example, let's assume the job
runs at 1AM each nigh. So on May 2, data for 4/29 would be processed, then
remove the oldest partition, such as:
declare
my_date date;
my_datenum number;
my_partition varchar2(16);
my_date := to_char(sysdate-3);
do_processing (my_date);
-- remove old partition
my_partition := 'abc_' to_char(my_date, 'YYYYMMDD');
execute immediate 'alter table abc drop partition ' || my_partition || '
update global indexes';
-- now add my new partition for today
my_partition := 'abc_' || to_number(to_char(sysdate));
execute immediate 'alter table abc add partition ' || my_partition || '
values less than (''' || to_char(sysdate) || ''')';
-- schedule tomorrow's job
Dynamic partition in Oracle 10G
http://www.ora600.be/news/tool-add-range-partitions-automatically-table
Link to create dynamic partition
Link to create dynamic partition
Tuesday, February 7, 2012
http://database.itags.org/oracle/68426/ - Encryption sample
http://www.oracle-base.com/articles/11g/SecureFiles_11gR1.php#lob_encryption - Secure File
http://psoug.org/reference/dbms_crypto.html - Bunch of encryption functions
http://www.oracle-base.com/articles/11g/SecureFiles_11gR1.php#lob_encryption - Secure File
http://psoug.org/reference/dbms_crypto.html - Bunch of encryption functions
Thursday, February 2, 2012
Oracle 11G partitioning & timestamp related importan sites
http://www.dba-oracle.com/t_interval_partitioning.htm
http://shonythomas.blogspot.com/2011/05/oracle-partitioning.html
http://www.orafaq.com/node/1912
http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
http://rampant-books.com/art_hernandez_partitioning_in_oracle.htm
http://www.orafaq.com/node/1912
http://psoug.org/reference/timestamp.html - timestamp
http://forum.springsource.org/showthread.php?65749-Oracle-TIMESTAMP(6)-WITH-LOCAL-TIME-ZONE-Problems - timestamp
http://oracle-cookies.blogspot.com/2008/03/whats-wrong-with-oracle-timestamp-with.html - timestamp
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm encryption on CLOB
http://shonythomas.blogspot.com/2011/05/oracle-partitioning.html
http://www.orafaq.com/node/1912
http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
http://rampant-books.com/art_hernandez_partitioning_in_oracle.htm
http://www.orafaq.com/node/1912
http://psoug.org/reference/timestamp.html - timestamp
http://forum.springsource.org/showthread.php?65749-Oracle-TIMESTAMP(6)-WITH-LOCAL-TIME-ZONE-Problems - timestamp
http://oracle-cookies.blogspot.com/2008/03/whats-wrong-with-oracle-timestamp-with.html - timestamp
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm encryption on CLOB
Wednesday, February 1, 2012
Partition table syntaxes
create table part(a date)
PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY')));
CREATE TABLE "KAJAL"."PRODUCTS"
( "PARTNO" NUMBER, "DESCRIPTION" VARCHAR2(60 BYTE) )
PARTITION BY HASH ("PARTNO")
(PARTITION "SYS_P21" TABLESPACE "USERS" ,
PARTITION "SYS_P22" TABLESPACE "USERS" ,
PARTITION "SYS_P23" TABLESPACE "USERS" ,
PARTITION "SYS_P24" TABLESPACE "USERS" ) ;
CREATE TABLE "KAJAL"."SALES"
( "YEAR" NUMBER(4,0),
"PRODUCT" VARCHAR2(10 BYTE),
"AMT" NUMBER(10,2))
PARTITION BY RANGE ("YEAR")
(PARTITION "P1" VALUES LESS THAN (1992) ,
PARTITION "P2" VALUES LESS THAN (1993) ,
PARTITION "P3" VALUES LESS THAN (1994) ,
PARTITION "P4" VALUES LESS THAN (1995) ,
PARTITION "P5" VALUES LESS THAN (MAXVALUE) ;
PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY')));
CREATE TABLE "KAJAL"."PRODUCTS"
( "PARTNO" NUMBER, "DESCRIPTION" VARCHAR2(60 BYTE) )
PARTITION BY HASH ("PARTNO")
(PARTITION "SYS_P21" TABLESPACE "USERS" ,
PARTITION "SYS_P22" TABLESPACE "USERS" ,
PARTITION "SYS_P23" TABLESPACE "USERS" ,
PARTITION "SYS_P24" TABLESPACE "USERS" ) ;
CREATE TABLE "KAJAL"."SALES"
( "YEAR" NUMBER(4,0),
"PRODUCT" VARCHAR2(10 BYTE),
"AMT" NUMBER(10,2))
PARTITION BY RANGE ("YEAR")
(PARTITION "P1" VALUES LESS THAN (1992) ,
PARTITION "P2" VALUES LESS THAN (1993) ,
PARTITION "P3" VALUES LESS THAN (1994) ,
PARTITION "P4" VALUES LESS THAN (1995) ,
PARTITION "P5" VALUES LESS THAN (MAXVALUE) ;
Automatic Partition Management for Oracle 10g
If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:
* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g* You also need to drop the oldest partition, because you have lack of storage issues
Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.
Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION 'x' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;
The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN-- Generates the name of the partition
select 'P'to_char(to_number(substr(partition_name,2,
instr(partition_name,'_',1)-2))+1)'_'
to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
replace(to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add := 'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION 'next_part;
comando_add := comando_add' VALUES LESS THAN (to_date('chr(39)less_than_char;
comando_add := comando_addchr(39)','chr(39)
'yyyy-mm-dd'chr(39)')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/
* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g* You also need to drop the oldest partition, because you have lack of storage issues
Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.
Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION 'x' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;
The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN-- Generates the name of the partition
select 'P'to_char(to_number(substr(partition_name,2,
instr(partition_name,'_',1)-2))+1)'_'
to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
replace(to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add := 'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION 'next_part;
comando_add := comando_add' VALUES LESS THAN (to_date('chr(39)less_than_char;
comando_add := comando_addchr(39)','chr(39)
'yyyy-mm-dd'chr(39)')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/
Partitioning Enhancements in Oracle Database 11g Release 1
Ref: http://www.oracle-base.com/articles/11g/PartitioningEnhancements_11gR1.php
This article uses simple examples to describe the partitioning enhancements in Oracle 11g Release 1.
Extended Composite Partitioning
Interval Partitioning
System Partitioning
Reference Partitioning
Virtual Column-Based Partitioning
Single Partition Transportable for Oracle Data Pump
Partition Advisor
Enhanced Statistics Collection for Partitioned Objects
Related articles.
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
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 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 MAXVALUE partition 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 SYSTEM clause, 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 n clause, 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 SELECT statement.
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 DEFERRABLE state (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 NULL clause.
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 MOVEMENT setting for both tables must match.
Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
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>
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>
Partition Advisor
The SQL Access Advisor, originally introduced in Oracle 10g, has been updated to include advice on partitioning existing tables, materialized views and indexes. Partitioning exiting tables can be quite a lengthy process, so don't expect this advice to provide a quick fix.
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. See here.
For more information see:
Partitioning Concepts
CREATE TABLE
Oracle Database New Features Guide 11g Release 1 (11.1)
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Hope this helps. Regards Tim...
Back to the Top.
This article uses simple examples to describe the partitioning enhancements in Oracle 11g Release 1.
Extended Composite Partitioning
Interval Partitioning
System Partitioning
Reference Partitioning
Virtual Column-Based Partitioning
Single Partition Transportable for Oracle Data Pump
Partition Advisor
Enhanced Statistics Collection for Partitioned Objects
Related articles.
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
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 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 MAXVALUE partition 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 SYSTEM clause, 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 n clause, 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 SELECT statement.
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 DEFERRABLE state (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 NULL clause.
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 MOVEMENT setting for both tables must match.
Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
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>
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>
Partition Advisor
The SQL Access Advisor, originally introduced in Oracle 10g, has been updated to include advice on partitioning existing tables, materialized views and indexes. Partitioning exiting tables can be quite a lengthy process, so don't expect this advice to provide a quick fix.
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. See here.
For more information see:
Partitioning Concepts
CREATE TABLE
Oracle Database New Features Guide 11g Release 1 (11.1)
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Hope this helps. Regards Tim...
Back to the Top.
How to Create and Manage Partition Tables in Oracle
Partitioning
Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as
Very Large Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature of
table partitioning i.e. you can partition a table according to some criteria . For example you
have a SALES table with the following structure
Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993 and 1994. And most of the time you are concerned about only one year i.e. you give queries like the following
select sum(amt) from sales where year=1991;
select product,sum(amt) from sales where year=1992
Group by product;
Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.
CREATING PARTITION TABLES
To create a partition table give the following statement
create table sales (year number(4),
product varchar2(10),
amt number(10,2))
partition by range (year)
partition p1 values less than (1992) tablespace u1,
partition p2 values less than (1993) tablespace u2,
partition p3 values less than (1994) tablespace u3,
partition p4 values less than (1995) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;
In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2. Similarly p3 and p4.
In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be able to insert any row with year above 1994.
Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces then you can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.
The above example the table is partition by range.
In Oracle you can partition a table by
Range Partitioning
Hash Partitioning
List Partitioning
Composite Partitioning
Range Partitioning
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range
Hash partitioning
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key
The following example shows how to create a hash partition table.
The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).
CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4);
List Partitioning Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
partition mapping. List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally The following example creates a table with list partitioning Create table customers (custcode number(5), Name varchar2(20), Addr varchar2(10,2), City varchar2(20), Bal number(10,2)) Partition by list (city),Partition north_India values (‘DELHI’,’CHANDIGARH’),Partition east_India values (‘KOLKOTA’,’PATNA’),Partition south_India values (‘HYDERABAD’,’BANGALORE’, ’CHENNAI’),Partition west India values (‘BOMBAY’,’GOA’); If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.
COMPOSITE PARTITONING
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating composite partitions, you specify the following:
Partitioning method: range
Partitioning column(s)
Partition descriptions identifying partition bounds
Subpartitioning method: hash
Subpartitioning column(s)
Number of subpartitions for each partition or descriptions of subpartitions
The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4). CREATE TABLE PRODUCTS (partno NUMBER, description VARCHAR(32), costprice NUMBER) PARTITION BY RANGE (partno) SUBPARTITION BY HASH(description) SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (MAXVALUE));
ALTERING PARTITION TABLES To add a partition You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition
at the beginning or in the middle of a table, use the SPLIT PARTITION clause. For example to add a partition to sales table give the following command. alter table sales add partition p6 values less than (1996); To add a partition to a Hash Partition table give the following command. Alter table products add partition; Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
To add a partition by user define name and in your specified tablespace give the following command. Alter table products add partition p5 tablespace u5; To add a partition to a List partition table give the following command. alter table customers add partition central_India values (‘BHOPAL’,’NAGPUR’);
Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.
Coalescing Partitions
Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.
To coalesce a hash partition give the following statement.
Alter table products coalesce partition;
This reduces by one the number of partitions in the table products.
DROPPING PARTITIONS
To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.
Alter table sales drop partition p5;
Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.
Alter index sales_ind rebuild;
To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then dropthe partition like this
Delete from sales where year=1994;
Alter table sales drop partition p4;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Another method of dropping partitions is give the following statement.ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES; This causes the global index to be updated at the time the partition is dropped.
Exchanging a Range, Hash, or List Partition
To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned. ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;
Merging Partitions
Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot merge non adjacent partitions.
For example the merge the partition p2 and p3 into one partition p23 give the following statement.
Alter table sales merge partition p2 and p3 into
partition p23;
Modifying Partitions: Adding Values
Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.
The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list. ALTER TABLE customers MODIFY PARTITION south_india ADD VALUES ('KOCHI', 'MANGALORE');
Modifying Partitions: Dropping Values
Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead.
The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.
The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list. ALTER TABLE customers MODIFY PARTITION south_india DROP VALUES (‘KOCHI’,’MANGALORE’);
SPLITTING PARTITIONS You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command. Alter table sales split partition p5 into (Partition p6 values less than (1996), Partition p7 values less then (MAXVALUE));
TRUNCATING PARTITON Truncating a partition will delete all rows from the partition. To truncate a partition give the following statement Alter table sales truncate partition p5;
LISTING INFORMATION ABOUT PARTITION TABLES To see how many partitioned tables are there in your schema give the following statement Select * from user_part_tables; To see on partition level partitioning information Select * from user_tab_partitions;
Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as
Very Large Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature of
table partitioning i.e. you can partition a table according to some criteria . For example you
have a SALES table with the following structure
Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993 and 1994. And most of the time you are concerned about only one year i.e. you give queries like the following
select sum(amt) from sales where year=1991;
select product,sum(amt) from sales where year=1992
Group by product;
Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.
CREATING PARTITION TABLES
To create a partition table give the following statement
create table sales (year number(4),
product varchar2(10),
amt number(10,2))
partition by range (year)
partition p1 values less than (1992) tablespace u1,
partition p2 values less than (1993) tablespace u2,
partition p3 values less than (1994) tablespace u3,
partition p4 values less than (1995) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;
In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2. Similarly p3 and p4.
In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be able to insert any row with year above 1994.
Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces then you can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.
The above example the table is partition by range.
In Oracle you can partition a table by
Range Partitioning
Hash Partitioning
List Partitioning
Composite Partitioning
Range Partitioning
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range
Hash partitioning
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key
The following example shows how to create a hash partition table.
The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).
CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4);
List Partitioning Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
partition mapping. List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally The following example creates a table with list partitioning Create table customers (custcode number(5), Name varchar2(20), Addr varchar2(10,2), City varchar2(20), Bal number(10,2)) Partition by list (city),Partition north_India values (‘DELHI’,’CHANDIGARH’),Partition east_India values (‘KOLKOTA’,’PATNA’),Partition south_India values (‘HYDERABAD’,’BANGALORE’, ’CHENNAI’),Partition west India values (‘BOMBAY’,’GOA’); If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.
COMPOSITE PARTITONING
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating composite partitions, you specify the following:
Partitioning method: range
Partitioning column(s)
Partition descriptions identifying partition bounds
Subpartitioning method: hash
Subpartitioning column(s)
Number of subpartitions for each partition or descriptions of subpartitions
The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4). CREATE TABLE PRODUCTS (partno NUMBER, description VARCHAR(32), costprice NUMBER) PARTITION BY RANGE (partno) SUBPARTITION BY HASH(description) SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (MAXVALUE));
ALTERING PARTITION TABLES To add a partition You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition
at the beginning or in the middle of a table, use the SPLIT PARTITION clause. For example to add a partition to sales table give the following command. alter table sales add partition p6 values less than (1996); To add a partition to a Hash Partition table give the following command. Alter table products add partition; Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
To add a partition by user define name and in your specified tablespace give the following command. Alter table products add partition p5 tablespace u5; To add a partition to a List partition table give the following command. alter table customers add partition central_India values (‘BHOPAL’,’NAGPUR’);
Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.
Coalescing Partitions
Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.
To coalesce a hash partition give the following statement.
Alter table products coalesce partition;
This reduces by one the number of partitions in the table products.
DROPPING PARTITIONS
To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.
Alter table sales drop partition p5;
Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.
Alter index sales_ind rebuild;
To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then dropthe partition like this
Delete from sales where year=1994;
Alter table sales drop partition p4;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Another method of dropping partitions is give the following statement.ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES; This causes the global index to be updated at the time the partition is dropped.
Exchanging a Range, Hash, or List Partition
To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned. ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;
Merging Partitions
Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot merge non adjacent partitions.
For example the merge the partition p2 and p3 into one partition p23 give the following statement.
Alter table sales merge partition p2 and p3 into
partition p23;
Modifying Partitions: Adding Values
Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.
The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list. ALTER TABLE customers MODIFY PARTITION south_india ADD VALUES ('KOCHI', 'MANGALORE');
Modifying Partitions: Dropping Values
Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead.
The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.
The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list. ALTER TABLE customers MODIFY PARTITION south_india DROP VALUES (‘KOCHI’,’MANGALORE’);
SPLITTING PARTITIONS You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command. Alter table sales split partition p5 into (Partition p6 values less than (1996), Partition p7 values less then (MAXVALUE));
TRUNCATING PARTITON Truncating a partition will delete all rows from the partition. To truncate a partition give the following statement Alter table sales truncate partition p5;
LISTING INFORMATION ABOUT PARTITION TABLES To see how many partitioned tables are there in your schema give the following statement Select * from user_part_tables; To see on partition level partitioning information Select * from user_tab_partitions;
Partitioning an Existing Table using EXCHANGE PARTITION
Ref: http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php
This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax. The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. Remember, in many cases incorrect partitioning is worse than no partitioning!
Create a Sample Schema
Create a Partitioned Destination Table
EXCHANGE PARTITION
SPLIT PARTITION
Related articles.
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Create a Sample Schema
First we create a sample schema as our starting point.
-- Create and populate a small lookup table.
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
Create a Partitioned Destination Table
Next we create a new table with the appropriate partition structure to act as the destination table. The destination must have the same constraints and indexes defined.
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
With this destination table in place we can start the conversion.
EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.
ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
The exchange operation should not be affected by the size of the segments involved.
Once this is complete we can drop the old table and rename the new table and all it's constraints.
DROP TABLE big_table;
RENAME big_table2 TO big_table;
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required.
ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;
ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
The following queries show that the partitioning was successful.
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005 335326
BIG_TABLE_2006 332730
BIG_TABLE_2007 334340
3 rows selected.
For more information see:
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Online Table Redefinition
Hope this helps. Regards Tim...
Back to the Top.
This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax. The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. Remember, in many cases incorrect partitioning is worse than no partitioning!
Create a Sample Schema
Create a Partitioned Destination Table
EXCHANGE PARTITION
SPLIT PARTITION
Related articles.
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Create a Sample Schema
First we create a sample schema as our starting point.
-- Create and populate a small lookup table.
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
Create a Partitioned Destination Table
Next we create a new table with the appropriate partition structure to act as the destination table. The destination must have the same constraints and indexes defined.
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
With this destination table in place we can start the conversion.
EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.
ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
The exchange operation should not be affected by the size of the segments involved.
Once this is complete we can drop the old table and rename the new table and all it's constraints.
DROP TABLE big_table;
RENAME big_table2 TO big_table;
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required.
ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;
ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
The following queries show that the partitioning was successful.
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005 335326
BIG_TABLE_2006 332730
BIG_TABLE_2007 334340
3 rows selected.
For more information see:
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Online Table Redefinition
Hope this helps. Regards Tim...
Back to the Top.
Partitioned Tables And Indexes
Ref: http://www.oracle-base.com/articles/8i/PartitionedTablesAndIndexes.php
Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.
Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
Query performance can be improved as access can be limited to relevant partitons only.
There is a greater ability for parallelism with more partitions.
All the examples shown here use the users tablespace for all partitions. In a real situation it is likely that these partitions would be assigned to different tablespaces to reduce device contention.
Range Partitioning Tables
Hash Partitioning Tables
Composite Partitioning Tables
Partitioning Indexes
Local Prefixed Indexes
Local Non-Prefixed Indexes
Global Prefixed Indexes
Global Non-Prefixed Indexes
Partitioning Existing Tables
Related articles.
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Range Partitioning Tables
Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
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 (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);
Hash Partitioning Tables
Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);
Or specified individually.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Composite Partitioning Tables
Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));
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.
For more information see:
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Hope this helps. Regards Tim...
Back to the Top.
Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.
Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
Query performance can be improved as access can be limited to relevant partitons only.
There is a greater ability for parallelism with more partitions.
All the examples shown here use the users tablespace for all partitions. In a real situation it is likely that these partitions would be assigned to different tablespaces to reduce device contention.
Range Partitioning Tables
Hash Partitioning Tables
Composite Partitioning Tables
Partitioning Indexes
Local Prefixed Indexes
Local Non-Prefixed Indexes
Global Prefixed Indexes
Global Non-Prefixed Indexes
Partitioning Existing Tables
Related articles.
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Range Partitioning Tables
Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
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 (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);
Hash Partitioning Tables
Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);
Or specified individually.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Composite Partitioning Tables
Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions.
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));
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.
For more information see:
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning Enhancements in Oracle Database 11g Release 1
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Hope this helps. Regards Tim...
Back to the Top.
Subscribe to:
Comments (Atom)