long sort 和 short sort

对这个帖子 http://www.itpub.net/thread-1266765-1-1.html
的SQL做了点测试，顺便发现oracle 10g对排序这个操作还是很有点门道值得我们研究的。

比如对于如下的两个SQL：

SQL1：
SELECT * FROM
(
SELECT a.*,ROWNUM AS rn FROM
(SELECT * FROM t ORDER BY t.c1 ) a
)
WHERE rn = 90;

SQL2：
SELECT * FROM
(
SELECT a.*,ROWNUM AS rn FROM
(SELECT * FROM t WHERE ORDER BY t.c1 )a
WHERE ROWNUM)
WHERE rn>90;

这是典型的分页查询，SQL2是典型的公认的比SQL1性能更好，可以体现在对排序的不同处理方面。

按照oracle 10132 trace里看到，
对SQL1 oracle是采用sort version 2（我们可以说它是long sort)，也是我们一般所认为的排序方式。
对SQL2，oracle采用的是sort version 1 （我们可以说它是short sort），是一种特殊的排序。

其实SQL2的优势就在于，它可以让oracle在排序的时候知道我们最终只对前一百行数据感兴趣，从而可以对排序做一些优化。
而SQL1做不到这一点。

普通的排序，比如(select * from t1 order by c1)这样的语句，一定是把所有的数据都放到内存里进行排序，这对内存要求比较高。

short sort的时候，比如针对SQL2的排序，
oracle只需要在PGA里维护一个包含100个数值的树状结构，
当有新数据进来的时候，新数据将会和原来的100个数据中的最大值作比较(如果是降序排序则会和最小值比较），
如果新数据更大，就直接舍弃新数据，
如果新数据更小，就舍弃原来的100个数据中的最大值，并把新数据插入到内存的树状结构中。

这样就起到了节省内存的作用。当然进一步会节省临时空间。
但是CPU方面很难说哪种方式更优秀，因为我在trace里看到有些metrics普通排序（或者long sort）还是比较好的。

比如Total number of comparisons performed    这个值普通排序更低，我想compare越少当然越节省CPU.

但是Direct Read Statistics的数据 short sort明显优越得多。

大家要看测试例子直接来这个帖子吧，太晚了，最近也好忙，没时间做更系统的测试，以后找时间再测测：
http://www.itpub.net/viewthread. ... p;extra=&page=2

画了张图，用window的画图画的，土了点，大家将就看看吧

[ 本帖最后由 fusnow 于 2010-2-4 11:51 编辑 ]