[20180316]為什麼不使用INDEX FULL SCAN (MIN/MAX).txt

--//連結:http://www.itpub.net/thread-2100456-1-1.html.自己重複測試看看.

1.環境:

SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as select * from dba_objects ;
Table created.

SCOTT@book> create index i_t_object_id on t(object_id);
Index created.

--//分析表略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '.

2.測試:
SCOTT@book> select /*+ index(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f2u3nkrcsdzbb, child number 0
-------------------------------------
select /*+ index(t,i_t_object_id) */
nvl2(max(object_id),max(object_id),3000000)+1 n10 from t
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |   347 (100)|          |
|   1 | SORT AGGREGATE    |      |      1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 86989 |   424K|   347   (1)| 00:00:05 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1

--//連索引都不用.設定約束object_id is null 看看.

SCOTT@book> delete from t where object_id is null ;
2 rows deleted.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter table t modify(object_id not null);
Table altered.

SCOTT@book> select /*+ index(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f2u3nkrcsdzbb, child number 0
-------------------------------------
select /*+ index(t,i_t_object_id) */
nvl2(max(object_id),max(object_id),3000000)+1 n10 from t
Plan hash value: 4145094723
----------------------------------------------------------------------------------------------
| Id | Operation                   | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |       |   195 (100)|          |
|   1 | SORT AGGREGATE             |               |      1 |     5 |            |          |
|   2 |   FIRST ROW                 |               | 86989 |   424K|   195   (1)| 00:00:03 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 86989 |   424K|   195   (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T@SEL$1
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--//可以使用索引,並且走INDEX FULL SCAN (MIN/MAX).不加提示看看:

SCOTT@book> select /*+ inde111x(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1d6mkncu6tjms, child number 0
-------------------------------------
select /*+ inde111x(t,i_t_object_id) */
nvl2(max(object_id),max(object_id),3000000)+1 n10 from t
Plan hash value: 3095383276
----------------------------------------------------------------------------------------
| Id | Operation             | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |        |       |    54 (100)|          |
|   1 | SORT AGGREGATE       |               |      1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 86989 |   424K|    54   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--//可以發現不加提示,雖然使用索引,但是執行計劃走的是INDEX FAST FULL SCAN.
--//注意看一個細節:cost=54.而前面加提示:cost=195,為什麼會這樣,導致執行計劃認為選擇INDEX FAST FULL SCAN更優.

SCOTT@book> alter session set statistics_level=all;

Session altered.

SCOTT@book> select max(object_id) n10 from t;
N10
---------------------
90460

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID df1726cj0y4vz, child number 0
-------------------------------------
select max(object_id) n10 from t
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 | SORT AGGREGATE            |               |      1 |      1 |     5 |            |          |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1

--//如果取最大值cost=2.問題集中在select /*+ index(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;的cost如何計算.

--//oraclenvl2,nvl函式有一個特點,要先運算第2,3個引數值.如下例子可以證明:

SYS@book> grant execute on sys.dbms_lock to scott;
Grant succeeded.

--//以scott使用者執行:
CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
sys.dbms_lock.sleep(seconds);
RETURN seconds;
END;
/

SCOTT@book> set timing on
SCOTT@book> select nvl2(1,sleep(1),sleep(2)) from dual ;
NVL2(1,SLEEP(1),SLEEP(2))
-------------------------
1
Elapsed: 00:00:03.00
--//執行需要時間3秒.也就證明先運算sleep(1),sleep(2),在算第1個引數,oracle不會選擇短路執行路徑.

SCOTT@book> select /*+ index(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f2u3nkrcsdzbb, child number 0
-------------------------------------
select /*+ index(t,i_t_object_id) */
nvl2(max(object_id),max(object_id),3000000)+1 n10 from t
Plan hash value: 4145094723
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |   195 (100)|          |      1 |00:00:00.01 |       2 |
|   1 | SORT AGGREGATE             |               |      1 |      1 |     5 |            |          |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                 |               |      1 | 86989 |   424K|   195   (1)| 00:00:03 |      1 |00:00:00.01 |       2 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID |      1 | 86989 |   424K|   195   (1)| 00:00:03 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T@SEL$1

--//注意看E-Rows=86989.而前面select max(object_id) n10 from t;的E-Rows=1.可以認為當執行nvl2(max(object_id),max(object_id),3000000)+1時,
--//第2個引數max(object_id),oracle認為這個是變數,運算86989次.導致成本上升為195,透過10053定位看看.
--//而如果使用nvl函式nvl(max(object_id),3000000)+1,第2引數是常量,不需要這種的運算.

3.10053分析:

SCOTT@book> @ &r/10053on 12
Session altered.

SCOTT@book> Select /*+ index(t,i_t_object_id) */ nvl2(max(object_id),max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

--//注意要改動sql語句,進行1次硬分析10053才跟蹤到.

SCOTT@book> @ &r/10053off
Session altered.

--//檢查轉儲:
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
    #Rows: 86989 #Blks: 1270 AvgRowLen: 98.00 ChainCnt: 0.00
Index Stats::
Index: I_T_OBJECT_ID Col#: 4
    LVLS: 1 #LB: 193 #DK: 86987 LB/K: 1.00 DB/K: 1.00 CLUF: 1368.00
    User hint to use this index
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
    Card: Original: 86989.000000 Rounded: 86989 Computed: 86989.00 Non Adjusted: 86989.00
Access Path: index (FullScan)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Index: I_T_OBJECT_ID
    resc_io: 194.00 resc_cpu: 18778959
    ix_sel: 1.000000 ix_sel_with_filters: 1.000000
    Cost: 194.51 Resp: 194.51 Degree: 1
Best:: AccessPath: IndexRange
Index: I_T_OBJECT_ID
         Cost: 194.51 Degree: 1 Resp: 194.51 Card: 86989.00 Bytes: 0
***************************************
--//注意看下劃線部分 Access Path: index (FullScan).
--// Best:: AccessPath: IndexRange

--//對比如下的10053轉儲
SCOTT@book> @ &r/10053on 12
Session altered.

SCOTT@book> Select max(object_id) n10 from t;
N10
---------------------
90460

SCOTT@book> @ &r/10053off
Session altered.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
    #Rows: 86989 #Blks: 1270 AvgRowLen: 98.00 ChainCnt: 0.00
Index Stats::
Index: I_T_OBJECT_ID Col#: 4
    LVLS: 1 #LB: 193 #DK: 86987 LB/K: 1.00 DB/K: 1.00 CLUF: 1368.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
    Card: Original: 86989.000000 Rounded: 86989 Computed: 86989.00 Non Adjusted: 86989.00
Access Path: TableScan
    Cost: 346.74 Resp: 346.74 Degree: 0
      Cost_io: 346.00 Cost_cpu: 27311919
      Resp_io: 346.00 Resp_cpu: 27311919
Access Path: index (index (FFS))
    Index: I_T_OBJECT_ID
    resc_io: 54.00 resc_cpu: 11812878
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
    Cost: 54.32 Resp: 54.32 Degree: 1
      Cost_io: 54.00 Cost_cpu: 11812878
      Resp_io: 54.00 Resp_cpu: 11812878
Access Path: index (Min/Max)
    Index: I_T_OBJECT_ID
    resc_io: 2.00 resc_cpu: 14443
    ix_sel: 0.000000 ix_sel_with_filters: 0.000000
    Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: I_T_OBJECT_ID
         Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************

--//可以看出oracle分析得到的最佳執行計劃實際上是Best:: AccessPath: IndexRange.
--//cost的成本實際上是IndexRange的成本.

SCOTT@book> select /*+ index(t) */ count(*) from t where object_id between 1 and 1e6;
COUNT(*)
----------
86987

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1ypsa9k66p3us, child number 0
-------------------------------------
select /*+ index(t) */ count(*) from t where object_id between 1 and 1e6
Plan hash value: 565091764
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation         | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |      1 |        |       |   195 (100)|          |      1 |00:00:00.03 |     194 |
|   1 | SORT AGGREGATE   |               |      1 |      1 |     5 |            |          |      1 |00:00:00.03 |     194 |
|* 2 |   INDEX RANGE SCAN| I_T_OBJECT_ID |      1 | 86987 |   424K|   195   (1)| 00:00:03 | 86987 |00:00:00.02 |     194 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=1000000)

--//注:一定要加提示/*+ index(t) */,不然執行計劃會選擇INDEX FAST FULL SCAN.而這樣執行計劃是INDEX RANGE SCAN.cost正好是195.
--//這樣的結果導致執行計劃不會選擇INDEX FULL SCAN (MIN/MAX).

--//實際上可以使用Coalesce引數可以避免短路執行.
SCOTT@book> select Coalesce(1,sleep(1)) from dual ;
COALESCE(1,SLEEP(1))
--------------------
1

SCOTT@book> set timing on
SCOTT@book> select Coalesce(1,sleep(1)) from dual ;
COALESCE(1,SLEEP(1))
--------------------
1

Elapsed: 00:00:00.01
SCOTT@book> set timing off
--//Coalesce引數可以短路執行.

SCOTT@book> Select /*+ ind111ex(t,i_t_object_id) */ Coalesce(max(object_id),3000000)+1 n10 from t;
N10
---------------------
90461

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 7m1705d3c8b1c, child number 0
-------------------------------------
Select /*+ ind111ex(t,i_t_object_id) */
Coalesce(max(object_id),3000000)+1 n10 from t

Plan hash value: 2939893782

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                  | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 | SORT AGGREGATE            |               |      1 |      1 |     5 |            |          |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID |      1 |      1 |     5 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1

--//最主要問題在於開發根本不應該使用nvl2函式.nvl,Coalesce就沒有問題.

20180316不使用INDEX FULL SCAN (MIN/MAX)

相關文章