rownum取值影响组合索引的使用

创建一个组合索引，分别运行两个SQL,SQL唯一不同的地方是rownum<20和rownum<200

create  index  IDX_TEL_ZU  on  t_s_Tel_Record(Employee_Id,Result)  online  nologging

--<例子1

SQL>  explain  plan  for 

    2   

    2    Select  *

    3        From  (Select  T0.*,  Rownum  As  Count_Id

    4                        From  (Select  Tel_Record_Id,

    5                                                  Employee_Id,

    6                                                  Customer_Id,

    7                                                  Callin_Number,

    8                                                  Calin_Time,

    9                                                  Recall_Number,

  10                                                  Extension,

  11                                                  Operation_Type,

  12                                                  Result,

  13                                                  Operator_Type_Id,

  14                                                  Operation_Desc

  15                                        From  t_s_Tel_Record

  16                                      Where  t_s_Tel_Record.Employee_Id  =  1067

  17                                          And  t_s_Tel_Record.Result  Is  Null

  18                                      Order  By  Tel_Record_Id  Desc)  T0

  19                      Where  Rownum  <=  20)  T1

  20      Where  Count_Id  >=  20  -  20  +  1;

 

Explained

 

SQL>  select  *  from  table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3591109256

----------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                   |    20 | 43660 |  3174   (1)| 00:00:39 |

|*  1 |  VIEW                          |                   |    20 | 43660 |  3174   (1)| 00:00:39 |

|*  2 |   COUNT STOPKEY                |                   |       |       |            |          |

|   3 |    VIEW                        |                   |    21 | 45570 |  3174   (1)| 00:00:39 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| T_S_TEL_RECORD    | 18844 |   993K|  3174   (1)| 00:00:39 |

|   5 |      INDEX FULL SCAN DESCENDING| PK_T_S_TEL_RECORD | 12173 |       |    12   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("COUNT_ID">=1)

   2 - filter(ROWNUM<=20)

   4 - filter("T_S_TEL_RECORD"."EMPLOYEE_ID"=1067 AND "T_S_TEL_RECORD"."RESULT" IS NULL)

 

19 rows selected

 

上面没有走组合索引

 

=====================================================================

=====================================================================

下面改为200，走组合索引了

 

--<例子2

SQL> explain plan for

  2 

  2  Select *

  3    From (Select T0.*, Rownum As Count_Id

  4            From (Select /*+  index(t_s_Tel_Record IDX_TEL_ZU)*/

  5                         Tel_Record_Id,

  6                         Employee_Id,

  7                         Customer_Id,

  8                         Callin_Number,

  9                         Calin_Time,

 10                         Recall_Number,

 11                         Extension,

 12                         Operation_Type,

 13                         Result,

 14                         Operator_Type_Id,

 15                         Operation_Desc

 16                    From t_s_Tel_Record

 17                   Where t_s_Tel_Record.Employee_Id = 1067

 18                     And t_s_Tel_Record.Result Is Null

 19                  Order By Tel_Record_Id Desc

 20                   ) T0

 21           Where Rownum <= 200) T1

 22   Where Count_Id >= 20 - 20 + 1;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2274292752

----------------------------------------------------------------------------------------------------------

| Id | Operation            | Name      | Rows | Bytes |TempSpc| Cost (%CPU)| Time   |

----------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT        |        |  200 |  426K|    | 4043  (1)| 00:00:49 |

|* 1 | VIEW              |        |  200 |  426K|    | 4043  (1)| 00:00:49 |

|* 2 |  COUNT STOPKEY         |        |    |    |    |      |     |

|  3 |  VIEW             |        | 18844 |  38M|    | 4043  (1)| 00:00:49 |

|* 4 |   SORT ORDER BY STOPKEY    |        | 18844 |  993K| 2968K| 4043  (1)| 00:00:49 |

|  5 |   TABLE ACCESS BY INDEX ROWID| T_S_TEL_RECORD | 18844 |  993K|    | 3785  (1)| 00:00:46 |

|* 6 |    INDEX RANGE SCAN     | IDX_TEL_ZU   | 18844 |    |    |  17  (6)| 00:00:01 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  1 - filter("COUNT_ID">=1)

  2 - filter(ROWNUM<=200)

  4 - filter(ROWNUM<=200)

  6 - access("T_S_TEL_RECORD"."EMPLOYEE_ID"=1067 AND "T_S_TEL_RECORD"."RESULT" IS NULL)

 

21 rows selected

 

=====================================================================

=====================================================================

rownum取200就走组合索引，20就不走组合索引，我们看到取20的时候

是INDEX FULL SCAN DESCENDING，这里就已经排序，oracle想通过全

索引扫描来避免order by（因为全索引扫描是排序的），而当取的数据

多了的时候，oracle认为排序和走组合索引效率更高。

 

实际上这里oracle判断出了问题， 因为不管是rownum这里取200还是20的时候

通过走组合索引的效率都要高于全索引扫描，在例子上我加如下hints

/*+ index(t_s_Tel_Record IDX_TEL_ZU)*/

效率仍然比走全索引扫描高，速度快了1倍

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10678398/viewspace-722127/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-722127/