我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

Oracle优化——单表分页优化

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

Oracle优化——单表分页优化

单表分页优化思路：


--创建测试表：

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 * 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  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID  cgjp65zfj1yqa, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID  0w9tbrwkn9tn6, child number 0
-------------------------------------
 select * from (select * from (select a.*,rownum rn from (select  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID  dfwkpppbtc8h7, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------、
SQL_ID  9mm61b7j943sf, child number 0
-------------------------------------
 select * from (select * from (select a.*,rownum rn from (select  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID  g8zgbvt1u1qjq, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID  bapu5jn5vn22r, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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_name,object_id);
select * from (select * from (select a.*,rownum rn from (select  * 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,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------:
SQL_ID  4z6tjgrdjm5a1, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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  * from p_test order by object_id) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID  bxw1059jmgxvx, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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  * from p_test order by object_name) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID  25pm0f6b6m49x, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select  * 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.











免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉

Oracle优化——单表分页优化

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

Oracle优化——单表分页优化

2024-04-02

oracle优化--表优化（临时表）

2024-04-02

MySQL单表亿级数据分页怎么优化？

我觉得对于创新型业务系统的设计，首先满足需求，其次考虑到万一业务井喷发展所要考虑到的临时解决方案，为系统升级预留时间。
MySQL数据分页数据库2024-12-03

sqlserver2005的分页优化

2024-04-02

高级SQL优化系列之深分页优化

PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员。PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/Da
PawSQL数据库管理2024-11-30

oracle优化--表优化（行链接产生实验）

2024-04-02

如何进行SQL优化中的limit分页优化

2024-04-02

优化评论分页查询

2024-04-02

oracle表优化方法教程

2024-04-02

Vue列表页渲染优化的示例分析

2024-04-02

MySQL优化LIMIT分页查询

在MySQL中，使用LIMIT关键字进行分页查询时，如果查询结果集很大，可能会导致性能问题。为了优化LIMIT分页查询，可以采用以下方法：使用索引：确保查询中涉及到的列都已经建立了索引，这样可以大大提高查询速度。同时，尽量避免在WHERE子
MySQL优化LIMIT分页查询
2024-10-20

PHPcms列表页SEO优化指南

PHPcms是一种常用的内容管理系统，对于网站的SEO优化至关重要。在网站优化的过程中，列表页的SEO优化是关键的一步。通过对列表页进行优化，可以提升网站在搜索引擎中的排名，增加网站的曝光度和流量。本文将介绍PHPcms列表页SEO优化的一
PHPcms列表页SEO优化指南
2024-03-15

Oracle分页查询性能优化代码详解

2024-04-02

oracle分页查询优化的方法是什么

Oracle数据库提供了几种方法来优化分页查询：1. 使用ROWNUM：ROWNUM是一个伪列，表示返回结果集中的行数。可以在查询中使用ROWNUM来限制返回的行数。例如，使用ROWNUM>10来获取结果集的第11行及以后的行。这种方法适用
2023-09-20

优化mysql大表分页查询翻页的具体方案

2024-04-02

MySQL 索引优化实践（单表）

2023-10-25

vue单页面SEO优化的实现

2024-04-02

dedecms TAG标签列表分页链接优化方法

先来看看DEDEcms默认的TAG列表分页（5.6为例）： 第1页时：
  • 首页
  • 上一页
    • 2022-06-12

    mysql分页查询如何优化

    2024-04-02

    MySQL中怎么优化分表

    2024-04-02

    热门标签

    Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

    编程热搜

    查看更多

    编程资源站

    • 资料下载
    • 历年试题
    2021年下半年软考高级信息系统项目管理师高频考点精选资料
    2021下半年软考高级信息系统技术知识点记忆口诀精选资料
    2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
    2021下半年软考高级考试备考攻略精选资料
    2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
    2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
    2021下半年软考高级知识点这样记，还担心记不住吗精选资料
    2021年下半年软考高级考试重点汇总精选资料
    2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
    2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
    查看更多
    信息系统项目管理师选择题每日一练（2024）历年试题
    2023年下半年信息系统项目管理师综合知识真题演练历年试题
    查看更多

    目录

    本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
    最新资讯更多
      友情链接编程网

      反馈

      我要
      反馈