1.建立测试例子:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.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: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
同样情况与10.2.0.3相同!如果access 没有使用函数,执行计划可以走INDEX RANGE SCAN (MIN/MAX),而如果查询使用nvl(flag,'1')='1',执行计划是INDEX RANGE SCAN.
3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)
--忽略.cost=2也说明分析成本的时候是走INDEX RANGE SCAN (MIN/MAX),而实际的执行计划是INDEX RANGE SCAN.
如果查询使用count(id),也能说明问题,在这样的情况下,同样走INDEX RANGE SCAN,cost=63.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 63 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 63 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
4.在10g下如何优化上述sql语句呢?可以把索引反过来建立.
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> 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: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
--情况依旧,删除索引在测试if_t_flag_id.(或者使用hint).
SQL> drop index if_t_flag_id;
Index dropped.
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 | | 50036 | 342K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50036 | 342K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--对比10.2.0.3可以发现:
--使用索引IF_T_ID_FLAG,但是走的是INDEX FULL SCAN (MIN/MAX),实际上这个查询是从id的最大值开始只要满足nvl(flag,'1')='1'的条件停止.
--如果nvl(flag,'1')='1'的最大id很小,以上情况最坏的是扫描整个索引.
update t set flag='0' where id>=11;
commit;
SQL> set autot traceonly
SQL> select /*+ gather_plan_statistics index(t,if_t_id_flag) */ max(id) from t where nvl(flag,'1')='1';
执行计划
----------------------------------------------------------
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("FLAG",'1')='1')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
0 physical reads
9220 redo size
335 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以发现逻辑读很多达到了378. 看来这个方法仅仅在满足条件nvl(flag,'1')='1'最大id很大的情况下比较好.
--当然如果应该经常查询id=:b 的情况下,并且查询以上max(id)的逻辑读很小,不失为一个好的选择.
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.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: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
同样情况与10.2.0.3相同!如果access 没有使用函数,执行计划可以走INDEX RANGE SCAN (MIN/MAX),而如果查询使用nvl(flag,'1')='1',执行计划是INDEX RANGE SCAN.
3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)
--忽略.cost=2也说明分析成本的时候是走INDEX RANGE SCAN (MIN/MAX),而实际的执行计划是INDEX RANGE SCAN.
如果查询使用count(id),也能说明问题,在这样的情况下,同样走INDEX RANGE SCAN,cost=63.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 63 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 63 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
4.在10g下如何优化上述sql语句呢?可以把索引反过来建立.
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> 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: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
--情况依旧,删除索引在测试if_t_flag_id.(或者使用hint).
SQL> drop index if_t_flag_id;
Index dropped.
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 | | 50036 | 342K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50036 | 342K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--对比10.2.0.3可以发现:
--使用索引IF_T_ID_FLAG,但是走的是INDEX FULL SCAN (MIN/MAX),实际上这个查询是从id的最大值开始只要满足nvl(flag,'1')='1'的条件停止.
--如果nvl(flag,'1')='1'的最大id很小,以上情况最坏的是扫描整个索引.
update t set flag='0' where id>=11;
commit;
SQL> set autot traceonly
SQL> select /*+ gather_plan_statistics index(t,if_t_id_flag) */ max(id) from t where nvl(flag,'1')='1';
执行计划
----------------------------------------------------------
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("FLAG",'1')='1')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
0 physical reads
9220 redo size
335 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以发现逻辑读很多达到了378. 看来这个方法仅仅在满足条件nvl(flag,'1')='1'最大id很大的情况下比较好.
--当然如果应该经常查询id=:b 的情况下,并且查询以上max(id)的逻辑读很小,不失为一个好的选择.
