首页 » ORACLE » oracle fast split partition

oracle fast split partition

当拆分一个(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.

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. porndodo | #1
    2020-02-16 at 22:34

    不让出门,只能刷刷博客了!