加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle优化——单表分页优化

发布时间：2020-12-12 13:26:53 所属栏目：百科 来源：网络整理
导读：单表分页优化思路： --创建测试表： SQL create table t_test as select * from dba_objects;? Table created. 如，下面的sql （没有过滤条件，只有排序），要将查询结果分页显示，每页显示10条，如： select * from t_test order by object_id; 例子： 1、
单表分页优化思路：


--创建测试表：

SQL> create table t_test as select * from dba_objects;?


Table created.


如，下面的sql （没有过滤条件，只有排序），要将查询结果分页显示，每页显示10条，如：

select * from t_test order by object_id;


例子：

1、分页查询sql语句，如下（通常会采用下面的方法，但是这是错误的分页框架）

语法：select * from (select t.*,rownum rn from (需要分页的sql)t ) where rn >=1 and rn <=10;


SQL>?select?*?from?(select?t.*,rownum?rn?from?(select?*?from?t_test?order?by?object_id)?t)?where?rn?>=1?and?rn?<=10;
Execution?Plan
----------------------------------------------------------
Plan?hash?value:?3603170480
----------------------------------------------------------------------------------------
|?Id??|?Operation?????????????|?Name???|?Rows??|?Bytes?|TempSpc|?Cost?(%CPU)|?Time?????|
----------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT??????|????????|?80700?|????16M|???????|??3918???(1)|?00:00:48?|
|*??1?|??VIEW?????????????????|????????|?80700?|????16M|???????|??3918???(1)|?00:00:48?|
|???2?|???COUNT???????????????|????????|???????|???????|???????|????????????|??????????|
|???3?|????VIEW???????????????|????????|?80700?|????15M|???????|??3918???(1)|?00:00:48?|
|???4?|?????SORT?ORDER?BY?????|????????|?80700?|????15M|????19M|??3918???(1)|?00:00:48?|
|???5?|??????TABLE?ACCESS?FULL|?t_test?|?80700?|????15M|???????|???287???(1)|?00:00:04?|
----------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN"<=10?AND?"RN">=1)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
Statistics
----------------------------------------------------------
????????331??recursive?calls
??????????0??db?block?gets
???????1211??consistent?gets
???????1024??physical?reads
??????????0??redo?size
???????2075??bytes?sent?via?SQL*Net?to?client
????????523??bytes?received?via?SQL*Net?from?client
??????????2??SQL*Net?roundtrips?to/from?client
??????????1??sorts?(memory)
??????????0??sorts?(disk)
?????????10??rows?processed


? ?从上面的执行计划可以看出，该sql走了全表扫描，如果该表很大，则先要将该表排序，再取出其中10行数据，这样会出现严重的性能问题，所以该sql不能走全表扫描，必须走索引扫描。（因为索引已经排序了，可使用索引来消除排序）一般分页语句中都有排序。

如下，在object_id列建立索引，并在索引中添加一个常量0，如下：（在索引中添加一个常量0，是因为object_id列允许位null，如果不添加常量（不一定是0，可以是1,2,3，也可以是英文字母），索引中就不能存储null值，；因为sql中并没有剔除null值，所以我们必须要添加一个常量，让索引存储null，这样才能使sql走索引。）

SQL> create index idx_test on t_test(object_id,0);


SQL> select * from (select t.*,rownum rn from (select /*+ index)t_test idx_test) */ * from t_test order by object_id) t) where rn >=1 and rn <=10;


SQL> alter session set statistics_level=all;

SQL>?select?*?from?table(dbms_xplan.display_cursor(null,null,'allstats?last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID??gvq2rvmutr85w,?child?number?0
-------------------------------------
?select?*?from?(select?t.*,rownum?rn?from?(select?/*+?index)t_test
idx_test)?*/?*?from?t_test?order?by?object_id)?t)?where?rn?>=1?and?rn
<=10
Plan?hash?value:?3119682446
-----------------------------------------------------------------------------------------------------
|?Id??|?Operation??????????????????????|?Name?????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|
-----------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT???????????????|??????????|??????1?|????????|?????10?|00:00:00.08?|????1245?|
|*??1?|??VIEW??????????????????????????|??????????|??????1?|??80700?|?????10?|00:00:00.08?|????1245?|
|???2?|???COUNT????????????????????????|??????????|??????1?|????????|??71903?|00:00:00.07?|????1245?|
|???3?|????VIEW????????????????????????|??????????|??????1?|??80700?|??71903?|00:00:00.06?|????1245?|
|???4?|?????TABLE?ACCESS?BY?INDEX?ROWID|?t_test???|??????1?|??80700?|??71903?|00:00:00.04?|????1245?|
|???5?|??????INDEX?FULL?SCAN???????????|?IDX_test?|??????1?|??80700?|??71903?|00:00:00.01?|?????181?|
-----------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter(("RN"<=10?AND?"RN">=1))
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
28?rows?selected.

(因为sql语句没有where过滤条件，强制走索引只能走index full scan，无法走索引范围扫描（index range scan）。E-Rows的显示 说明了扫描了索引中所有叶子块，一共消耗了1245个逻辑读；理想的执行计划是：index full scan只扫描一个最多几个索引叶子块，最根本的原因还在于这个分页框架错了！)


2、正确的分页框架：

语法：select * from (select * from (select a.*,rownum rn from (需要分页的sql) a ) where rownum <=10) where rn >=1;


SQL>?select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test)?*/?*?from?t_test?order?by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>?alter?session?set?statistics_level=all;
SQL>?select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID??cgjp65zfj1yqa,?child?number?0
-------------------------------------
select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+
index(t_test?idx_test)?*/?*?from?t_test?order?by?object_id)?a?)?where
rownum?<=10)?where?rn?>=1
Plan?hash?value:?1201925926
-------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name?????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|
-------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|*??1?|??VIEW????????????????????????????|??????????|??????1?|?????10?|?????10?|00:00:00.01?|???????5?|
|*??2?|???COUNT?STOPKEY??????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|???3?|????VIEW??????????????????????????|??????????|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|
|???4?|?????COUNT????????????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|???5?|??????VIEW????????????????????????|??????????|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|
|???6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test???|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|
|???7?|????????INDEX?FULL?SCAN???????????|?IDX_test?|??????1?|??80700?|?????10?|00:00:00.01?|???????3?|
-------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
31?rows?selected.


(从上面的执行计划可以看到，sql走 index full scan，只扫描了10条数据（id=7 A-Rows=10）就停止了（id=2 COUNT STOPKEY）一共消耗了5个逻辑读；该执行计划利用索引已经排序的特性只扫描索引获取了10条数据，然后再利用count stopkey特性，获取到分页需要的数据，sql立即停止运行，这才是最佳的执行计划。)


注意：

? 如果分页语句中有排序（order by）,要利用索引已经排序特性，将order by的列包含在索引中，同时也要利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。


例子：

如下两条sq（注意，过滤条件是等值过滤，当然也有order by）l，改成分页语句，并查看执行计划，如下：

select * from t_test where owner='SCOTT' order by object_id;

select * from t_test where owner='SYS' order by object_id;

(其中第一条sql语句的过滤条件是where owner='SCOTT'；该过滤条件能过滤掉表中大部分数据。第二条sql语句的过滤条件where owner='SYS'，能过滤表中一半数据)


---执行第一条sql语句：

SQL>?select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test)?*/?*?from?t_test?where?owner='SCOTT'?order?by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>?alter?session?set?statistics_level=all;
SQL>?select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID??0w9tbrwkn9tn6,?child?number?0
-------------------------------------
?select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+
index(t_test?idx_test)?*/?*?from?t_test?where?owner='SCOTT'?order?by
object_id)?a?)?where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?1201925926
-------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name?????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|
-------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|??????????|??????1?|????????|?????10?|00:00:00.04?|????1245?|
|*??1?|??VIEW????????????????????????????|??????????|??????1?|?????10?|?????10?|00:00:00.04?|????1245?|
|*??2?|???COUNT?STOPKEY??????????????????|??????????|??????1?|????????|?????10?|00:00:00.04?|????1245?|
|???3?|????VIEW??????????????????????????|??????????|??????1?|?????12?|?????10?|00:00:00.04?|????1245?|
|???4?|?????COUNT????????????????????????|??????????|??????1?|????????|?????10?|00:00:00.04?|????1245?|
|???5?|??????VIEW????????????????????????|??????????|??????1?|?????12?|?????10?|00:00:00.04?|????1245?|
|*??6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test???|??????1?|?????12?|?????10?|00:00:00.04?|????1245?|
|???7?|????????INDEX?FULL?SCAN???????????|?IDX_test?|??????1?|??80700?|??71901?|00:00:00.01?|?????181?|
-------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
???6?-?filter("OWNER"='SCOTT')
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
32?rows?selected.

---执行第2条语句，如下：

SQL>?select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test)?*/?*?from?t_test?where?owner='SYS'?order?by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>?alter?session?set?statistics_level=all;
SQL>?select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID??dfwkpppbtc8h7,rownum?rn?from?(select?/*+
index(t_test?idx_test)?*/?*?from?t_test?where?owner='SYS'?order?by
object_id)?a?)?where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?1201925926
-------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name?????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|
-------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|*??1?|??VIEW????????????????????????????|??????????|??????1?|?????10?|?????10?|00:00:00.01?|???????5?|
|*??2?|???COUNT?STOPKEY??????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|???3?|????VIEW??????????????????????????|??????????|??????1?|??28483?|?????10?|00:00:00.01?|???????5?|
|???4?|?????COUNT????????????????????????|??????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|
|???5?|??????VIEW????????????????????????|??????????|??????1?|??28483?|?????10?|00:00:00.01?|???????5?|
|*??6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test???|??????1?|??28483?|?????10?|00:00:00.01?|???????5?|
|???7?|????????INDEX?FULL?SCAN???????????|?IDX_test?|??????1?|??80700?|?????10?|00:00:00.01?|???????3?|
-------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
???6?-?filter("OWNER"='SYS')
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
32?rows?selected.


? ?从上面的执行计划可以看出，两条sql都走了 index full scan，第1条sql从索引中扫描了71901条数据（id=7 A-Rows=71901），在回表的时候对数据进行了大量过滤（id=6），最后得到10条数据，耗费了1245个逻辑读。

? ?第2条sql从索引中扫描了10条数据，耗费了5个逻辑读。可以看出，第二条sql的执行计划是正确的，而第一条sql的执行计划是错误的，应该尽量在索引扫描的时候就取得10行数据。

（为什么上面的两条sql只有过滤条件不一样，而第一条sql的执行计划就错了呢？这是因为第一条sql的过滤条件where owner='SCOTT'，在表中只有很少数据，通过扫描object_id列的索引，然后在回表去匹配owner='SCOTT'，因为owner='SCOTT'数据量很少，要搜索大量数据才能匹配上。而第二条sql的过滤条件owner='SYS'，因为数据量多，只需要搜索少量的数据就能匹配上。）


---优化第一条sql：（就必须让过滤条件的列出现在索引中，如下：）


SQL>?create?index?idx_test_all?on?t_test(owner,object_id);??---创建一个组合索引，包含owner字段
SQL>??select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test_all)?*/?*?from?t_test?where?owner='SCOTT'?order?by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>??select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
---------------------------------------------、
SQL_ID??9mm61b7j943sf,rownum?rn?from?(select?/*+
index(t_test?idx_test_all)?*/?*?from?t_test?where?owner='SCOTT'?order
by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?3696904346
-----------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name?????????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|
-----------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|??????????????|??????1?|????????|?????10?|00:00:00.01?|???????6?|
|*??1?|??VIEW????????????????????????????|??????????????|??????1?|?????10?|?????10?|00:00:00.01?|???????6?|
|*??2?|???COUNT?STOPKEY??????????????????|??????????????|??????1?|????????|?????10?|00:00:00.01?|???????6?|
|???3?|????VIEW??????????????????????????|??????????????|??????1?|?????12?|?????10?|00:00:00.01?|???????6?|
|???4?|?????COUNT????????????????????????|??????????????|??????1?|????????|?????10?|00:00:00.01?|???????6?|
|???5?|??????VIEW????????????????????????|??????????????|??????1?|?????12?|?????10?|00:00:00.01?|???????6?|
|???6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test???????|??????1?|?????12?|?????10?|00:00:00.01?|???????6?|
|*??7?|????????INDEX?RANGE?SCAN??????????|?IDX_test_ALL?|??????1?|?????12?|?????10?|00:00:00.01?|???????3?|
-----------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
???7?-?access("OWNER"='SCOTT')
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
32?rows?selected.

（这时候会发现，sql走了索引范围扫描，从索引中扫描了10条数据，一共耗费了6个逻辑读。这说明现在的执行计划是正确的。）


注意：

? 在实际的生成环境中，过滤条件一般都是绑定变量，我们无法控制传参究竟传入哪个值，这就不能确定返回数据究竟是多还是少了，所以，建议最好将排序的列包含在索引中。


但是要注意：如果排序列有多个列，创建索引的时候，我们要将所有的排序列包含在索引中，并且要注意排序列先后顺序，而且还要注意列时升序还是降序。如果分页语句中排序列只有一个列，但是是降序显示的，创建索引的时候就没必要降序创建索引了，我们可以使用hint：index_desc 让索引降序扫描就可以了。


例子：

(创建索引，只能是object_id在前，object_name在后，另外object_name是降序显示的，那么在创建索引的时候，还要指定object_name列降序排序。下面的sql也没有过滤条件，在创建索引的时候还要加个常量，如下所示：)

SQL>?create?index?idx_test_2?on?t_test(object_id,object_name?desc,0);??---创建一个组合索引，包含owner字段
SQL>??select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test_2)?*/?*?from?t_test??order?by?object_id,object_name?desc)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>??select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID??g8zgbvt1u1qjq,rownum?rn?from?(select?/*+
index(t_test?idx_test_2)?*/?*?from?t_test??order?by
object_id,object_name?desc)?a?)?where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?2251915778
------------------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name???????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|?Reads??|
------------------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|*??1?|??VIEW????????????????????????????|????????????|??????1?|?????10?|?????10?|00:00:00.01?|???????5?|??????1?|
|*??2?|???COUNT?STOPKEY??????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|???3?|????VIEW??????????????????????????|????????????|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|??????1?|
|???4?|?????COUNT????????????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|???5?|??????VIEW????????????????????????|????????????|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|??????1?|
|???6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test?????|??????1?|??80700?|?????10?|00:00:00.01?|???????5?|??????1?|
|???7?|????????INDEX?FULL?SCAN???????????|?IDX_test_2?|??????1?|??80700?|?????10?|00:00:00.01?|???????3?|??????1?|
------------------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
31?rows?selected.


如果在创建索引的时候没有指定 object_name列降序排序，那么执行计划中会出现sort order by。因为索引中排序和分页语句中排序不一致，如:


SQL>?create?index?idx_test_3?on?t_test(object_id,object_name,0);
SQL>??select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test_3)?*/?*?from?t_test??order?by?object_id,'allstats?last'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID??bapu5jn5vn22r,rownum?rn?from?(select?/*+
index(t_test?idx_test_3)?*/?*?from?t_test??order?by
object_id,object_name?desc)?a?)?where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?3547841113
-----------------------------------------
|?Id??|?Operation?????????????????????????|?Name???????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|?Reads??|??OMem?|??1Mem?|?Used-Mem?|
----------------------------------------------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT??????????????????|????????????|??????1?|????????|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|*??1?|??VIEW?????????????????????????????|????????????|??????1?|?????10?|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|*??2?|???COUNT?STOPKEY???????????????????|????????????|??????1?|????????|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|???3?|????VIEW???????????????????????????|????????????|??????1?|??80700?|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|???4?|?????COUNT?????????????????????????|????????????|??????1?|????????|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|???5?|??????VIEW?????????????????????????|????????????|??????1?|??80700?|?????10?|00:00:00.10?|????1490?|????426?|???????|???????|??????????|
|???6?|???????SORT?ORDER?BY???????????????|????????????|??????1?|??80700?|?????10?|00:00:00.10?|????1490?|????426?|????10M|??1272K|?9811K?(0)|
|???7?|????????TABLE?ACCESS?BY?INDEX?ROWID|?t_test?????|??????1?|??80700?|??71903?|00:00:00.06?|????1490?|????426?|???????|???????|??????????|
|???8?|?????????INDEX?FULL?SCAN???????????|?IDX_test_3?|??????1?|??80700?|??71903?|00:00:00.03?|?????427?|????426?|???????|???????|??????????|
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
32?rows?selected.


(从上面可以看出，执行计划中出现了 sort order by，这就意味着分页语句没有利用到索引已经排序的特性，执行计划是错误的，这时候就需要创建正确的索引。)


例子3：

一条sql（，过滤条件有等值条件，也有非等值条件，当然也有order by），如下，将下面的sql分页查询：

select * from t_test where owner='SYS' and object_id > 1000 order by object_name;


--：创建索引（因为owner是等值过滤，object_Id是非等值过滤，创建索引的时候要优先将等值过滤列和排序列组合在一起，然后再将非等值过滤列放到后面）如下：

create?index?idx_test_4?on?t_test(owner,object_id);
select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(t_test?idx_test_4)?*/?*?from?t_test?where?owner='SYS'?and?object_id?>?1000?order?by?object_name)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>??select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
PLAN_TABLE_OUTPUT
------------------------------------------:
SQL_ID??4z6tjgrdjm5a1,rownum?rn?from?(select?/*+
index(t_test?idx_test_4)?*/?*?from?t_test?where?owner='SYS'?and
object_id?>?1000?order?by?object_name)?a?)?where?rownum?<=10)?where?rn
>=1
Plan?hash?value:?1432357471
------------------------------------------------------------------------------------------------------------------
|?Id??|?Operation????????????????????????|?Name???????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|?Reads??|
------------------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT?????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|??????14?|??????2?|
|*??1?|??VIEW????????????????????????????|????????????|??????1?|?????10?|?????10?|00:00:00.01?|??????14?|??????2?|
|*??2?|???COUNT?STOPKEY??????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|??????14?|??????2?|
|???3?|????VIEW??????????????????????????|????????????|??????1?|??25683?|?????10?|00:00:00.01?|??????14?|??????2?|
|???4?|?????COUNT????????????????????????|????????????|??????1?|????????|?????10?|00:00:00.01?|??????14?|??????2?|
|???5?|??????VIEW????????????????????????|????????????|??????1?|??25683?|?????10?|00:00:00.01?|??????14?|??????2?|
|???6?|???????TABLE?ACCESS?BY?INDEX?ROWID|?t_test?????|??????1?|??25683?|?????10?|00:00:00.01?|??????14?|??????2?|
|*??7?|????????INDEX?RANGE?SCAN??????????|?IDX_test_4?|??????1?|????256?|?????10?|00:00:00.01?|???????4?|??????2?|
------------------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
???7?-?access("OWNER"='SYS'?AND?"OBJECT_ID">1000)
???????filter("OBJECT_ID">1000)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
34?rows?selected.

（可以看出，执行计划中没有出现 sort order by，逻辑读也只有14个，说明执行计划是正确的。）



注意：

? ①：如果分页语句中有排序（order by），要利用索引已经排序的特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用rownum的count stopkey特性来优化分页sql。如果分页中没有排序，可以直接利用rownum的count stopkey特性来优化分页sql。


?②：要想一眼看出执行计划是否正确，就要先看分页语句有没有order by，再看执行计划有没有sort order by，如果执行计划中有sort order by，说明执行计划一般是错误的。


?③：如果分页语句中排序的表是分区表，这时我们要看分页语句中是否有跨区分区扫描，如果有跨分区扫描，创建的索引一般都是global索引，如果不创建global索引，就无法保证分页的顺序与所有的顺序是一致的。如果就只扫描一个分区，这时可以创建local索引：


例子：创建分区表，并导入数据

?CREATE TABLE p_test

? ?(? ? "OWNER" VARCHAR2(30),

? ? ? ? "OBJECT_NAME" VARCHAR2(128),

? ? ? ? "SUBOBJECT_NAME" VARCHAR2(30),

? ? ? ? "OBJECT_ID" NUMBER,

? ? ? ? "DATA_OBJECT_ID" NUMBER,

? ? ? ? "OBJECT_TYPE" VARCHAR2(19),

? ? ? ? "CREATED" DATE,

? ? ? ? "LAST_DDL_TIME" DATE,

? ? ? ? "TIMESTAMP" VARCHAR2(19),

? ? ? ? "STATUS" VARCHAR2(7),

? ? ? ? "TEMPORARY" VARCHAR2(1),

? ? ? ? "GENERATED" VARCHAR2(1),

? ? ? ? "SECONDARY" VARCHAR2(1),

? ? ? ? "NAMESPACE" NUMBER,

? ? ? ? "EDITION_NAME" VARCHAR2(30)

? ?) partition by range (object_id)

? ?(

? ?partition p1 values less than (10000),

? ?partition p2 values less than (20000),

? ?partition p3 values less than (30000),

? ?partition p4 values less than (40000),

? ?partition p5 values less than (50000),

? ?partition p6 values less than (60000),

? ?partition p7 values less than (70000),

? ?partition px values less than (maxvalue));


?SQL> insert into p_test select * from dba_objects;


71917 rows created.??


---例子1：分页语句：

select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_id) a ) where rownum <=10) where rn >=1;


? （这个分页语句没有过滤条件，因此会扫描表中的所有分区。因为排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是如果将范围分区改为list分区或者hash分区，这时我们就必须创建global索引，因为list分区和hash分区是无序的。）


SQL> create index idx_test_id on p_test(object_id,0) local;? ---创建local索引


select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(p_test?idx_test_id?)?*/?*?from?p_test?order?by?object_id)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>?select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
SQL_ID??bxw1059jmgxvx,rownum?rn?from?(select?/*+
index(p_test?idx_test_id?)?*/?*?from?p_test?order?by?object_id)?a?)
where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?1291390031
--------------------------------------------------------------------------------------------------------------------------
|?Id??|?Operation???????????????????????????????|?Name????????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|?Reads??|
--------------------------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|*??1?|??VIEW???????????????????????????????????|?????????????|??????1?|?????10?|?????10?|00:00:00.01?|???????5?|??????1?|
|*??2?|???COUNT?STOPKEY?????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|???3?|????VIEW?????????????????????????????????|?????????????|??????1?|??63696?|?????10?|00:00:00.01?|???????5?|??????1?|
|???4?|?????COUNT???????????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|???????5?|??????1?|
|???5?|??????VIEW???????????????????????????????|?????????????|??????1?|??63696?|?????10?|00:00:00.01?|???????5?|??????1?|
|???6?|???????PARTITION?RANGE?ALL???????????????|?????????????|??????1?|??63696?|?????10?|00:00:00.01?|???????5?|??????1?|
|???7?|????????TABLE?ACCESS?BY?LOCAL?INDEX?ROWID|?P_TEST??????|??????1?|??63696?|?????10?|00:00:00.01?|???????5?|??????1?|
|???8?|?????????INDEX?FULL?SCAN?????????????????|?IDX_TEST_ID?|??????1?|??63696?|?????10?|00:00:00.01?|???????3?|??????1?|
--------------------------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
32?rows?selected.


例子2：分页语句（根据object_name排序）

select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_name) a ) where rownum <=10) where rn >=1;


这时候我们需要创建global索引，因为如果是本地索引就会产生 sort order by


SQL>?drop?index?idx_test_id?;?
SQL>?create?index?idx_test_id?on?p_test(object_name,0);?
select?*?from?(select?*?from?(select?a.*,rownum?rn?from?(select?/*+?index(p_test?idx_test_id?)?*/?*?from?p_test?order?by?object_name)?a?)?where?rownum?<=10)?where?rn?>=1;
SQL>?select?*?from?table(dbms_xplan.display_cursor(null,'allstats?last'));
SQL_ID??25pm0f6b6m49x,rownum?rn?from?(select?/*+
index(p_test?idx_test_id?)?*/?*?from?p_test?order?by?object_name)?a?)
where?rownum?<=10)?where?rn?>=1
Plan?hash?value:?246970912
--------------------------------------------------------------------------------------------------------------------------
|?Id??|?Operation???????????????????????????????|?Name????????|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|?Reads??|
--------------------------------------------------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|??????10?|??????2?|
|*??1?|??VIEW???????????????????????????????????|?????????????|??????1?|?????10?|?????10?|00:00:00.01?|??????10?|??????2?|
|*??2?|???COUNT?STOPKEY?????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|??????10?|??????2?|
|???3?|????VIEW?????????????????????????????????|?????????????|??????1?|??63696?|?????10?|00:00:00.01?|??????10?|??????2?|
|???4?|?????COUNT???????????????????????????????|?????????????|??????1?|????????|?????10?|00:00:00.01?|??????10?|??????2?|
|???5?|??????VIEW???????????????????????????????|?????????????|??????1?|??63696?|?????10?|00:00:00.01?|??????10?|??????2?|
|???6?|???????TABLE?ACCESS?BY?GLOBAL?INDEX?ROWID|?P_TEST??????|??????1?|??63696?|?????10?|00:00:00.01?|??????10?|??????2?|
|???7?|????????INDEX?FULL?SCAN??????????????????|?IDX_TEST_ID?|??????1?|??63696?|?????10?|00:00:00.01?|???????4?|??????2?|
--------------------------------------------------------------------------------------------------------------------------
Predicate?Information?(identified?by?operation?id):
---------------------------------------------------
???1?-?filter("RN">=1)
???2?-?filter(ROWNUM<=10)
Note
-----
???-?dynamic?sampling?used?for?this?statement?(level=2)
31?rows?selected.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同