【求助】排序后分页的效率问题

Question

我用的是 oracle 10.2.0.1
我的表定义和索引的建立 如下
注意：  指定 PRIMARY KEY(V_KEY,V_ID)后，oracle会自动创建 其对应的 unique index
CREATE TABLE T_VDATA( 
           V_KEY    varchar2(200) NOT NULL,
           V_ID                number(10)         NOT NULL , 
           V_NAME  varchar2(50) NOT NULL,
           V_STATE  number(1) NOT NULL,
           PRIMARY KEY(V_KEY,V_ID)
    );
    
CREATE UNIQUE INDEX U_INDEX1_T_VDATA ON T_VDATA(V_KEY,V_NAME);CREATE INDEX IDX1_T_VDATA ON T_VDATA(V_KEY);CREATE INDEX IDX2_T_VDATA ON T_VDATA(V_ID);我的分页的sql脚本如下 
SELECT * FROM (
        SELECT ROWNUM RNUM, VT.* 
        FROM (SELECT * FROM T_VDATA ORDER BY V_KEY,V_ID ) VT WHERE ROWNUM<=?  
) TT WHERE TT.RNUM>?第一个参数为： 页长*请求页码
第二个参数为： 页长*（请求页码-1）以上设计逻辑上没有问题。
但是效率上有些问题 。
我的表T_VDATA中有 600多万条数据。我测试用的页长都为 25场景一：
当我请求的当前也为最后一页（或则页码比较靠后）时 ，耗时居然要到30秒。
如果 页码比较靠前，耗时也就2秒。
场景二 ：
当我去掉 sql中的 order by  后，sql语句如下
SELECT * FROM (
        SELECT ROWNUM RNUM, VT.* 
        FROM (SELECT * FROM T_VDATA  ) VT WHERE ROWNUM<=?  
) TT WHERE TT.RNUM>?即使请求的是最后一页，耗时也只要4秒。当然，去掉排序后的结果不是我想要的，这儿只不过是比较一下 。我要问的是 V_KEY,V_ID 我已经做过索引了，为什么order by时效率还是这么的低 ？
我感觉用order by 后，由于查询最后 才限定 TT.RNUM>?，所以 oracle会将 前面的数据都会加载到内存中来 ，
但是为什么不用order by 时好像就没有这个过程（根据耗时猜测的）。
用不用 order by，oracle处理起来有什么区别。

Answer 1

排序如果没有索引，肯定很慢，要全部记录扫描后才能排序看一下解释计划，可能没有用到索引，加上/*+index(...)*/或者where触发一下试试

Answer 2

应该不是索引的问题 ，加上/*+index(...)*/ 显式指定索引，效果一样。
---------------------------

Answer 3

如果只是select索引字段的话很快可以试试select * from T_VDATA where (V_KEY,V_ID) in (select ...)

Answer 4

建立这样的索引看一下.
CREATE INDEX IDX1_T_VDATA ON T_VDATA(V_KEY, V_ID); 

Answer 5

其实如果你使用rowid方式来排序分页的话可能会更加好一点．

Answer 6

你说的没错
如果改成 查询语句时，速度很快 3秒左右。
SELECT V_KEY,V_ID FROM ( 
        SELECT ROWNUM RNUM, VT.V_KEY,VT.V_ID
        FROM (SELECT V_KEY,V_ID FROM T_VDATA ORDER BY V_KEY,V_ID ) VT WHERE ROWNUM <=?  
) TT WHERE TT.RNUM>? 但是再加下面的SQL后，速度还是变慢了，很原来差不多
SELECT * FROM T_VDATA WHERE (V_KEY,V_ID) IN (
SELECT V_KEY,V_ID FROM ( 
        SELECT ROWNUM RNUM, VT.V_KEY,VT.V_ID
        FROM (SELECT V_KEY,V_ID FROM T_VDATA ORDER BY V_KEY,V_ID ) VT WHERE ROWNUM <=?  
) TT WHERE TT.RNUM>? 
)----------------------------------------------

Answer 7

楼主这种情况想要速度快，只有先在unique索引中找到对应的值，然后再从表中查询对应的记录
但是局限性很大，order和where的所有字段必须在索引中，而且必须是unique key
SELECT * FROM T_VDATA WHERE (V_KEY,V_ID) IN ( 
SELECT V_KEY,V_ID FROM ( 
        SELECT ROWNUM RNUM, VT.V_KEY,VT.V_ID 
        FROM (SELECT V_KEY,V_ID FROM T_VDATA ORDER BY V_KEY,V_ID ) VT WHERE ROWNUM <=?  
) TT WHERE TT.RNUM>? 
) 
这句效率很慢的话，只能说解释计划和预期的不符
改成这样试试吧：
with tt as (...)
SELECT * FROM T_VDATA WHERE (V_KEY,V_ID) IN (
  SELECT V_KEY,V_ID FROM tt
)

Answer 8

补充一句，in改成exists可能也比较快

Answer 9

前段时候买了陈吉平（Piner）的书，他的书里正好有对这种分页的优化
按他的思路帮你改一下
SELECT * FROM ( 
        SELECT ROWNUM RNUM, VT.* 
        FROM (SELECT * FROM T_VDATA ORDER BY V_KEY,V_ID ) VT WHERE ROWNUM <=?  
) TT WHERE TT.RNUM>? 
SELECT /*+ordered use_nl(a,T_VDATA )*/
       t.*
  FROM (SELECT rid
          FROM (SELECT ROWNUM rn, rid
                  FROM (SELECT   ROWID rid
                            FROM t_vdata
                        ORDER BY v_key, v_id)
                 WHERE ROWNUM <= 500)
         WHERE rn >= 451) a,
       t_vdata t
 WHERE a.rid = t.ROWID这样改写看执行计划，其实差别不大，但逻辑读减少了，原先随着页码的增长逻辑读也增长，这样改写每页都和第一页差不多不过我没试过

Answer 10

什么意思？就是用hebo的方法阿。只不过他HINT写错了。OPER@TL> create table test as select * from dba_objects;Table created.OPER@TL> create index ind_test on test(object_id);Index created.OPER@TL> exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL procedure successfully completed.OPER@TL> set autot traceonly statistics
OPER@TL> --1到10条
OPER@TL> select /*+ ordered use_nl(t,test)*/ test.*
  2  from (
  3  select rid from (
  4  (select rid,rownum rn from
  5  (select rowid rid from test where object_id is not null
  6  order by object_id)
  7  where rownum<=10))
  8  where rn>=1) t,test
  9  where test.rowid=t.rid;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        13  consistent gets
          0  physical reads
          0  redo size
       1481  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processedOPER@TL> --10001到10010条
OPER@TL> select /*+ ordered use_nl(t,test)*/ test.*
  2  from (
  3  select rid from (
  4  (select rid,rownum rn from
  5  (select rowid rid from test where object_id is not null
  6  order by object_id)
  7  where rownum<=10010))
  8  where rn>=10001) t,test
  9  where test.rowid=t.rid;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        33  consistent gets
          0  physical reads
          0  redo size
     1458  bytes sent via SQL*Net to client
      400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processedOPER@TL> --50001到50010条
OPER@TL> select /*+ ordered use_nl(t,test)*/ test.*
  2  from (
  3  select rid from (
  4  (select rid,rownum rn from
  5  (select rowid rid from test where object_id is not null
  6  order by object_id)
  7  where rownum<=50010))
  8  where rn>=50001) t,test
  9  where test.rowid=t.rid;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      123  consistent gets
          0  physical reads
          0  redo size
    1830  bytes sent via SQL*Net to client
      400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed普通情况下：
OPER@TL>  --1到10条
OPER@TL> select * from (
  2  select rownum rn,x.*
  3  from (select * from test where object_id is not null order by object_id) x
  4  where rownum<=10)
  5  where rn>=1;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1563  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processedOPER@TL> --10001到10010条
OPER@TL> select * from (
  2  select rownum rn,x.*
  3  from (select * from test where object_id is not null order by object_id) x
  4  where rownum<=10010)
  5  where rn>=10001;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       166  consistent gets
          0  physical reads
          0  redo size
      1689  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processedOPER@TL> --50001到50010条
OPER@TL> select * from (
  2  select rownum rn,x.*
  3  from (select * from test where object_id is not null order by object_id) x
  4  where rownum<=10010)
  5  where rn>=10001;10 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1205  consistent gets
          0  physical reads
          0  redo size
    1932  bytes sent via SQL*Net to client
      400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
可以看到第二个方法，consistent gets涨得是非常快。

Answer 11

使用rowid的方法.如果对where条件和order by字段建有索引. 不管查询哪一页的效率都差不多,比较稳定,而且效率比较高. 因为这时候rowid实际上是从索引结构中获得的,索引结构的数据量要比数据表少的多. 而如果没有索引的话,要获取rowid还是比较耗的.