1.建立测试例子:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.执行测试命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2070287077
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IF_T_FLAG_ID | 1 | 7 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."SYS_NC00004$"='1')
--可以发现11g下这个问题得到了彻底解决!选择了正确的执行计划INDEX RANGE SCAN (MIN/MAX).
3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)
SQL> alter session set events '10053 trace name context forever';
Session altered.
SQL> select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> alter session set events '10053 trace name context off';
结果如下:
...
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 1597 AvgRowLen: 110.00
Index Stats::
Index: IF_T_FLAG_ID Col#: 4 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Index: I_T_FLAG_ID Col#: 2 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
***** Virtual column Adjustment ******
Column name SYS_NC00004$
cost_cpu 300.00
cost_io 17976931348623157081452742373170435679807056752584499659891747680315726078002853876058955863276687817154045895351438246423432132688946418276846754670353751698604991057
***** End virtual column Adjustment ******
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: TableScan
Cost: 435.83 Resp: 435.83 Degree: 0
Cost_io: 434.00 Cost_cpu: 58372940
Resp_io: 434.00 Resp_cpu: 58372940
Access Path: index (index (FFS))
Index: IF_T_FLAG_ID
resc_io: 69.00 resc_cpu: 43780360
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 70.37 Resp: 70.37 Degree: 1
Cost_io: 69.00 Cost_cpu: 43780360
Resp_io: 69.00 Resp_cpu: 43780360
Access Path: index (Min/Max)
Index: IF_T_FLAG_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: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
4.11g下可以把索引反过来建立也可以:
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> drop index if_t_flag_id;
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 1 | 7 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--当然如果应该经常查询id=:b 的情况下,并且查询以上max(id)的逻辑读很小,不失为一个好的选择.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.执行测试命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2070287077
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IF_T_FLAG_ID | 1 | 7 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."SYS_NC00004$"='1')
--可以发现11g下这个问题得到了彻底解决!选择了正确的执行计划INDEX RANGE SCAN (MIN/MAX).
3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)
SQL> alter session set events '10053 trace name context forever';
Session altered.
SQL> select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> alter session set events '10053 trace name context off';
结果如下:
...
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 1597 AvgRowLen: 110.00
Index Stats::
Index: IF_T_FLAG_ID Col#: 4 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Index: I_T_FLAG_ID Col#: 2 1
LVLS: 1 #LB: 250 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 1564.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
***** Virtual column Adjustment ******
Column name SYS_NC00004$
cost_cpu 300.00
cost_io 17976931348623157081452742373170435679807056752584499659891747680315726078002853876058955863276687817154045895351438246423432132688946418276846754670353751698604991057
***** End virtual column Adjustment ******
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: TableScan
Cost: 435.83 Resp: 435.83 Degree: 0
Cost_io: 434.00 Cost_cpu: 58372940
Resp_io: 434.00 Resp_cpu: 58372940
Access Path: index (index (FFS))
Index: IF_T_FLAG_ID
resc_io: 69.00 resc_cpu: 43780360
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 70.37 Resp: 70.37 Degree: 1
Cost_io: 69.00 Cost_cpu: 43780360
Resp_io: 69.00 Resp_cpu: 43780360
Access Path: index (Min/Max)
Index: IF_T_FLAG_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: IF_T_FLAG_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
4.11g下可以把索引反过来建立也可以:
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> drop index if_t_flag_id;
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 1 | 7 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--当然如果应该经常查询id=:b 的情况下,并且查询以上max(id)的逻辑读很小,不失为一个好的选择.
