大数据量删除时delete的表现

jvkojvko

值得研究一下

jack198409

Good Job!

leimingliang

值得赞赏。

fuyou001

关注学习下

psop123456

高手啊，学习下

x4x

很不明白，为什么会这样呢？

liqun99133

Day Day Up

yellowlee

OO说的很清楚，不过我也没有试验。

zhaosj1726

看到这个帖子,忽然想到一个数据维护顺序的问题，总的来说,我觉得可能存在三种维护顺序：
1.先维护表数据,再维护索引数据；
2.先维护索引数据，再维护表数据；
3.前两者的结合。

对于
2、循环删除（使用索引）：
SQL> begin
  2  for v in (select name from testp) loop
  3  delete from testc where name = v.name;
  4  end loop;
  5  commit;
  6  end;
  7  /

如果是第二种方案的话,这里先使用索引找到name=:1的索引项,一项一项的删除索引条目的同时通过rowid找到表数据项,删除这些表数据项,这样当clustering_factor很大的时候,表数据维护的逻辑IO可能会很大,因为对于同一个name来说,同一个数据块可能会被多次读取(一个索引的叶块上,会按照rowid排序吗？如果会按照rowid排序的话,对于同一个name来说,同一个数据块也只会被读取一次,但我觉得应该不是按照rowid排序的.是吗?望高人给出见解),但可以使用表预取技术,先取得rowid list,排序后,再去维护表数据,这样对于同一个name来说,同一个数据块只会被读取一次。

对于
1、直接删除：
SQL> delete from testc;

如果是第一种方案的话,根据表数据项如何找到对应的索引项呢?
索引条目里记录着rowid,可以很容易的找到对应的表数据项,
但反过来,一个数据项对应的索引项该如何找到呢?
比如说删除的这行数据name='z'  rowid='xxxxxxxx';
把name='z'作为一个access predicateslai定位索引项目,然后把rowid='xxxxxxxx'作为一个filter predicates来定位最终要删除的索引条目,但感觉这样做效率也太低了吧?!


当然,上面说得有点儿乱,不知道大家怎么看这个问题.

zhaosj1726

delete数据行的时候,是如何定位对应的索引条目的呢?
create table zsj_obj2 as select * from dba_objects;
insert into zsj_obj2 select * from zsj_obj2; 多次执行
commit;
select count(1) from zsj_obj2;                                                                                
  COUNT(1)
----------
    808544

update zsj_obj2 set object_id=rownum,object_name='ZSJ_TEST';
COMMIT;
实际上只有一个object_name
CREATE INDEX IND_ZSJ_OBJ2_ID ON ZSJ_OBJ2(OBJECT_ID);
CREATE INDEX IND_ZSJ_OBJ2_NAME ON ZSJ_OBJ2(OBJECT_NAME);
exec dbms_stats.gather_table_stats(user,'ZSJ_OBJ2',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1');
select BLEVEL,LEAF_BLOCKS from user_indexes where index_name='IND_ZSJ_OBJ2_NAME';                             
    BLEVEL LEAF_BLOCKS
---------- -----------
         2        2253
如果像我猜想的那样,删除一个数据行的时候,根据这个数据行的索引列值来access索引并根据rowid来filter并最终确定要删除的索引条目的话,那么定位叶块链表上第一个叶块,最后一个叶块和中间的叶块上的索引条目的逻辑IO一定是不同的(因为只有一个object_name,根据rowid来filter索引条目的话,第一个叶块,最后一个叶块,中间叶块的逻辑IO一定是不同的),可下面的实验结果却是完全相同的.

variable v_object_id number;
exec :v_object_id:=1;
set autotrace on;
delete from zsj_obj2 where object_id=:v_object_id;
1 row deleted.
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  DELETE           | ZSJ_OBJ2        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_ZSJ_OBJ2_ID |     1 |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:V_OBJECT_ID))

Statistics
----------------------------------------------------------
          1  recursive calls
          9  db block gets
          6  consistent gets
          1  rows processed

exec :v_object_id:=808540;
delete from zsj_obj2 where object_id=:v_object_id;
                                                        
1 row deleted.
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  DELETE           | ZSJ_OBJ2        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_ZSJ_OBJ2_ID |     1 |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:V_OBJECT_ID))

Statistics
----------------------------------------------------------
          0  recursive calls
          9  db block gets
          6  consistent gets
          1  rows processed

exec :v_object_id:=400000;
delete from zsj_obj2 where object_id=:v_object_id;
1 row deleted.
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  DELETE           | ZSJ_OBJ2        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_ZSJ_OBJ2_ID |     1 |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:V_OBJECT_ID))

Statistics
----------------------------------------------------------
          0  recursive calls
          9  db block gets
          6  consistent gets
          1  rows processed

exec :v_object_id:=100000;                                                                                    
delete from zsj_obj2 where object_id=:v_object_id;                                                            
1 row deleted.
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  DELETE           | ZSJ_OBJ2        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_ZSJ_OBJ2_ID |     1 |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:V_OBJECT_ID))

Statistics
----------------------------------------------------------
          0  recursive calls
          9  db block gets
          6  consistent gets
          1  rows processed
  
通过dump索引结构,可以知道在IND_ZSJ_OBJ2_NAME索引上,1确实是叶块链表中第一个叶块上的条目,808540确实是叶块链表中最后一个叶块上的条目.
其实,这也是我早就预料到的结果,oracle怎么可能那样去定位一个已经存在的数据行对应的索引条目的位置呢?那么oracle又是如何定位的呢?是在叶块上添加还是在数据块上添加了什么结构变量而后能够迅速定位了呢?