[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<=1e3;
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/
给出了更加有意思的测试.