FIRST_ROWS vs. ALL_ROWS and ROWNUM predicate

最新推荐文章于 2024-03-17 16:49:04 发布

转载最新推荐文章于 2024-03-17 16:49:04 发布 · 137 阅读

本文探讨了Oracle中FIRST_ROWS与ALL_ROWS优化器模式的区别，特别是在DML操作中如何影响执行计划。通过创建示例表并执行不同类型的查询，展示了ROWNUM谓词如何改变查询优化器的行为。

一个比较著名的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/