[20131121]奇怪的執行計劃變化.txt

lfree發表於2013-11-21
[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 |
-------------------------------------------------------------------------------------------------------

參考:
給出了更加有意思的測試.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-777191/，如需轉載，請註明出處，否則將追究法律責任。

相關文章