Oracle分區表技術 (Partitioned Tables)，partitionedtables
自Oracle 8（1997年左右）就引入了分區表&分區索引(Partitioned Tables & Indexes)的概念來調整大表和大索引，提升效能，提升營運管理的能力。分區表和分區索引機制是海量資料庫管理（Very Large Databases ，即VLDB) 中一個重要的提升效能的機制。
Oracle分區技術的曆史Oracle 8引入了分區的概念，後續的每個版本都有對分區機制最佳化和改進。
使用分區表/索引的好處
- 效能提升： Select語句只檢索本分區的記錄，減少了記錄總數，可有效提升了查詢效能。另外，可以通過資料表空間將分區映射到不同的物理磁碟上，分散裝置IO，提升效能；
- 分區營運：可以針對不同分區，管理資料的載入、索引的建立(以及重建)、資料備份與恢複。因為可針對分區的管理，所以可以有效降低了分區間的幹擾、影響。
更多分區的好處可以閱讀Oracle Partitioning這篇文章，寫的更加詳細些。
什麼時候使用分區表什麼時候使用分區表，並沒有一個精確的界限，Oracle只有一些通用的建議，具體使用需要自行評估：
- Tables greater than 2GB should 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
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; |
分區表的索引可以是分區索引，也可以是非分區索引（普通索引）。
如何分區（分區的方法）
Partition Key分區表/索引是以一個或多個欄位為依據來決定記錄儲存在哪個分區，被選用的分區欄位稱為Partition Key，Oracle會根據Partition Key自動找到對應的分區中做insert, update, delete操作。
分區的方法Oracle提供了6種分區方法：
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 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;
--建立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 into Invoices values('1',TO_DATE('2010-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2010 invoice');
insert into Invoices values('2',TO_DATE('2011-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2011 invoice');
insert into Invoices values('3',TO_DATE('2012-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2012 invoice');
insert into Invoices values('4',TO_DATE('2013-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2013 invoice');
commit;
--查詢確認，是不是資料被儲存到正確的分區SQL> select * from Invoices;
INVOICE_NO INVOICE_CREATION_DAY INVOICE_DATA
---------- -------------------- ------------------------------
1 2010/6/10 2010 invoice
2 2011/7/20 2011 invoice
3 2012/8/25 2012 invoice
4 2013/8/25 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/7/20 2011 invoice
List Partitioning Example--建立資料表空間
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;
--把不同的州劃分到不同的分區(資料表空間)中
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;
--檢查分區是否建立正確
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'PARTITION_BY_LIST'
ORDER BY TABLESPACE_NAME;
--插入測試資料
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;
--查詢確認，是不是資料被儲存到正確的分區
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]如何知道一個表或索引是否有被分區SELECT * FROM dba_part_tables;
SELECT * FROM dba_part_indexes;
[Query]如何列出分區表或索引的分區對象SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';
SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';
分區表的資料匯出匯入
全表匯出C:\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, 2009, 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.
分區表匯出C:\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, 2009, 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.
全表匯入
imp system/welcome@ptian file=test.dmp full=y
分區表匯入
imp system/welcome@ptianfile=test_part.dmp full=y
資料泵expdp/impdp的方式匯出整個表
expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3
匯出多個分區
expdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2
匯入單個分區
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
匯入整個表
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
參考：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
