带排序的分页查询使用不同的查询计划差异很大，怎么解决呢？

Question

本帖最后由 JUSTHELP 于 2009-06-08 18:01:27 编辑

Answer 1

这里的400w是总记录数，还是符合条件的结果数借vc555的一个帖http://topic.csdn.net/u/20090504/12/8b09a374-c70e-4c7b-9522-3250d8b1a120.html用这里面的方法试试。

Answer 2

400w是总记录数，符合条件的，如果key='123'的有10w的数据。
VC555的帖子看过了，没借鉴的地方，那帖子讨论的都是不带查询条件的，
关键在于加了where之后，如果没有where很快的。

Answer 3

请楼上和楼上的楼上详解：
是不是把orderby得字段和查询字段见联合索引
比如：会有四个查询字段key1，key2，key3，key4，就建下面的索引？CREATE INDEX IDX_key1 ON tableA (key1, id);
CREATE INDEX IDX_key1 ON tableA (key2, id);
CREATE INDEX IDX_key1 ON tableA (key3, id);
CREATE INDEX IDX_key1 ON tableA (key4, id);

Answer 4

lz在分页时具体的where子句是什么样的？
是用where key=还是where time1 >= to_date...把where后的字段加上id都放入索引，索引中字段的顺序要看你where条件是否变化。

Answer 5

where key=还是where time1 >= to_date... 的都有，也有where key =  and time1 >= to_date 一起的。
你的意思是不是：
向下面这样，我试了创建IDX_key1 ，没什么作用。是不是因为我的数据太多了啊，满足key1=，的有55W.
CREATE INDEX IDX_key1 ON tableA (key1, id); 
CREATE INDEX IDX_time1 ON tableA (time1, id); 另外下面这个是什么原因呢？
非时间字段，先查后排序比按索引扫描快 
时间字段，取决于需要排序的数据，需要排序得比较多时，排序的计划慢，需要排序得比较少时，没有明显差异 

Answer 6

一个一个解决。
你先列出当前要优化的其中一个sql。

Answer 7

select   * from
( 
select t.*,rownum rn
from (select /*+rule*/ * from tableA 
where position = '123' 
order by queryid desc) t
where rownum <= 100
)
where rn >= 49字段position 和queryid分别有索引，没有组合索引。
一共400w的记录，满足position = '123'的不管有多少，不加/*+rule*/的话，巨慢无比。
区别在于 加/*+rule*/用position索引，不加用queryid 索引查的。如果用你前面给的如下方法：加/*+rule*/不起作用，始终使用queryid 索引，和上面语句同样的数据很慢。如果没有where  position1='123'，就快的很。
  select /*+ ordered use_nl(t, tableA) */ tableA.* from 
 (
     select rid from (
        select rownum rn, rid from (
          select /*+RULE*/ rowid rid from historyalarm where  position1='123' order by queryid desc
        ) where rownum <= 1000
     )where rn >=500
 ) t, tableA 
 where t.rid = tableA.rowid;

Answer 8

  上面第2个写错了，改一下
加/*+rule*/不起作用，始终使用queryid 索引，和上面语句同样的数据很慢。如果没有where  position1='123'，就快的很。 select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from 
 (
     select rid from (
        select rownum rn, rid from (
          select /*+RULE*/ rowid rid from historyalarm where  position='123' order by queryid desc
        ) where rownum <= 1000
     )where rn >=500
 ) t, historyalarm 
 where t.rid = historyalarm.rowid;

Answer 9

select  * from 
( 
select t.*,rownum rn 
from (select /*+rule*/ * from tableA 
where position = '123' 
order by queryid desc) t 
where rownum <= 100 
) 
where rn >= 49 针对上面这个查询，建索引
create index ind_1 on tableA(position,queryid)建了后先收集统计信息，再执行上面的sql，把执行计划也贴下。

Answer 10

SQL> set autot traceonly statistics
SQL> select   * from
  2  (
  3  select t.*,rownum rn
  4  from (select * from historyalarm
  5  where position1 = '123'
  6  order by queryid desc) t
  7  where rownum <= 100
  8  )
  9  where rn >= 49;未选定行
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       3075  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processedSQL> select   * from
  2  (
  3  select t.*,rownum rn
  4  from (select * from historyalarm
  5  where position1 = '5011001'
  6  order by queryid desc) t
  7  where rownum <= 100
  8  )
  9  where rn >= 49;已选择52行。
Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
       3279  consistent gets
         55  physical reads
          0  redo size
       8818  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         52  rows processedSQL>现在不加/*+rule*/也快了，但是他没使用我新创建的联合索引，使用的是position1这个字段的索引SELECT STATEMENT, GOAL = ALL_ROWS 10942 100 669600
 VIEW CAFFM 10942 100 669600
  COUNT STOPKEY
   VIEW CAFFM 10942 64081 428253323
    SORT ORDER BY STOPKEY 10942 64081 15635764
     TABLE ACCESS BY INDEX ROWID CAFFM HISTORYALARM 3279 64081 15635764
      INDEX RANGE SCAN CAFFM IDX_HISTORYALARM$03 989 64081

Answer 11

SQL> select  * from 
  2  ( 
  3  select t.*,rownum rn 
  4  from (select * from historyalarm 
  5  where position1 = '5011001' 
  6  order by queryid desc) t 
  7  where rownum <= 100 
  8  ) 
  9  where rn >= 49; 快了吗？但是看这个sql的Statistics很不好啊。还有disk sort.你没有收集统计信息吧？包括索引的。
你加hint用联合索引，或者把单独的那个索引删了。

Answer 12

收集了统计信息的，没有贴出来
exec dbms_stats.gather_table_stats('fm','historyalarm',cascade=>true)
就是执行了这句之后，才没有加/*+rule*/,变快的把之前position1 drop了之后，重新执行了上面那句收集统计信息的，在执行下面的，Statistics还是不好，但是查询在1-2秒就返回了
SQL> select   * from
  2  (
  3  select t.*,rownum rn
  4  from (select * from historyalarm
  5  where position1 = '5011001'
  6  order by queryid desc) t
  7  where rownum <= 100
  8  )
  9  where rn >= 50;已选择51行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11043 Card=100 Bytes
          =669600)   1    0   VIEW (Cost=11043 Card=100 Bytes=669600)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=11043 Card=64081 Bytes=428253323)
   4    3         SORT (ORDER BY STOPKEY) (Cost=11043 Card=64081 Bytes
          =15635764)   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'HISTORYALARM' (C
          ost=3380 Card=64081 Bytes=15635764)   6    5             INDEX (RANGE SCAN) OF 'IDX_HISTORYALARM_TEST1' (
          NON-UNIQUE) (Cost=1090 Card=64081)Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
       3380  consistent gets
         55  physical reads
          0  redo size
       8721  bytes sent via SQL*Net to client
        536  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         51  rows processed

Answer 13

这样创建索引之后，用ROWID的方法也起效了。
SQL> select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from
  2   (
  3       select rid from (
  4          select rownum rn, rid from (
  5            select  rowid rid from historyalarm where  position1='123' order by queryid desc
  6          ) where rownum <= 1000
  7       )where rn >=500
  8   ) t, historyalarm
  9   where t.rid = historyalarm.rowid;未选定行已用时间:  00: 00: 00.01Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5069 Card=1000 Bytes
          =264000)   1    0   NESTED LOOPS (Cost=5069 Card=1000 Bytes=264000)
   2    1     VIEW (Cost=4069 Card=1000 Bytes=20000)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=4069 Card=64081 Bytes=448567)
   5    4           SORT (ORDER BY STOPKEY) (Cost=4069 Card=64081 Byte
          s=7433396)   6    5             INDEX (RANGE SCAN) OF 'IDX_HISTORYALARM_TEST1' (
          NON-UNIQUE) (Cost=1090 Card=64081 Bytes=7433396)   7    1     TABLE ACCESS (BY USER ROWID) OF 'HISTORYALARM' (Cost=1 C
          ard=1 Bytes=244)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          3  physical reads
          0  redo size
       3025  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processedSQL> select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from
  2   (
  3       select rid from (
  4          select rownum rn, rid from (
  5            select  rowid rid from historyalarm where  position1='123' order by queryid desc
  6          ) where rownum <= 1000
  7       )where rn >=500
  8   ) t, historyalarm
  9   where t.rid = historyalarm.rowiD;未选定行已用时间:  00: 00: 00.01Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5069 Card=1000 Bytes
          =264000)   1    0   NESTED LOOPS (Cost=5069 Card=1000 Bytes=264000)
   2    1     VIEW (Cost=4069 Card=1000 Bytes=20000)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=4069 Card=64081 Bytes=448567)
   5    4           SORT (ORDER BY STOPKEY) (Cost=4069 Card=64081 Byte
          s=7433396)   6    5             INDEX (RANGE SCAN) OF 'IDX_HISTORYALARM_TEST1' (
          NON-UNIQUE) (Cost=1090 Card=64081 Bytes=7433396)   7    1     TABLE ACCESS (BY USER ROWID) OF 'HISTORYALARM' (Cost=1 C
          ard=1 Bytes=244)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       3025  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processedSQL>   select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from
  2   (
  3       select rid from (
  4          select rownum rn, rid from (
  5            select  rowid rid from historyalarm where  position1='5011001' order by que
  6          ) where rownum <= 1000
  7       )where rn >=500
  8   ) t, historyalarm
  9   where t.rid = historyalarm.rowid;已选择501行。已用时间:  00: 00: 00.02Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5069 Card=1000 Bytes
          =264000)   1    0   NESTED LOOPS (Cost=5069 Card=1000 Bytes=264000)
   2    1     VIEW (Cost=4069 Card=1000 Bytes=20000)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=4069 Card=64081 Bytes=448567)
   5    4           SORT (ORDER BY STOPKEY) (Cost=4069 Card=64081 Byte
          s=7433396)   6    5             INDEX (RANGE SCAN) OF 'IDX_HISTORYALARM_TEST1' (
          NON-UNIQUE) (Cost=1090 Card=64081 Bytes=7433396)   7    1     TABLE ACCESS (BY USER ROWID) OF 'HISTORYALARM' (Cost=1 C
          ard=1 Bytes=244)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1591  consistent gets
          0  physical reads
          0  redo size
      53686  bytes sent via SQL*Net to client
        866  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        501  rows processedSQL>   select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from
  2   (
  3       select rid from (
  4          select rownum rn, rid from (
  5            select  rowid rid from historyalarm where  position1='5011001' order by que
  6          ) where rownum <= 100000
  7       )where rn >=90500
  8   ) t, historyalarm
  9   where t.rid = historyalarm.rowid;未选定行已用时间:  00: 00: 00.07Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=68150 Card=64081 Byt
          es=16917384)   1    0   NESTED LOOPS (Cost=68150 Card=64081 Bytes=16917384)
   2    1     VIEW (Cost=4069 Card=64081 Bytes=1281620)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=4069 Card=64081 Bytes=448567)
   5    4           SORT (ORDER BY STOPKEY) (Cost=4069 Card=64081 Byte
          s=7433396)   6    5             INDEX (RANGE SCAN) OF 'IDX_HISTORYALARM_TEST1' (
          NON-UNIQUE) (Cost=1090 Card=64081 Bytes=7433396)   7    1     TABLE ACCESS (BY USER ROWID) OF 'HISTORYALARM' (Cost=1 C
          ard=1 Bytes=244)Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
       1090  consistent gets
        445  physical reads
          0  redo size
       3025  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          0  rows processedSQL>

Answer 14

速度快了就好。不过看lz的执行计划，应该使用的是oracle 9i。
感觉9i不像10g那样，好像不自动走index range scan descending，而是先走index range scan,再来个sort.
所以LZ如果把你的索引改成降序索引，应该还能减少一个sort和逻辑读。

Answer 15

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 6月 10 11:01:53 2009
你的意思是不是这样：
还是尽量避免排序，
我先前的测试，如果满足where条件数据很小，先走index range scan,再来个sort. 比不排序的快，这时候sort消耗会小些，
主要还是因为，索引不合适。建合适的索引的话，即使满足where条件数据很小，直接index range scan descending也比排序的快。我去看看能否升级到9205，和加上降序索引试下。以前找过，window的好像没有9205的升级包。还有对于where A='123' or B='123' order by queryid desc的情况，该怎么建索引.
我试了2种索引方式
CREATE INDEX IDX_HISTORYALARM_position ON HISTORYALARM (A, B, queryid);
或
CREATE INDEX IDX_HISTORYALARM_A ON HISTORYALARM (A, queryid);
CREATE INDEX IDX_HISTORYALARM_B ON HISTORYALARM (B, queryid);
都没用或者尽量避免这种查询。

Answer 16

CREATE INDEX IDX_HISTORYALARM_position2 ON HISTORYALARM (position2, queryid DESC);
之前的索引，也留着的
CREATE UNIQUE INDEX IDX_HISTORYALARM$01 ON HISTORYALARM (QUERYID);
分析索引之后
你推荐的方法
SQL> select /*+ ordered use_nl(t,historyalarm) */ historyalarm.* from
  2   (
  3       select rid from (
  4          select rownum rn, rid from (
  5            select  rowid rid from historyalarm where  position2='5011001'  order by queryid desc
  6          ) where rownum <= 10000
  7       )where rn >9500
  8   ) t, historyalarm
  9   where t.rid = historyalarm.rowid;未选定行已用时间:  00: 00: 01.00Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30144 Card=10000 Byt
          es=2750000)   1    0   NESTED LOOPS (Cost=30144 Card=10000 Bytes=2750000)
   2    1     VIEW (Cost=20144 Card=10000 Bytes=200000)
   3    2       COUNT (STOPKEY)
   4    3         VIEW (Cost=20144 Card=1025296 Bytes=7177072)
   5    4           SORT (ORDER BY STOPKEY) (Cost=20144 Card=1025296 B
          ytes=24607104)   6    5             INDEX (FAST FULL SCAN) OF 'IDX_HISTORYALARM_POSI
          TION2' (NON-UNIQUE) (Cost=401 Card=1025296 Bytes=24607104)   7    1     TABLE ACCESS (BY USER ROWID) OF 'HISTORYALARM' (Cost=1 C
          ard=1 Bytes=255)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4178  consistent gets
       4165  physical reads
          0  redo size
       3025  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed原来的方法
SQL> select   * from
  2  (
  3  select t.*,rownum rn
  4  from (select * from historyalarm
  5  where position2 = '5011001'
  6  order by queryid desc) t
  7  where rownum <= 1000
  8  )
  9  where rn > 500;未选定行已用时间:  00: 01: 09.02Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39482 Card=1000 Byte
          s=6696000)   1    0   VIEW (Cost=39482 Card=1000 Bytes=6696000)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=39482 Card=1025296 Bytes=6852053168)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'HISTORYALARM' (Cos
          t=39482 Card=1025296 Bytes=261450480)   5    4           INDEX (FULL SCAN DESCENDING) OF 'IDX_HISTORYALARM$
          01' (UNIQUE) (Cost=2858 Card=1025296)Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39482  consistent gets
      21903  physical reads
          0  redo size
       3075  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
都是where position2 = '5011001' order by queryid desc
查询计划不一样。是不是因为后者是select * 阿