Oracle Partition Table Technology (Partitioned Tables), partitionedtables
Since Oracle 8 (around 1997), the concept of Partition Table & partition index (Partitioned Tables & Indexes) has been introduced to adjust large Tables and large Indexes to improve performance and improve O & M management capabilities. The partition table and partition index mechanisms are an important mechanism to improve performance in the management of massive Databases (Very Large Databases, VLDB.
History of Oracle Partitioning technologyOracle 8 introduces the concept of partitioning. In subsequent versions, the partitioning mechanism is optimized and improved.
Benefits of Using Partitioned Tables/Indexes
- Performance improvement:The Select statement only retrieves records in the current partition, reducing the total number of records and effectively improving query performance. In addition, partitions can be mapped to different physical disks through tablespaces, which disperses device IO and improves performance;
- Partition O & M:You can manage data loading, index creation (and Reconstruction), and data backup and recovery for different partitions. Because partition management can effectively reduce the interference and impact of intervals.
For more information about the benefits of Partitioning, see Oracle Partitioning.
When to use a partition tableWhen to use a partition table, there is no precise boundary. Oracle only has some general suggestions. You need to evaluate the specific usage:
- Tables greater than 2 GB shoshould always be considered for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
Tip: SQL statement used to calculate the table size
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 partition table can be a partition index or a non-partition index (common index ).
How to partition (partition method)
Partition KeyThe Partition table/index determines the Partition in which the record is stored based on one or more fields. The selected Partition field is called the Partition Key, oracle will automatically find the corresponding Partition based on the Partition Key for insert, update, and delete operations.
Partitioning MethodOracle provides six partitioning methods:
Partitioning Method
|
Brief Description
|
Range Partitioning
|
Used when there are 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, then spreads data into hash partitions. Possible usage: range partition by date of birth then hash partition by name; store the results into the hash partitions
|
Composite Range-List Partitioning
|
Used to range partition first, then spreads data into list partitions. Possible usage: range partition by date of birth then list partition by state, then store the results into the list partitions |
Examples
Range Partitioning Example-- Create tablespace invoices_2010 datafile 'e: \ app \ TianPan \ oradata \ tbs01.dbf' size 50 m;
Create tablespace invoices_2011 datafile 'e: \ app \ TianPan \ oradata \ tbs02.dbf' size 50 m;
Create tablespace invoices_2012 datafile 'e: \ app \ TianPan \ oradata \ tbs03.dbf' size 50 m;
Create tablespace invoices_2013 datafile 'e: \ app \ TianPan \ oradata \ tbs04.dbf' size 50 m;
-- Create a Range-type Partition Table
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 ('2017-12-31 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') TABLESPACE invoices_2010,
PARTITION part02 values less than (TO_DATE ('2017-12-31 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') TABLESPACE invoices_2011,
PARTITION part03 values less than (TO_DATE ('2017-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 ('2017-06-10 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), '2017 invoice ');
Insert into Invoices values ('2', TO_DATE ('2017-07-20 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), '2017 invoice ');
Insert into Invoices values ('3', TO_DATE ('2017-08-25 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), '2017 invoice ');
Insert into Invoices values ('4', TO_DATE ('2017-08-25 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), '2017 invoice ');
Commit;
-- Check whether the data is stored in the correct partition SQL> select * from Invoices;
INVOICE_NO INVOICE_CREATION_DAY INVOICE_DATA
------------------------------------------------------------
1 2010/6/10 2010 invoice
2 2011 invoice
3 2012 invoice
4 2013 invoice
SQL> select * from Invoices partition (part01 );
INVOICE_NO INVOICE_CREATION_DAY INVOICE_DATA
------------------------------------------------------------
1 2010/6/10 2010 invoice
SQL> select * from Invoices partition (part02 );
INVOICE_NO INVOICE_CREATION_DAY INVOICE_DATA
------------------------------------------------------------
2 2011 invoice
List Partitioning Example-- Create a tablespace
Create tablespace TS01 datafile 'e: \ app \ TianPan \ oradata \ list \ tbs01.dbf' size 50 m;
Create tablespace TS02 datafile 'e: \ app \ TianPan \ oradata \ list \ tbs02.dbf' size 50 m;
Create tablespace TS03 datafile 'e: \ app \ TianPan \ oradata \ list \ tbs03.dbf' size 50 m;
Create tablespace TS04 datafile 'e: \ app \ TianPan \ oradata \ list \ tbs04.dbf' size 50 m;
Create tablespace TS05 datafile 'e: \ app \ TianPan \ oradata \ list \ tbs05.dbf' size 50 m;
-- Divide different states into different partitions (tablespace ).
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 whether 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;
-- Insert 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;
-- Check whether 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 do I know whether a table or index is partitioned?SELECT * FROM dba_part_tables;
SELECT * FROM dba_part_indexes;
[Query] how to list partition objects of partition tables or IndexesSELECT * FROM dba_tab_partitions WHERE table_name = '<table_name> ';
SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name> ';
Partition Table data export and import
Full table ExportC: \ Users \ TianPan>
Exp system/welcome @ ptian 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,200 9, 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/welcome @ ptian 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,200 9, 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/welcome @ ptian file = test. dmp full = y
Partition Table Import
Imp system/welcome @ ptianfile = test_part.dmp full = y
Data Pump expdp/impdp ModeExport the 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 the 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
Refer:Partitioned Tables And Indexes Partitioning an Oracle table TipsOracle Partitioned Tables & Indexes Oracle®Database VLDB and Partitioning GuideAll about Partitions Oracle Partitioning PartitioningPartitioning FAQNew Whitepaper: Database Partitioning 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
