[20120131]函式索引與取max值的問題3.txt

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 <= 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: 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)的邏輯讀很小,不失為一個好的選擇.