2008年7月3日星期四

update partition key is slower than insert and delete (1)

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.

 

 

 


 





没有评论: