Update a partition key is costlier than insert and delete. Is it weird? Yes, but it's true.
CREATE TABLE QIHUA.part_tab
(
a NUMBER,
b NUMBER,
c NUMBER,
d NUMBER
)
PARTITION BY LIST (a)
(
PARTITION PART1 VALUES (1)
PARTITION PART2 VALUES (2)
PARTITION PART3 VALUES (3)
PARTITION PART4 VALUES (DEFAULT)
)
ENABLE ROW MOVEMENT;
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> update part_tab set a=2 where a=1;
100000 rows updated.
SQL> commit;
Commit complete.
SQL> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
SQL>
SQL> insert into part_tab select 1,b,c,d from part_tab partition (part2);
100000 rows created.
SQL> delete part_tab partition (part2);
100000 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> exec runstats_pkg.rs_stop(500)
Run1 ran in 664 hsecs
Run2 ran in 311 hsecs
run 1 ran in 213.5% of the time
Name Run1 Run2 Diff
...............................................................
STAT...undo change vector size 26,858,648 12,297,520 -14,561,128
STAT...redo size 75,653,896 28,716,804 -46,937,092 ===> big difference in redo log size. why?
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,787,625 546,896 -1,240,729 326.87%
PL/SQL procedure successfully completed.
没有评论:
发表评论