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

最新推荐文章于 2025-03-28 19:20:15 发布

转载最新推荐文章于 2025-03-28 19:20:15 发布 · 470 阅读

文章标签：

本文探讨了在Oracle中使用RowNum进行数据检索时，不同数值设置对索引选择的影响。发现当RowNum小于20时，Oracle选择了全索引扫描；而当RowNum增大到200时，则更倾向于使用组合索引。通过对SQL执行计划的分析，指出在某些情况下，即使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 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

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

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

| 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 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

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

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

| 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/