先瞧一个大家习以为常的现象:
hr@ORCL> drop table t purge;
Table dropped.
hr@ORCL> create table t (x number,y varchar2(30));
Table created.
hr@ORCL> insert into t select rownum,rownum||'a' from dual connect by rownum<1000000;
999999 rows created.
hr@ORCL> create index idx_t on t(y);
Index created.
hr@ORCL> commit;
Commit complete.
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
PL/SQL procedure successfully completed.
hr@ORCL> Set autotrace traceonly
/* 走的是 INDEX RANGE SCAN、Cost 为 6、3个逻辑读 */
hr@ORCL> select count(*) from t where y='999999a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"='999999a')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hr@ORCL> delete t;
999999 rows deleted.
hr@ORCL> commit;
Commit complete.
/* 表清空后、走的依然是INDEX RANGE SCAN、Cost 为 6、3个逻辑读 */
hr@ORCL> select count(*) from t where y='999999a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1500240790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"='999999a')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
意料之内、delete后查询逻辑读仍然相同、这表明、INDEX RANGE SCAN扫描的叶子块数目没有变
也就是说、整表delete后、索引根块+分支块的内容也没变、还是能够通过分支块的内容定位到第一个叶子块
那么、记录被清空、索引的entry也被清空、Oracle如何判断出读到哪个叶子块停下来?
答案其实也很简单、索引的entry条目的删除、并非物理被干掉、只是打了个标记D、因此、Oracle还是能够知道读到哪停止
所以、经过上面的看和想、我想我们平时在操作DB时、应该要规范点、正经点、认真点、严肃点
以下针对这个现象的两个规范操作:
① 整表删除用 truncate
② 大量数据 delete 、重建索引
By David Lin
2013-06-06
Good Luck