一个比较著名的FIRST_ROWS vs. ALL_ROWS的问题:
1)select 操作执行了索引查询
2)但是当select作为dml操作的一部分的时候(例如:insert .. select ),cbo忽略了这个索引
oracle对于dml操作,内总总会倾向与all_rows模式,这就意味着即使你系统级别设置的优化器模式
为first_rows,你的dml操作仍然使用的all_rows模式
但是是不是意味着在all_rows模式下,select操作 就和dml操作没有区别了呢?
create table t1 (
c1 varchar2(10),
c2 number,
c3 number,
constraint t1_pk primary key (c2, c1)
);
insert into t1
select
dbms_random.string('x', 10),
case when level <= 5000 then 1 else 0 end,
level
from dual
connect by level <= 10000
;
exec dbms_stats.gather_table_stats(user, 't1');
explain plan for
select *
from t1
where c2 = 0 and rownum = 1
;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_PK | | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
explain plan for
update t1 set c2 = 1
where c2 = 0 and rownum = 1
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 14 | 8 (0)| 00:00:01 |
| 1 | UPDATE | T1 | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| T1 | 5000 | 70000 | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------
我们看到即使在all_rows模式下,select 和dml操作也产生了不同的执行计划。
这是因为rownum谓词会导致frist_rows模式,这个特性由一个隐含参数控制_optimizer_rownum_pred_based_fkr
NAME VALUE DESCRIB
---------------------------------------- -------------------- ------------------------------------------------------------
_optimizer_rownum_pred_based_fkr TRUE enable the use of first K rows due to rownum predicate
这意味着在all_rows模式下,当使用rownum谓词的时候,oracle也尽量使用first_rows模式
explain plan for
select /*+ opt_param('_optimizer_rownum_pred_based_fkr', 'false') */ *
from t1
where c2 = 0 and rownum = 1
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 8 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 5000 | 85000 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
explain plan for
update /*+ index(t1) */ t1 set c2 = 1
where c2 = 0 and rownum = 1
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 14 | 21 (0)| 00:00:01 |
| 1 | UPDATE | T1 | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | INDEX RANGE SCAN| T1_PK | 5000 | 70000 | 21 (0)| 00:00:01 |
----------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-703201/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-703201/