Oracle 12c分區(qū)新功能
發(fā)布于：2021-02-05 10:00:20
在線移動分區(qū)
在oracle12c中，當分區(qū)表上的DML事務正在進行時，我們可以在線移動和壓縮分區(qū)。
在早期版本中，如果在分區(qū)表上的DML語句進行時嘗試移動分區(qū)，則會出現(xiàn)如下所示的錯誤。
ORA-00054:資源正忙，獲取時指定了NOWAIT或超時。
這與12c中與信息生命周期管理相關(guān)的新功能相關(guān)聯(lián)，其中表（和分區(qū)）可以作為ILM策略的一部分移動到低成本存儲和/或壓縮。因此，我們不希望在移動或壓縮分區(qū)時影響任何正在進行的DML語句—因此是聯(lián)機特性。
12c的另一個特性是，這種聯(lián)機分區(qū)移動不會使相關(guān)的分區(qū)索引處于不可用狀態(tài)。updateindexes ONLINE子句將維護表上的全局和局部索引。
SQL> ALTER TABLE sales MOVE PARTITION sales_q2_1998 TABLESPACE users
間隔參考分區(qū)
在oracle11g中，介紹了區(qū)間劃分和引用劃分的方法。在12c中，我們進一步將這兩種分區(qū)方法結(jié)合到一起。因此，我們現(xiàn)在可以根據(jù)定義了間隔分區(qū)的父表對要引用的子表進行分區(qū)。
所以要記住兩件事。
每當在父表中創(chuàng)建間隔分區(qū)時，也會在引用的子表中創(chuàng)建一個分區(qū)，并從父表繼承分區(qū)名稱。
子表中與父表中的分區(qū)相對應的分區(qū)是在將行插入子表時創(chuàng)建的。
讓我們看一個使用classic ORDERS和ORDER_ITEMS表的示例，這兩個表具有父子關(guān)系，并且父ORDERS表已被間隔分區(qū)。
CREATE TABLE "OE"."ORDERS_PART"
(
"ORDER_ID" NUMBER(12,0) NOT NULL,
"ORDER_DATE" TIMESTAMP (6) CONSTRAINT "ORDER_PART_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(6,0) ,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
CONSTRAINT ORDERS_PART_pk PRIMARY KEY (ORDER_ID)
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION P_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00 +00:00'),
PARTITION P_2007 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00 +00:00'),
PARTITION P_2008 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00 +00:00')
)
;
CREATE TABLE "OE"."ORDER_ITEMS_PART"
(
"ORDER_ID" NUMBER(12,0) NOT NULL,
"LINE_ITEM_ID" NUMBER(3,0) NOT NULL ENABLE,
"PRODUCT_ID" NUMBER(6,0) NOT NULL ENABLE,
"UNIT_PRICE" NUMBER(8,2),
"QUANTITY" NUMBER(8,0),
CONSTRAINT "ORDER_ITEMS_PART_FK" FOREIGN KEY ("ORDER_ID")
REFERENCES "OE"."ORDERS_PART" ("ORDER_ID") ON DELETE CASCADE )
PARTITION BY REFERENCE (ORDER_ITEMS_PART_FK)
;
注意父表中的分區(qū)。
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
我們可以看到子表從父表繼承了相同的分區(qū)。
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDER_ITEMS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
我們現(xiàn)在向表中插入一個新行，這將導致自動創(chuàng)建一個新分區(qū)。
SQL> INSERT INTO ORDERS_PART
2 VALUES
3 (9999,'17-MAR-15 01.00.00.000000 PM', 'DIRECT',147,5,1000,163,NULL);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
SYS_P301
注意，此時子表仍然只有3個分區(qū)，只有在將行插入子表時，才會創(chuàng)建與父表對應的新分區(qū)。
我們現(xiàn)在向子表中插入一些行—請注意，行插入會導致在與父表相對應的子表中創(chuàng)建一個新分區(qū)。
SQL> INSERT INTO ORDER_ITEMS_PART
2 VALUES
3 (9999,1,2289,10,100);
1 row created.
SQL> INSERT INTO ORDER_ITEMS_PART
2 VALUES
3 (9999,2,2268,500,1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDER_ITEMS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
SYS_P301
截斷級聯(lián)
在oracle12c中，我們可以向TRUNCATE TABLE或ALTER TABLE TRUNCATE PARTITION命令添加CASCADE選項。
CASCADE選項將截斷引用父表的所有子表，以及使用ON DELETE CASCADE選項創(chuàng)建引用約束的子表。
當在引用分區(qū)模型的分區(qū)級別使用TRUNCATE CASCADE時，它還將級聯(lián)到子表中的分區(qū)，如下面的示例所示。
SQL> alter table orders_part truncate partition SYS_P301 cascade;
Table truncated.
SQL> select count(*) from orders_part partition (SYS_P301);
COUNT(*)
----------
0
SQL> select count(*) from order_items_part partition (SYS_P301);
COUNT(*)
----------
0
多分區(qū)維護操作
在oracle12c中，我們可以在一個操作中添加、截斷或刪除多個分區(qū)。
在12c之前的版本中，拆分和合并分區(qū)操作一次只能在兩個分區(qū)上執(zhí)行。如果一個表有10個分區(qū)，表示需要合并，則必須發(fā)出9個單獨的DDL語句。
現(xiàn)在，只需一個命令，我們就可以將數(shù)據(jù)推出到較小的分區(qū)中，或者將數(shù)據(jù)推出到較大的分區(qū)中。
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2014 VALUES LESS THAN (TO_DATE('01-APR-2014','dd-MON-yyyy'))
, PARTITION sales_q2_2014 VALUES LESS THAN (TO_DATE('01-JUL-2014','dd-MON-yyyy'))
, PARTITION sales_q3_2014 VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy'))
, PARTITION sales_q4_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy'))
);
ALTER TABLE sales ADD
PARTITION sales_q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2015','dd-MON-yyyy')),
PARTITION sales_q2_2015 VALUES LESS THAN (TO_DATE('01-JUL-2015','dd-MON-yyyy')),
PARTITION sales_q3_2015 VALUES LESS THAN (TO_DATE('01-OCT-2015','dd-MON-yyyy')),
PARTITION sales_q4_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'));
SQL> ALTER TABLE sales MERGE PARTITIONS sales_q1_2015,sales_q2_2015,sales_q3_2015,sales_q4_2015 INTO PARTITION sales_2015;
Table altered.
SQL> ALTER TABLE sales SPLIT PARTITION sales_2015 INTO
2 (PARTITION sales_q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2015','dd-MON-yyyy')),
3 PARTITION sales_q2_2015 VALUES LESS THAN (TO_DATE('01-JUL-2015','dd-MON-yyyy')),
4 PARTITION sales_q3_2015 VALUES LESS THAN (TO_DATE('01-OCT-2015','dd-MON-yyyy')),
5 PARTITION sales_q4_2015);
Table altered.
部分索引
在oracle12c中，我們現(xiàn)在可以看到這樣一種情況：只有表的某些分區(qū)被索引，而其他分區(qū)沒有任何索引。例如，我們可能希望受許多OLTP類型操作影響的最新分區(qū)沒有任何索引，以便加快插入活動，而表的較舊分區(qū)受DSS類型查詢的影響，并從索引中受益。
我們可以在表級別打開或關(guān)閉索引，然后在分區(qū)級別有選擇地啟用或禁用索引。
請看下面的例子。
CREATE TABLE "SH"."SALES_12C"
(
"PROD_ID" NUMBER NOT NULL ENABLE,
"CUST_ID" NUMBER NOT NULL ENABLE,
"TIME_ID" DATE NOT NULL ENABLE,
"CHANNEL_ID" NUMBER NOT NULL ENABLE,
"PROMO_ID" NUMBER NOT NULL ENABLE,
"QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
"AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE
)
TABLESPACE "EXAMPLE"
INDEXING OFF
PARTITION BY RANGE ("TIME_ID")
(PARTITION "SALES_1995" VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) ,
PARTITION "SALES_1996" VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) ,
PARTITION "SALES_1997" VALUES LESS THAN (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) ,
PARTITION "SALES_1998" VALUES LESS THAN (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) ,
PARTITION "SALES_1999" VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) ,
PARTITION "SALES_2000" VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INDEXING ON,
PARTITION "SALES_2001" VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INDEXING ON,
PARTITION "SALES_2002" VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INDEXING ON
)
;
在表上創(chuàng)建一個本地分區(qū)索引，并注意本地索引的大小。
SQL> CREATE INDEX SALES_12C_IND ON SALES_12C (TIME_ID) LOCAL;
Index created.
SQL> SELECT SUM(BYTES)/1048576 FROM USER_SEGMENTS WHERE SEGMENT_NAME='SALES_12C_IND';
SUM(BYTES)/1048576
------------------
32
我們刪除索引并創(chuàng)建相同的索引，但這次是作為部分索引。由于索引只在表的幾個分區(qū)上創(chuàng)建，而不是在整個表上創(chuàng)建，因此它的大小是原始索引的一半。
SQL> CREATE INDEX SALES_12C_IND ON SALES_12C (TIME_ID) LOCAL INDEXING PARTIAL;
Index created.
SQL> SELECT SUM(BYTES)/1048576 FROM USER_SEGMENTS WHERE SEGMENT_NAME='SALES_12C_IND';
SUM(BYTES)/1048576
------------------
16
我們可以看到，對于未啟用索引的分區(qū)，索引被創(chuàng)建為不可用。
SQL> SELECT PARTITION_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='SALES_12C_IND';
PARTITION_NAME STATUS
------------------------------ --------
SALES_2002 USABLE
SALES_2001 USABLE
SALES_2000 USABLE
SALES_1999 UNUSABLE
SALES_1998 UNUSABLE
SALES_1997 UNUSABLE
SALES_1996 UNUSABLE
SALES_1995 UNUSABLE
請注意兩個查詢之間EXPLAIN計劃的不同之處—它們訪問同一表的不同分區(qū)，在一種情況下使用本地部分索引，在另一種情況下執(zhí)行全表掃描。
SQL> EXPLAIN PLAN FOR
2 SELECT SUM(QUantity_sold) from sales_12c
3 where time_id select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2557626605
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1925 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 472 | 5192 | 1925 (1)| 00:00:01 | 1 | KEY |
|* 3 | TABLE ACCESS FULL | SALES_12C | 472 | 5192 | 1925 (1)| 00:00:01 | 1 | KEY |
SQL> EXPLAIN PLAN FOR
2 SELECT SUM(QUantity_sold) from sales_12c
3 where time_id='01-JAN-97';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2794067059
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 22 | | | | |
| 2 | VIEW | VW_TE_2 | 2 | 26 | 2 (0)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 22 | 1 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_12C | 1 | 22 | 1 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 7 | INDEX RANGE SCAN | SALES_12C_IND | 1 | | 1 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 8 | FILTER | | | | | | | |
| 9 | PARTITION RANGE SINGLE | | 1 | 22 | 2 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 10 | TABLE ACCESS FULL | SALES_12C | 1 | 22 | 2 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
--------------------------------------------------------------------------------------------------------------------------------
注意數(shù)據(jù)字典視圖中的新列索引和定義索引。
SQL> select def_indexing from user_part_tables where table_name='SALES_12C';
DEF
---
OFF
SQL> select indexing from user_indexes where index_name='SALES_12C_IND';
INDEXIN
-------
PARTIAL
異步全局索引維護
在早期版本中，甚至在單個分區(qū)上執(zhí)行截斷或刪除分區(qū)等操作都會導致全局索引不可用，并且需要在應用程序使用索引之前重建索引。
現(xiàn)在，當我們發(fā)出相同的DROP或TRUNCATE partition命令時，我們可以使用updateindexes子句，這樣可以保持全局索引處于可用狀態(tài)。
現(xiàn)在推遲了全局索引維護，該維護由名為SYS.PMO_DEFERRED_GIDX_MAINT_JOB的DBMS_SCHEDULER作業(yè)執(zhí)行，該作業(yè)計劃在每天的2.00 AM運行。
我們還可以使用具有CLEANUP_GIDX過程的DBMS_PART包，該過程可以清理全局索引。
DBA | USER | ALL_INDEXES視圖中的新列ORPHANED_ENTRIES會跟蹤全局索引，并指定全局索引分區(qū)是否包含由DROP / TRUNCATE PARTITION操作引起的陳舊條目。
讓我們看一個同樣的例子。請注意，重要的一點是，即使在對分區(qū)表執(zhí)行截斷操作之后，全局索引仍處于可用狀態(tài)。
SQL> alter table sales_12c truncate partition SALES_2000 UPDATE INDEXES;
Table truncated.
SQL> select distinct status from user_ind_partitions;
STATUS
--------
USABLE
SQL> select partition_name, ORPHANED_ENTRIES from user_ind_partitions
2 where index_name='SALES_GIDX';
PARTITION_NAME ORP
------------------------------ ---
SYS_P348 YES
SYS_P347 YES
SYS_P346 YES
SYS_P345 YES
SYS_P344 YES
SYS_P343 YES
SYS_P342 YES
SYS_P341 YES
SQL> exec dbms_part.cleanup_gidx('SH','SALES_12C');
PL/SQL procedure successfully completed.
SQL> select partition_name, ORPHANED_ENTRIES from user_ind_partitions
2 where index_name='SALES_GIDX';
PARTITION_NAME ORP
------------------------------ ---
SYS_P341 NO
SYS_P342 NO
SYS_P343 NO
SYS_P344 NO
SYS_P345 NO
SYS_P346 NO
SYS_P347 NO
SYS_P348 NO
