[20131121]奇怪的执行计划变化.txt
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t pctfree 99 as select rownum id,lpad('x',1000,'x') name from dual connect by level
create index i_t_id on t(id) ;
exec dbms_stats.gather_table_stats(user, 'T', no_invalidate => false);
SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> select avg(id) from t;
AVG(ID)
----------
500.5
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1kadrxzj9ahk7, child number 0
-------------------------------------
select avg(id) from t
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------
14 rows selected.
--可以发现执行计划选择了INDEX FAST FULL SCAN.逻辑读=6.
但是如果语句修改为select avg(id)+1 from t; 加入了运算,结果如何呢?
SCOTT@test> select avg(id)+1 from t;
AVG(ID)+1
----------
501.5
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4d54cp5vqmzt0, child number 0
-------------------------------------
select avg(id)+1 from t
Plan hash value: 2966233522
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 279 (100)| 1 |00:00:00.01 | 1004 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 1004 |
| 2 | TABLE ACCESS FULL| T | 1 | 1000 | 279 (0)| 1000 |00:00:00.01 | 1004 |
--------------------------------------------------------------------------------------------------
14 rows selected.
--仅仅改为avg(id)+1,执行计划就发生了变化,变为TABLE ACCESS FULL.逻辑读=1004.
--加入条件id is not null 才可以解决这个问题.
SCOTT@test> select avg(id)+1 from t where id is not null;
AVG(ID)+1
----------
501.5
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7bpga35zfgxb0, child number 0
-------------------------------------
select avg(id)+1 from t where id is not null
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
|* 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID" IS NOT NULL)
19 rows selected.
--修改id为not null.
SCOTT@test> alter table scott.t modify(id not null);
Table altered.
SCOTT@test> select avg(id)+1 from t ;
AVG(ID)+1
----------
501.5
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4997mr261xaua, child number 0
-------------------------------------
select avg(id)+1 from t
Plan hash value: 3548397654
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
| 2 | INDEX FAST FULL SCAN| I_T_ID | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------
参考:http://alexanderanokhin.wordpress.com/2013/11/16/filter-is-not-null/
给出了更加有意思的测试.