本帖最后由 xihuisi333 于 2016-4-24 21:34 编辑
select语句执行计划有1步是根据ROWID取表的数据,逻辑读=1次根节点扫描+1次叶子节点扫描+1次表扫描=3。
update和delete语句没有TABLE ACCESS BY INDEX ROWID,逻辑读=1次根节点扫描+1次叶子节点扫描=2。
update和delete语句不需要根据rowid查出表中的数据再做更新或删除吗?还是执行计划中没体现?
create table test1 as select level id,'x'||level name from dual connect by level<=10000;
create unique index i_test1 on test1(id);
select a.blevel,a.leaf_blocks,a.num_rows,a.index_name from user_indexes a where index_name='I_TEST1';
BLEVEL LEAF_BLOCKS NUM_ROWS INDEX_NAME
------ ----------- ---------- ---------------
1 20 10000 I_TEST1
--1.
select * from test1 where id=1;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | I_TEST1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
3 consistent gets
--2.
update test1 set name='x' where id=1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | UPDATE | TEST1 | | | | |
|* 2 | INDEX UNIQUE SCAN| I_TEST1 | 1 | 10 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
2 consistent gets
--3.
delete from test1 where id=1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | DELETE | TEST1 | | | | |
|* 2 | INDEX UNIQUE SCAN| I_TEST1 | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
2 consistent gets