Oracle database 12c Release 2中引入了許多對Oracle數(shù)據(jù)庫分區(qū)選項的增強。

其中包括：

• 自動列表分區(qū)

• 多列列表分區(qū)

• 只讀分區(qū)

• 篩選分區(qū)維護操作

• 在線將非分區(qū)表轉(zhuǎn)換為分區(qū)表

• 分區(qū)外部表 

與Oracle Database 12c Release 1中引入的間隔分區(qū)方法類似，Oracle 12c Release 2中引入的一個新分區(qū)特性是自動列表分區(qū)特性，它使分區(qū)表更易于管理。

基于列中不同值對表進行分區(qū)的能力已經(jīng)存在了很長一段時間，但Oracle database 12.2中的新功能是，我們可以首先基于已知的值對表進行分區(qū)，這些值出現(xiàn)在要對表進行分區(qū)的列中。當(dāng)數(shù)據(jù)加載到表中時，如果加載的分區(qū)鍵值與任何現(xiàn)有分區(qū)都不對應(yīng)，則數(shù)據(jù)庫會自動創(chuàng)建新分區(qū)。

在Oracle Database 12c Release 2中，我們現(xiàn)在可以基于多列的值列表來劃分表，而不是像以前版本那樣僅基于一列。這稱為多列列表分區(qū)。

因此，如果我們有一個包含整個國家的銷售數(shù)據(jù)的非常大的表，我們可以根據(jù)一個州和位于同一州的一個城市來劃分表。

另一個新特性是只讀分區(qū)。因此，整個表不是只讀的，但是表中的一些分區(qū)可以是只讀的。也許我們需要存儲歷史數(shù)據(jù)，表中較舊的分區(qū)可以設(shè)置為只讀，以防止對此類“舊”數(shù)據(jù)執(zhí)行任何DML操作。

作為DBA，我們必須定期執(zhí)行的一個常見分區(qū)維護操作是移動分區(qū)—可能表中較舊的分區(qū)被移動到低成本存儲上托管的另一個表空間，因為這些數(shù)據(jù)很少被訪問?，F(xiàn)在在Oracle數(shù)據(jù)庫12.2中，我們可以將移動分區(qū)、合并分區(qū)和拆分分區(qū)等分區(qū)維護操作與數(shù)據(jù)過濾結(jié)合起來。例如，在移動特定分區(qū)的數(shù)據(jù)時，我們還可以過濾將要移動的數(shù)據(jù)。通過INCLUDING ROWS關(guān)鍵字指定過濾謂詞，我們可以確定在執(zhí)行分區(qū)維護操作時應(yīng)該保留哪些數(shù)據(jù)以及可以丟棄哪些數(shù)據(jù)。

但是12.2的一個最好的新特性是，我們現(xiàn)在可以通過帶有MODIFY子句的ALTER table命令，以及在表上執(zhí)行并發(fā)DML操作時，非常容易地將非分區(qū)表轉(zhuǎn)換為分區(qū)表。因此，表的轉(zhuǎn)換是一個在線過程，通過updateindexes子句，我們還可以將表上的任何索引轉(zhuǎn)換為本地或全局分區(qū)索引。

我們現(xiàn)在還可以創(chuàng)建一個分區(qū)的外部表。因此，基本上，我們是基于Oracle數(shù)據(jù)庫之外的數(shù)據(jù)創(chuàng)建一個表，并對包含這些外部數(shù)據(jù)的表進行分區(qū)，以便更好地優(yōu)化查詢，如果我們處理大量的這些外部數(shù)據(jù)集，這將非常有益。

讓我們看看所有這些12c版本2分區(qū)的新特性。

首先創(chuàng)建一個帶有索引的測試非分區(qū)表，并在表中插入一些數(shù)據(jù)。

SQL> CREATE TABLE sales_by_state 
 (sales_state VARCHAR2(3), 
  sales_city VARCHAR2(20),
  sales_quarter NUMBER,
  sales_amount  NUMBER);
 
Table created.

SQL> CREATE INDEX sales_state_ind ON sales_by_state (sales_state);

Index created.


SQL> INSERT INTO sales_by_state 
 VALUES 
 ('WA','PERTH',1,10000);  

1 row created.

SQL> INSERT INTO sales_by_state 
VALUES 
('WA','ALBANY',1,9500);   

1 row created.

SQL> INSERT INTO sales_by_state 
VALUES 
('VIC','MELBOURNE',1,75000);   

1 row created.

SQL> INSERT INTO sales_by_state 
VALUES 
('VIC','GEELONG',1,6500);    

1 row created.

SQL> INSERT INTO sales_by_state 
VALUES 
('NSW','SYDNEY',1,50000);    

1 row created.

SQL> COMMIT;

Commit complete.


SQL> SELECT * FROM sales_by_state;

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA  PERTH     1      10000
WA  ALBANY     1       9500
VIC MELBOURNE             1      75000
VIC GEELONG     1       6500
NSW SYDNEY     1      50000

現(xiàn)在，我們在線將非分區(qū)表轉(zhuǎn)換為多列列表分區(qū)表，并在表上創(chuàng)建本地分區(qū)索引以及同一操作的一部分。

SQL> ALTER TABLE system.sales_by_state 
MODIFY 
PARTITION BY LIST (sales_state,sales_city) AUTOMATIC 
(PARTITION p_wa_1 VALUES ('WA','PERTH'),
 PARTITION p_wa_2 values ('WA','ALBANY'),
 PARTITION p_vic_1 values ('VIC','MELBOURNE'),
 PARTITION p_vic_2 values ('VIC','GEELONG'),
 PARTITION p_nsw_1 values ('NSW','SYDNEY'))
 ONLINE 
 UPDATE INDEXES;   

Table altered.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';

PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2


SQL> select partition_name from user_IND_PARTITIONS WHERE INDEX_NAME='SALES_STATE_IND';

PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2

請注意，當(dāng)我們在分區(qū)表中插入一些新行時，表中還沒有state和city值。

SQL> INSERT INTO sales_by_state 
     VALUES 
     ('SA','ADELAIDE',1,10500);

1 row created.

SQL> INSERT INTO sales_by_state 
     VALUES
     ('SA','ADELAIDE',2,8000);

1 row created.

SQL> COMMIT; 

Commit complete.

SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';

PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
SYS_P1323

6 rows selected.

SQL> SELECT COUNT(*) FROM sales_by_state PARTITION(SYS_P1323);

  COUNT(*)
----------
 2

將表中的一個分區(qū)設(shè)為只讀。

SQL> ALTER TABLE sales_by_state MODIFY PARTITION p_wa_1 READ ONLY;

Table altered.


SQL> INSERT INTO sales_by_state 
     VALUES 
    ('WA','PERTH',2,5000);

INSERT INTO sales_by_state
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

接下來，我們將創(chuàng)建一個外部分區(qū)表。我們有4個平面文件，其中包含特定州的銷售信息，現(xiàn)在我們要創(chuàng)建一個按州名稱分區(qū)的表。

SQL> !cat salesdata_wa.txt
WA PERTH 1 10000
WA ALBANY 1 9500

SQL> !cat salesdata_nsw.txt
NSW SYDNEY 1 50000

SQL> !cat salesdata_vic.txt 
VIC MELBOURNE 1 75000
VIC GEELONG 1 6500

SQL> !cat salesdata.txt
SA ADELAIDE 1 10500
SA ADELAIDE 2 8000

CREATE TABLE system.sales_by_state 
(sales_state VARCHAR2(3), 
 sales_city VARCHAR2(20),
sales_quarter NUMBER,
 sales_amount  NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE)
)
PARTITION BY LIST (sales_state) 
(PARTITION p_wa VALUES ('WA') LOCATION ('salesdata_wa.txt'),
 PARTITION p_vic values ('VIC') LOCATION ('salesdata_vic.txt'),
 PARTITION p_nsw values ('NSW') LOCATION ('salesdata_nsw.txt'),
 PARTITION p_others values (DEFAULT) LOCATION ('salesdata.txt')
)
; 
Table created.

查詢已分區(qū)的外部表。

SQL> SELECT * FROM sales_by_state;

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA  PERTH     1      10000
WA  ALBANY     1       9500
VIC MELBOURNE     1      75000
VIC GEELONG     1       6500
NSW SYDNEY     1      50000
SA  ADELAIDE     1      10500
SA  ADELAIDE     2       8000

7 rows selected.

SQL> SELECT * FROM sales_by_state partition (p_wa);

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA  PERTH     1      10000
WA  ALBANY     1       9500

SQL> SELECT * FROM sales_by_state partition (p_nsw);

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
NSW SYDNEY     1      50000

在本例中，我們將把一個分區(qū)移動到另一個表空間，可能是為了存檔數(shù)據(jù)，作為分區(qū)維護操作的一部分，我們只想保留滿足特定篩選器的行，在本例中，該篩選器僅是分區(qū)中屬于SALES_QUARTER列且值為2的行。不符合此篩選條件的所有其他行將作為移動分區(qū)操作的一部分被清除。

SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_);

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA  PERTH     1      10000
WA  ALBANY     1       9500
WA  BROOME     1       9700
WA  PERTH     2      11000


SQL> ALTER TABLE sales_by_state_part 
  2  MOVE PARTITION p_wa_ TABLESPACE archive_data COMPRESS ONLINE
  3  INCLUDING ROWS WHERE sales_quarter=2;

Table altered.

SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_);

SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA  PERTH     2      11000

SQL> SELECT TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE PARTITION_NAME='P_WA_';

TABLESPACE_NAME
------------------------------
ARCHIVE_DATA