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

2022-11-30 16:22:03

--//链接: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

--//连索引都不用.设置约束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就没有问题.

码农公寓

相关文章