ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分页查询的性能

分页查询的性能

原创 Linux操作系统 作者:admincy 时间:2019-05-30 10:45:05 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4  

分页查询中使用order by子句中的索引和nested loop关联方式,可用COUNT STOPKEY只检索出每页的记录数据,不需要检索所有的记录后再排序,即省去SORT ORDER BY STOPKEY操作,大大提高执行效率。

 

 

1.                分页查询一般是有order by子句的如以下的语句:

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM company_draft c, member m, sample_draft s --,window_product w

                 WHERE s.company_id = c.id

                   AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                -- and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

 

2.                可在order by 字段上建立相应的索引:

create index sample_draft_gmid on sample_draft (gmt_modified DESC,id) tablespace olps_data;

 

 

3.                3. 此时运行速度很快,因为执行计划中有COUNT STOPKEY  ,表明优化器通过扫描索引SAMPLE_DRAFT_GMID,因为在索引中已经对order by子句的字段进行了排序,则在|   3 |  所生成的 VIEW中,只存在30条符合条件的记录 ,对于其它剩下的记录不再进行其中的关联操作。这样在生成view时,大大减少了关联的数据量

 

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

| Id  | Operation                        |  Name               | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT                 |                     | 30000 |   791K|  4414K|

|*  1 |  VIEW                            |                     | 30000 |   791K|  4414K|

|*  2 |   COUNT STOPKEY                  |                     |       |       |       |

|   3 |    VIEW                          |                     |  2196K|    29M|  4414K|

|   4 |     NESTED LOOPS                 |                     |  2196K|   117M|  4393K|

|   5 |      NESTED LOOPS                |                     |  2196K|   113M|  2197K|

|   6 |       TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT        |  2196K|    58M|   826 |

|   7 |        INDEX FULL SCAN           | SAMPLE_DRAFT_GMID   |  2196K|       |    26 |

|*  8 |       TABLE ACCESS BY INDEX ROWID| COMPANY_DRAFT       |     1 |    26 |     1 |

|*  9 |        INDEX RANGE SCAN          | COMPANY_DRAFT_PK    |     2 |       |       |

|* 10 |      INDEX RANGE SCAN            | MEMBER_COMPANY_IND  |     1 |     2 |     1 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("from$_subquery$_001"."RNUM">=20000)

   2 - filter(ROWNUM<=30000)

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

   9 - access("S"."COMPANY_ID"="C"."ID")

  10 - access("M"."COMPANY_ID"="C"."ID")

       filter("M"."COMPANY_ID" IS NOT NULL)

 

Note: cpu costing is off

 

4.                而以下语句却不同:没有用到索引SAMPLE_DRAFT_GMID,而只是用到了索引SAMPLE_DRAFT_CID_IND,此索引建立在字段GMT_MODIFIED, STATUS, COMPANY_ID上的,与order by的排序无关。此时需将所有数据关联后排序,使用了SORT ORDER BY STOPKEY后再使用COUNT STOPKEY

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT /*+ ordered */

          --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM company_draft c, member m, sample_draft s --,window_product w

                 WHERE s.company_id = c.id

                   AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                -- and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

 

PLAN_TABLE_OUTPUT

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

| Id  | Operation                       |  Name                 | Rows  | Bytes |TempSpc| Cost  |

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

|   0 | SELECT STATEMENT                |                       |    30 |   810 |       |   471K|

|*  1 |  VIEW                           |                       |    30 |   810 |       |   471K|

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

|   3 |    VIEW                         |                       |  2196K|    29M|       |   471K|

|*  4 |     SORT ORDER BY STOPKEY       |                       |  2196K|   117M|   286M|   471K|

|   5 |      TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT          |   117 |  3276 |       |    23 |

|   6 |       NESTED LOOPS              |                       |  2196K|   117M|       |   451K|

|   7 |        NESTED LOOPS             |                       | 18785 |   513K|       | 19026 |

|*  8 |         TABLE ACCESS FULL       | COMPANY_DRAFT         | 18783 |   476K|       |   243 |

|*  9 |         INDEX RANGE SCAN        | MEMBER_COMPANY_IND    |     1 |     2 |       |     1 |

|* 10 |        INDEX RANGE SCAN         | SAMPLE_DRAFT_CID_IND  |  1551 |       |       |     2 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("from$_subquery$_001"."RNUM">=20)

   2 - filter(ROWNUM<=30)

   4 - filter(ROWNUM<=30)

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

   9 - access("M"."COMPANY_ID"="C"."ID")

       filter("M"."COMPANY_ID" IS NOT NULL)

  10 - access("S"."COMPANY_ID"="C"."ID")

 

Note: cpu costing is off

Statistics

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

          0  recursive calls

          0  db block gets

     730389  consistent gets

       6309  physical reads

          0  redo size

        926  bytes sent via SQL*Net to client

        504  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

5. 经测试 ,使用hash join无法达到以上效果,即使强制使用索引,且SAMPLE_DRAFT表为driver 表,。

 

SELECT *

  FROM (SELECT t1.*, rownum rnum

          FROM (SELECT /*+ ordered  use_hash(s c) index(s sample_draft_gmid)  */

          --/*+ ordered use_hash(s c) use_hash(s w) use_hash(c m) index(s sample_draft_gmid) */--index(s SAMPLE_D_MDF_STS_CID_IND)

                 s.ROWID AS srid, c.ROWID AS crid

                  FROM sample_draft s,company_draft c--, member m -- ,window_product w

                 WHERE s.company_id = c.id

                   --AND m.company_id = c.id

                   AND (c.IS_VALIDATE = 'enabled' OR

                       (c.IS_VALIDATE = 'disabled' AND

                       c.charge_type = 'tbpaid'))

                 --and      s.id = w.product_id

                 ORDER BY s.gmt_modified DESC, s.id) t1

         WHERE ROWNUM <= 30)

 WHERE rnum >= 20

 

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

| Id  | Operation                        |  Name              | Rows  | Bytes |TempSpc| Cost  |

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

|   0 | SELECT STATEMENT                 |                    |    30 |   810 |       | 22193 |

|*  1 |  VIEW                            |                    |    30 |   810 |       | 22193 |

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

|   3 |    VIEW                          |                    |  2196K|    29M|       | 22193 |

|*  4 |     SORT ORDER BY STOPKEY        |                    |  2196K|   113M|   286M| 22193 |

|*  5 |      HASH JOIN                   |                    |  2196K|   113M|    83M|  2415 |

|   6 |       TABLE ACCESS BY INDEX ROWID| SAMPLE_DRAFT       |  2196K|    58M|       |   826 |

|   7 |        INDEX FULL SCAN           | SAMPLE_DRAFT_GMID  |  2196K|       |       |    26 |

|*  8 |       TABLE ACCESS FULL          | COMPANY_DRAFT      | 18783 |   476K|       |   243 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("from$_subquery$_001"."RNUM">=20)

   2 - filter(ROWNUM<=30)

   4 - filter(ROWNUM<=30)

   5 - access("S"."COMPANY_ID"="C"."ID")

   8 - filter("C"."IS_VALIDATE"='enabled' OR "C"."IS_VALIDATE"='disabled' AND

              "C"."CHARGE_TYPE"='tbpaid')

 

Statistics

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

          0  recursive calls

          0  db block gets

    1259883  consistent gets

      11687  physical reads

          0  redo size

        926  bytes sent via SQL*Net to client

        504  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

 

 

 

 

 

 

 

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2018-09-17

  • 博文量
    18
  • 访问量
    17089