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 <= 99900
                 THEN '1'
              ELSE '0'
           END flag, LPAD ('a', 100, 'a') vc
      FROM DUAL
CONNECT BY LEVEL <= 100000;

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)的逻辑读很小,不失为一个好的选择.