[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就没有问题.