Oracle 12c分区新功能
发布于：2021-02-05 10:00:20
在线移动分区
在oracle12c中，当分区表上的DML事务正在进行时，我们可以在线移动和压缩分区。
在早期版本中，如果在分区表上的DML语句进行时尝试移动分区，则会出现如下所示的错误。
ORA-00054:资源正忙，获取时指定了NOWAIT或超时。
这与12c中与信息生命周期管理相关的新功能相关联，其中表（和分区）可以作为ILM策略的一部分移动到低成本存储和/或压缩。因此，我们不希望在移动或压缩分区时影响任何正在进行的DML语句—因此是联机特性。
12c的另一个特性是，这种联机分区移动不会使相关的分区索引处于不可用状态。updateindexes ONLINE子句将维护表上的全局和局部索引。
SQL> ALTER TABLE sales MOVE PARTITION sales_q2_1998 TABLESPACE users
间隔参考分区
在oracle11g中，介绍了区间划分和引用划分的方法。在12c中，我们进一步将这两种分区方法结合到一起。因此，我们现在可以根据定义了间隔分区的父表对要引用的子表进行分区。
所以要记住两件事。
每当在父表中创建间隔分区时，也会在引用的子表中创建一个分区，并从父表继承分区名称。
子表中与父表中的分区相对应的分区是在将行插入子表时创建的。
让我们看一个使用classic ORDERS和ORDER_ITEMS表的示例，这两个表具有父子关系，并且父ORDERS表已被间隔分区。
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)
;
注意父表中的分区。
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
我们可以看到子表从父表继承了相同的分区。
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDER_ITEMS_PART';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_2006
P_2007
P_2008
我们现在向表中插入一个新行，这将导致自动创建一个新分区。
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个分区，只有在将行插入子表时，才会创建与父表对应的新分区。
我们现在向子表中插入一些行—请注意，行插入会导致在与父表相对应的子表中创建一个新分区。
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
截断级联
在oracle12c中，我们可以向TRUNCATE TABLE或ALTER TABLE TRUNCATE PARTITION命令添加CASCADE选项。
CASCADE选项将截断引用父表的所有子表，以及使用ON DELETE CASCADE选项创建引用约束的子表。
当在引用分区模型的分区级别使用TRUNCATE CASCADE时，它还将级联到子表中的分区，如下面的示例所示。
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
多分区维护操作
在oracle12c中，我们可以在一个操作中添加、截断或删除多个分区。
在12c之前的版本中，拆分和合并分区操作一次只能在两个分区上执行。如果一个表有10个分区，表示需要合并，则必须发出9个单独的DDL语句。
现在，只需一个命令，我们就可以将数据推出到较小的分区中，或者将数据推出到较大的分区中。
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中，我们现在可以看到这样一种情况：只有表的某些分区被索引，而其他分区没有任何索引。例如，我们可能希望受许多OLTP类型操作影响的最新分区没有任何索引，以便加快插入活动，而表的较旧分区受DSS类型查询的影响，并从索引中受益。
我们可以在表级别打开或关闭索引，然后在分区级别有选择地启用或禁用索引。
请看下面的例子。
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
)
;
在表上创建一个本地分区索引，并注意本地索引的大小。
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
我们删除索引并创建相同的索引，但这次是作为部分索引。由于索引只在表的几个分区上创建，而不是在整个表上创建，因此它的大小是原始索引的一半。
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
我们可以看到，对于未启用索引的分区，索引被创建为不可用。
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计划的不同之处—它们访问同一表的不同分区，在一种情况下使用本地部分索引，在另一种情况下执行全表扫描。
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)|
--------------------------------------------------------------------------------------------------------------------------------
注意数据字典视图中的新列索引和定义索引。
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
异步全局索引维护
在早期版本中，甚至在单个分区上执行截断或删除分区等操作都会导致全局索引不可用，并且需要在应用程序使用索引之前重建索引。
现在，当我们发出相同的DROP或TRUNCATE partition命令时，我们可以使用updateindexes子句，这样可以保持全局索引处于可用状态。
现在推迟了全局索引维护，该维护由名为SYS.PMO_DEFERRED_GIDX_MAINT_JOB的DBMS_SCHEDULER作业执行，该作业计划在每天的2.00 AM运行。
我们还可以使用具有CLEANUP_GIDX过程的DBMS_PART包，该过程可以清理全局索引。
DBA | USER | ALL_INDEXES视图中的新列ORPHANED_ENTRIES会跟踪全局索引，并指定全局索引分区是否包含由DROP / TRUNCATE PARTITION操作引起的陈旧条目。
让我们看一个同样的例子。请注意，重要的一点是，即使在对分区表执行截断操作之后，全局索引仍处于可用状态。
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
作者介绍
