ANBOB™

一般向一个表插入数据时，为了提速用到append 提示，但有没有想过表上的索引是怎么维护的？

有时会发现如果果禁用了表上索引，再insert /* +append*/ 1000万数据有可能用几分钟，但如果索引在有效状态可能会增加到1个小时，所有处理步骤可能会这么安排

1. execute immediate ‘disable A indexes’
2. insert /*+ Append */ into A select from B
3. execute immediate ‘rebuild A indexes’

但是如果你向表中插入的数据是原大表中数据的1%数据量，那么第三步所有的时间就会大于索引有效状态上的insert;如果你新插入的数据可能比原表还要大那么上面的做法也许是正确的选择

如果index是有效状态，insert append又是怎么做的呢？tom提示如下 10g r2

a) we write table data above the high water mark (table is LOCKED, no other transaction will modify it)
b) we write indexing data for the newly inserted rows into mini-index structures in temp
c) at the end of the insert – we take these mini indexes and merge them in bulk into the existing indexes. We are done

而禁用或删除index，insert append的是
a) we write table data above the high water mark (table is LOCKED, no other transaction will modify it)
b) at the end – we will full scan the table – the entire table – the whole table – N times where N = number of indexes – to recreate them. If you just made a large table a little larger – the time to full scan over and over defeats your goal.

如查表中的数据经常性的加载数据替换原有数据
probably truncate + disable + parallel load + parallel rebuild index
如果还保留原索引再追加合并效率往往更低

打赏

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