oracle分页查询不用order by排序速度反而变慢问题分析总结

--sql-1,不使用order by排序再分页,执行300多秒才出来：
select *
  from (select t.*, rownum rn
          from (select a.RFID,
                       decode(e.type, '112', e.name, sh.name) as name,
                       to_char(d.DEATH_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       to_char(d.DEATH_DEAL_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_TAG,
                       d.DEATH_TYPE,
                       to_char(d.DEATH_REG_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_RESULT,
                       d.DEATH_REASON,
                       d.death_change,
                       d.death_method,
                       d.id /* ,
                            rownum as ro    */
                  from tbl_Death       d,
                       tbl_animal_info a,
                       tbl_Enterprise  e,
                       tbl_Enterprise  sh,
                       tbl_Region      r
                 where d.ANIMAL_ID = a.id
                   and a.ENTERPRISE_ID = e.id(+)
                   and a.owner_name = to_char(sh.id(+))
                   and e.REGION_ID = r.id
                   and r.code like '331082%'
                --order by d.id desc   
                ) t
         where rownum <= 20) re
 where re.rn >= 1

[color=darkblue]
sql-1第一次执行了300多秒才出来，第二次17秒，第三次12秒。
sql-1执行计划：（可以看到cost消耗很小，cardinality基数很大）
[/color]
[img]http://dl.iteye.com/upload/attachment/0068/4891/1530ec8f-8851-364b-a3c9-0306cdf1a030.jpg[/img]

--sql-2,使用order by排序再分页,1秒左右出来：
select *
  from (select t.*, rownum rn
          from (select a.RFID,
                       decode(e.type, '112', e.name, sh.name) as name,
                       to_char(d.DEATH_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       to_char(d.DEATH_DEAL_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_TAG,
                       d.DEATH_TYPE,
                       to_char(d.DEATH_REG_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_RESULT,
                       d.DEATH_REASON,
                       d.death_change,
                       d.death_method,
                       d.id /* ,
                            rownum as ro    */
                  from tbl_Death       d,
                       tbl_animal_info a,
                       tbl_Enterprise  e,
                       tbl_Enterprise  sh,
                       tbl_Region      r
                 where d.ANIMAL_ID = a.id
                   and a.ENTERPRISE_ID = e.id(+)
                   and a.owner_name = to_char(sh.id(+))
                   and e.REGION_ID = r.id
                   and r.code like '331082%'
                 order by d.id desc) t
         where rownum <= 20) re
 where re.rn >= 1

[color=darkblue]
sql-2第一次执行3.2秒，后面都是0.4秒内就出来了。
sql-2执行计划：（可以看到cost消耗较小，cardinality基数变很小了）
[/color]
[img]http://dl.iteye.com/upload/attachment/0068/5898/a34a0824-e696-30a7-ab56-3ad1a148c6ec.jpg[/img]
[color=red]
以前看执行计划，以为只要看cost消耗小，sql就会执行很快，现在看来cardinality基数的影响也是很大的。
[/color]

--sql-3 是sql-1不分页，内层的sql，执行很快1秒内。
select a.RFID,
                       decode(e.type, '112', e.name, sh.name) as name,
                       to_char(d.DEATH_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       to_char(d.DEATH_DEAL_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_TAG,
                       d.DEATH_TYPE,
                       to_char(d.DEATH_REG_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                       d.DEATH_DEAL_RESULT,
                       d.DEATH_REASON,
                       d.death_change,
                       d.death_method,
                       d.id /* ,
                            rownum as ro    */
                  from tbl_Death       d,
                       tbl_animal_info a,
                       tbl_Enterprise  e,
                       tbl_Enterprise  sh,
                       tbl_Region      r
                 where d.ANIMAL_ID = a.id
                   and a.ENTERPRISE_ID = e.id(+)
                   and a.owner_name = to_char(sh.id(+))
                   and e.REGION_ID = r.id
                   and r.code like '331082%'

[color=darkblue]
sql-3执行速度基本和sql-2一样。
sql-3执行计划：（可以看到cost消耗和cardinality基数，基本和排序了的sql-2在一个数量级）
[/color]
[img]http://dl.iteye.com/upload/attachment/0068/5903/5bd689b3-9dd1-3653-bac1-dca348ac19ae.jpg[/img]
[color=red]
总结分析：不用order by排序而分页，oracle需要耗费更多来记住这个无序的排序，以用以分页。而已经按索引列排序的字段order by后，减少了这个过程。所以使用order by反而加快了实际查询速度。

[/color][color=blue]ps:有高手看到的话希望能提点一下：sql-1第一次执行300多秒，后面接着执行几次变成了10秒多出来，这是oracle的什么缓存机制吗？想知道具体是oracle的什么样的原理机制。这也是线上系统经常遇到的一个问题，第一次点sql执行太久，不出结果，过一会再点就可以了。[/color]