使用过order by rowid排序吗

前天协助SA排错时，通过tbsql sql发现了使用oreder by rowid做排序的sql，我很好奇这种写法，为什么业务逻辑会通过rowid来排序来实现，常常这种疑惑浪费了我很多时间。
先确认两个问题：
1.为什么会使用rowid来排序
2.一个普通的分页sql，物理读排在第一，为什么每次需要消耗356个的物理读

Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----
 5,043,897       14,149          356.5    7.7   744.89  53095.33 2800676544
SQL> select/*+ordered use_nl(t1 t2)*/ *
  9    from (select rid, rownum as linenum
 10            from (select rowid as rid
 11                    from auc_table
 12                   where username = 'abc123456789'
 13                     and approve_status in (0, 1, -9)
 14                     and ends > SYSDATE
 15                   order by starts asc,rowid )
 16           where rownum <= 100) t1,
 17         auc_table t2
 18   where t1.linenum >= 1
 19     and t1.rid = t2.rowid;

引江枫的论断：

oracle9204版本有这个问题：因为不稳定的排序，同样值的记录的位置在排序后不是固定的。假设两条记录，1，a和1，b，按1排序，那么在第一页的时候，可能最后一条是 1,a，翻到第二页，本来第一条应该是1,b的但是因为排序不稳定，可能第一条还是1，a，这样1,b就没有显示出来了

通过讨论可以确认，使用rowid是为了保证分页取数据显示正常，以防不稳定的排序导致有些记录漏掉显示，如果我们能保证每次分页都能取到正确的数据，那不就可以去掉order by rowid。
1.统计username所占的索引块数。

select count(*)
  from auc_table
  where username = 'abc123456789'and approve_status in (0, 1, -9)and ends > SYSDATE;
1 row selected.

Execution Plan
--------------------------------------------------------
    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=45)
  0   SORT (AGGREGATE)
  1     INDEX (RANGE SCAN) OF 'IDX_auc_USERNAME' (NON-UNIQUE) (Cost=4 Card=5 Bytes=225)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   5055  consistent gets  --差不多我们可以推断这个用户的索引占用了5050个左右的块。
      0  physical reads
      0  redo size
    493  bytes sent via SQL*Net to client
    656  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1    rows processed

从上面的结果我们可以看出，这个id占用索引块大概在5000个左右，而且走的索引扫描，没有回表。
order by rowid是为了避免不稳定的排序带来分页sql取值丢失的问题,如果我们只根据索引来取数据的话，索引中的数据相对位置是不变的，这样是不需要排序，也就没有排序稳不稳定的问题，我们要做的就是根据分页在索引中从头到尾一直把数据取出来而已，没必要通过rowid大小来保证取数据的正确性。
去掉order by rowid排序，性能会有多大的提高呢，我们来对比一下：
1.使用order by rowid排序，取第一页

SQL> select/*+ordered use_nl(t1 t2)*/ *
  9    from (select rid, rownum as linenum
 10            from (select rowid as rid
 11                    from auc_table
 12                   where username = 'abc123456789'
 13                     and approve_status in (0, 1, -9)
 14                     and ends > SYSDATE
 15                   order by starts asc,rowid )
 16           where rownum <= 100) t1,
 17         auc_table t2
 18   where t1.linenum >= 1
 19     and t1.rid = t2.rowid;

100 rows selected.
Execution Plan
-----------------------------------------------------
     SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=5 Bytes=1875    )
   0   NESTED LOOPS (Cost=23 Card=5 Bytes=1875)
   1     VIEW (Cost=18 Card=5 Bytes=100)
   2       COUNT (STOPKEY)
   3         VIEW (Cost=18 Card=5 Bytes=35)
   4           SORT (ORDER BY STOPKEY) (Cost=18 Card=5 Bytes=290)
   5             INDEX (RANGE SCAN) OF 'IDX_auc_USERNAME' (NON-UNIQUE) (Cost=4 Card=5 Bytes=290)
   1     TABLE ACCESS (BY USER ROWID) OF 'auc_table' (Cost     =1 Card=1 Bytes=355)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   5161  consistent gets =5055（索引块）+100（行块）， 如果加上rowid排序的话，应该是把这个username全部数据都要扫描一遍，在排序
     62  physical reads
      0  redo size
  14132  bytes sent via SQL*Net to client
    722  bytes received via SQL*Net from client
      8  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    100  rows processed

2.去掉order by rowid排序,也是只取第一页:

SQL> select/*+ordered use_nl(t1 t2)*/ *
  9    from (select rid, rownum as linenum
 10            from (select rowid as rid
 11                    from auc_table
 12                   where username = 'abc123456789'
 13                     and approve_status in (0, 1, -9)
 14                     and ends > SYSDATE
 15                   order by starts asc )
 16           where rownum <= 100) t1,
 17         auc_table t2
 18   where t1.linenum >= 1
 19     and t1.rid = t2.rowid;

100 rows selected.
Execution Plan
----------------------------------------------------------
     SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1875)
   0   NESTED LOOPS (Cost=7 Card=5 Bytes=1875)
   1     VIEW (Cost=2 Card=5 Bytes=100)
   2       COUNT (STOPKEY)
   3         VIEW (Cost=2 Card=5 Bytes=35)
   4           INDEX (RANGE SCAN) OF 'IDX_auc_USERNAME'
     (NON-UNIQUE) (Cost=4 Card=5 Bytes=290)
   1     TABLE ACCESS (BY USER ROWID) OF 'auc_table' (Cost   =1 Card=1 Bytes=355)

Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
     926  consistent gets  --应该是只取到前面100条的数据就退出了，实际上这个逻辑还是有点大，我以为只用100多一点就够
      44  physical reads
       0  redo size
   13963  bytes sent via SQL*Net to client
     722  bytes received via SQL*Net from client
       8  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
     100  rows processed

同样的功能，不同的实现方式性能相差还是很大的，对于我们这种业务量及其繁忙的系统，7.7%的物理读，如果能优化下来，也是相当可观的。

--EOF--