ANBOB™

当拆分一个(partition)分区为两个分区时，其中一个分区为空，另一个非空分区保持了与原来分区相同的存储属性时，因为未产生数据移动，只通过内部切换data_object_id的内部调用，同时保证原来的Global 和 partition 索引一直处于USABLE(可用)状态，该特性叫做fast split partition. 这是9.2开始的老特性，IOT类型是从10.2 开始支持。

除了拆分后其中一个分区为空，还有要求统计信息准确，不只是拆分的分区上，同要也要求相应的索引重要是分区键相关，因为在拆分时会执行2个递归查询，分别以<分区值和>=分区值，确保拆分后的分区有一个分区为空。所以分区键上最好有索引，否则这个查询也可能会耗很长时间， 不要以为有rownum<2就会很快，如以前这个案例。
如：

ALTER TABLE largetab SPLIT PARTITION p5000000 AT (900000)
INTO
(
PARTITION p900000,
PARTITION p5000000
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 1 36 79 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.03 1 36 79 0

select /*+ FIRST_ROWS(1) PARALLEL("LARGETAB", 1) */ 1
from
NO_CROSS_CONTAINER("ANBOB"."LARGETAB") PARTITION ("P5000000") where ( ( (
( "ID" < 900000 ) ) ) ) and rownum < 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 10 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 13 0 0

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=31 us starts=1)
0 0 0 PARTITION RANGE SINGLE PARTITION: 3 3 (cr=3 pr=0 pw=0 time=26 us starts=1 cost=0 size=13 card=1)
0 0 0 INDEX RANGE SCAN IDX_LARGETAB PARTITION: 3 3 (cr=3 pr=0 pw=0 time=21 us starts=1 cost=0 size=13 card=1)(object id 74334)

select /*+ FIRST_ROWS(1) PARALLEL("LARGETAB", 1) */ 1
from
NO_CROSS_CONTAINER("ANBOB"."LARGETAB") PARTITION ("P5000000") where ( ( (
( "ID" >= 900000 ) ) ) ) and rownum < 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 10 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 13 0 1

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 COUNT STOPKEY (cr=3 pr=0 pw=0 time=183 us starts=1)
1 1 1 PARTITION RANGE SINGLE PARTITION: 3 3 (cr=3 pr=0 pw=0 time=179 us starts=1 cost=1 size=10393357 card=799489)
1 1 1 INDEX RANGE SCAN IDX_LARGETAB PARTITION: 3 3 (cr=3 pr=0 pw=0 time=172 us starts=1 cost=1 size=10393357 card=799489)(object id 74334)

诊断fast split partition的event

ALTER SESSION SET tracefile_identifier='MYTRACE';
ALTER SESSION SET MAX_DUMP_FILE_SIZE = unlimited;
ALTER SESSION SET events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
alter session set events '14525 trace name context forever, level 2';
run the split operation here

Note that starting from 12.1 there is no segment created for a resulting partition that is empty.

从Oracle 12.2 开始split partition 可以支持online选项，不会再导致索引失效和影响DML操作。

SQL> alter table order_tab split partition CREATED_MX into
(partition CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2016', 'DD/MM/YYYY')),PARTITION CREATED_MX) ONLINE; 
Table altered.

打赏

微信扫一扫，打赏作者吧～