Since Oracle 8 (around 1997), the concept of partitioned Tables & partitioned Indexes (partitioned Tables & Indexes) has been introduced to adjust large tables and large indexes to improve performance and improve operational management capabilities. Partitioned tables and partitioned index mechanisms are massive database management (Very Large Databases , or VLDB), is an important mechanism for improving performance.
History of Oracle Partitioning technologyOracle 8 introduces the concept of partitioning, with each subsequent release having optimizations and improvements to the partitioning mechanism.
benefits of using partitioned tables/indexes
- Performance Improvements: The SELECT statement retrieves only the records for the subregion, reducing the total number of records, which effectively improves query performance. In addition, the partition can be mapped to different physical disks through the table space, and the device IO is dispersed to improve the performance.
- partition operations: You can manage the loading of data, the creation of indexes (and rebuilding), data backup and recovery for different partitions. Because it can be used for the management of partition, it can effectively reduce the interference and influence between partitions.
The benefits of more partitioning can be read in the Oracle partitioning article, written in more detail.
when to use a partitioned tableWhen using partitioned tables without a precise boundary, Oracle has only a few common recommendations that need to be evaluated on its own:
- Tables greater than 2GB should always being considered for partitioning.
- Tables containing historical data, in which new data are added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other one months are read Only.
TIP: Calculate table-Size SQL
SELECT B.owner,
B.tablespace_name,
B.table_name,
ROUND (SUM (BYTES)/1024/1024/1024, 6) GIGS
From SYS. Dba_extents A, SYS. Dba_tables B
WHERE ((b.tablespace_name = a.tablespace_name)
and (B.owner = UPPER (' &owner ')) and (B.table_name = ' &table '))
GROUP by B.owner, B.tablespace_name, B.table_name; |
The index of a partitioned table can be either a partitioned index or a non-partitioned index (normal index).
How to partition (method of partitioning)
Partition KeyPartition Table/index is based on one or more fields to determine which partition the record is stored in, the selected partition field is called Partition key,oracle will automatically find the corresponding partition according to partition Key to do insert, UPDATE, delete operation.
Methods of PartitioningOracle provides 6 partitioning methods:
Partitioning Method
|
Brief Description
|
Range Partitioning
|
Used when there is logical ranges of data. Possible usage:dates, part numbers, and serial numbers
|
Hash Partitioning
|
Used to spread data evenly over partitions. Possible Usage:data has no logical groupings
|
List Partitioning
|
Used to list together unrelated data into partitions. Possible usage:a Number of states list partitioned into a region
|
Composite Range-hash Partitioning
|
Used to range partition first and then spreads data into hash partitions. Possible Usage:range partition by the date of birth then hash partition by name; Store the results into the hash partitions
|
Composite Range-list Partitioning
|
Used to range partition first and then spreads data into list partitions. Possible Usage:range partition by date of birth then list partition by state and then store the results into the list Partit Ions |
Examples
Range Partitioning Example--Create TABLE spaceCreate tablespace invoices_2010 datafile ' e:\app\TianPan\oradata\tbs01.dbf ' size 50m;
Create tablespace invoices_2011 datafile ' e:\app\TianPan\oradata\tbs02.dbf ' size 50m;
Create tablespace invoices_2012 datafile ' e:\app\TianPan\oradata\tbs03.dbf ' size 50m;
Create tablespace invoices_2013 datafile ' e:\app\TianPan\oradata\tbs04.dbf ' size 50m;
--Create a partition table of type range
CREATE TABLE Invoices (
Invoice_no VARCHAR2 (10),
Invoice_creation_day DATE,
Invoice_data VARCHAR2 (30)
)
PARTITION by RANGE (Invoice_creation_day)
(
PARTITION part01 VALUES Less THAN (to_date (' 2010-12-31 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')) tablespace invoices_2010,
PARTITION part02 VALUES Less THAN (to_date (' 2011-12-31 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')) tablespace invoices_2011,
PARTITION part03 VALUES Less THAN (to_date (' 2012-12-31 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')) tablespace invoices_2012,
PARTITION part04 VALUES less THAN (MAXVALUE) tablespace invoices_2013
);
--Insert test data by year
Insert into invoices values (' 1 ', to_date (' 2010-06-10 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Invoice ');
Insert into invoices values (' 2 ', to_date (' 2011-07-20 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Invoice ');
Insert into invoices values (' 3 ', to_date (' 2012-08-25 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Invoice ');
Insert into invoices values (' 4 ', to_date (' 2013-08-25 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Invoice ');
commit;
--Query confirmation, is not the data is stored in the correct partitionSql> select * from Invoices;
Invoice_no Invoice_creation_day Invoice_data
---------- -------------------- ------------------------------
1 2010/6/10 Invoice
2 2011/7/20 Invoice
3 2012/8/25 Invoice
4 2013/8/25 Invoice
Sql> SELECT * from Invoices partition (PART01);
Invoice_no Invoice_creation_day Invoice_data
---------- -------------------- ------------------------------
1 2010/6/10 Invoice
Sql> SELECT * from Invoices partition (part02);
Invoice_no Invoice_creation_day Invoice_data
---------- -------------------- ------------------------------
2 2011/7/20 Invoice
List Partitioning Example--Create TABLE space
Create tablespace TS01 datafile ' e:\app\TianPan\oradata\list\tbs01.dbf ' size 50m;
Create tablespace TS02 datafile ' e:\app\TianPan\oradata\list\tbs02.dbf ' size 50m;
Create tablespace TS03 datafile ' e:\app\TianPan\oradata\list\tbs03.dbf ' size 50m;
Create tablespace TS04 datafile ' e:\app\TianPan\oradata\list\tbs04.dbf ' size 50m;
Create tablespace TS05 datafile ' e:\app\TianPan\oradata\list\tbs05.dbf ' size 50m;
--dividing different states into different partitions (tablespaces)
CREATE TABLE Partition_by_list
(DeptID number,
Deptname VARCHAR2 (15),
State VARCHAR2 (2),
CONSTRAINT partition_by_list_pk PRIMARY KEY (DeptID))
PARTITION by LIST (state)
(PARTITION Depts_in_north VALUES (' AK ') tablespace TS01,
PARTITION depts_in_east VALUES (' NY ', ' NJ ', ' VA ', ' CT ') tablespace TS02,
PARTITION depts_in_south VALUES (' TX ', ' MS ', ' GA ', ' KY ') tablespace TS03,
PARTITION depts_in_west VALUES (' CA ', ' AZ ', ' OR ', ' NV ') tablespace TS04,
PARTITION depts_with_no_region VALUES (DEFAULT) tablespace TS05)
ENABLE ROW movement;
--Check that the partition is created correctly
SELECT table_name, partition_name, High_value, Tablespace_name
From User_tab_partitions
WHERE table_name = ' partition_by_list '
ORDER by Tablespace_name;
--Inserting test data
INSERT into Partition_by_list (DeptID, Deptname, State) VALUES (1, ' Anchorage ', ' AK ');
INSERT into Partition_by_list (DeptID, Deptname, State) VALUES (2, ' NEW YORK ', ' NY ');
INSERT into Partition_by_list (DeptID, Deptname, State) VALUES (3, ' DALLAS ', ' TX ');
INSERT into Partition_by_list (DeptID, Deptname, State) VALUES (4, ' LOS ANGELES ', ' CA ');
INSERT into Partition_by_list (DeptID, Deptname, State) VALUES (5, ' WAIKIKI ', ' HI ');
COMMIT;
--Query confirmation, is not the data is stored in the correct partition
Sql> select * from Partition_by_list;
DeptID Deptname State
---------- --------------- -----
1 Anchorage AK
2 NEW YORK NY
3 DALLAS TX
4 LOS ANGELES CA
5 WAIKIKI HI
Sql> SELECT * from Partition_by_list PARTITION (Depts_in_north);
DeptID Deptname State
---------- --------------- -----
1 Anchorage AK
Sql> SELECT * from Partition_by_list PARTITION (depts_in_east);
DeptID Deptname State
---------- --------------- -----
2 NEW YORK NY
Sql> SELECT * from Partition_by_list PARTITION (depts_with_no_region);
DeptID Deptname State
---------- --------------- -----
5 WAIKIKI HI
Useful Query
[Query] How to know if a table or index has been partitionedSELECT * from Dba_part_tables;
SELECT * from Dba_part_indexes;
[Query] How to list partition objects for partitioned tables or indexesSELECT * FROM dba_tab_partitions WHERE table_name = ' <table_name> ';
SELECT * from dba_ind_partitions WHERE index_name = ' <index_name> ';
Data Export Import for partitioned tables
Full Table ExportC:\users\tianpan>
exp System/[email protected] file=c:\test.dmp tables=invoices
Export:release 11.2.0.1.0-production on Fri Jan 9 16:18:05 2015
Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.
Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production
With the partitioning, OLAP, Data Mining and Real application testing options
Export done in ZHS16GBK character set and Al16utf16 NCHAR character set
About to export specified tables via conventional Path ...
. . Exporting table Invoices
. . Exporting partition PART01 1 rows exported
. . Exporting partition PART02 1 rows exported
. . Exporting partition PART03 1 rows exported
. . Exporting partition PART04 1 rows exported
Export terminated successfully without warnings.
Partition Table ExportC:\users\tianpan>
exp System/[email protected] file=c:\test_part.dmp tables=invoices:part03
Export:release 11.2.0.1.0-production on Fri Jan 9 16:21:04 2015
Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.
Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production
With the partitioning, OLAP, Data Mining and Real application testing options
Export done in ZHS16GBK character set and Al16utf16 NCHAR character set
About to export specified tables via conventional Path ...
. . Exporting table Invoices
. . Exporting partition PART03 1 rows exported
Export terminated successfully without warnings.
Full Table Import
imp system/[email protected] file=test.dmp full=y
Partition Table Import
imp system/[email protected]file=test_part.dmp full=y
How the data pump EXPDP/IMPDPExport an entire table
EXPDP scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3
Export Multiple partitions
EXPDP scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log tables= (tb_pt:sal_16,tb_pt:sal_other) Parallel =2
Import a single partition
IMPDP scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_ Indexes=y Table_exists_action=replace
Import an entire table
IMPDP scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log tables=tb_pt skip_unusable_indexes=y Table_exists_action=replace
Reference:Partitioned Tables and Indexes partitioning an Oracle table tipsoracle partitioned Tables & Indexes oracle®database V LDB and partitioning guideall about partitions Oracle partitioning partitioningpartitioning faqnew whitepaper:database Pa Rtitioning for the e-business suiteusing Database partitioning with the e-business Suite Oracle Metalink note:554539.1 -Using Database partitioning with Oracle e-business Suite
Oracle Partition Table Technology (partitioned Tables)
