13:34:45 SQL> select /*+ ordered USE_MERGE(a) */

13:34:48   2   a.pk


13:34:48   3    from test_2m b, test_2m a


13:34:48   4   where a.pk = b.pk;




已选择20000行。





已用时间:  00: 00: 00.07





Execution Plan

----------------------------------------------------------


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=113 Card=20000 Bytes


          =280000)





   1    0   MERGE JOIN (Cost=113 Card=20000 Bytes=280000)


   2    1     INDEX (FULL SCAN) OF 'PK_TEST_2M' (UNIQUE) (Cost=50 Card


          =20000 Bytes=140000)





   3    1     SORT (JOIN) (Cost=63 Card=20000 Bytes=140000)


   4    3       INDEX (FAST FULL SCAN) OF 'PK_TEST_2M' (UNIQUE) (Cost=


          6 Card=20000 Bytes=140000)
















Statistics

----------------------------------------------------------


          0  recursive calls


          0  db block gets


       1434  consistent gets


          0  physical reads


          0  redo size


     336647  bytes sent via SQL*Net to client


      15166  bytes received via SQL*Net from client


       1335  SQL*Net roundtrips to/from client


          1  sorts (memory)


          0  sorts (disk)


      20000  rows processed





13:34:50 SQL>

13:35:04 SQL>

13:35:04 SQL>

13:35:04 SQL> select /*+ ordered USE_MERGE(a) index(a) */

13:35:05   2   a.pk


13:35:05   3    from test_2m b, test_2m a


13:35:05   4   where a.pk = b.pk;




已选择20000行。





已用时间:  00: 00: 00.08





Execution Plan

----------------------------------------------------------


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=157 Card=20000 Bytes


          =280000)





   1    0   MERGE JOIN (Cost=157 Card=20000 Bytes=280000)


   2    1     INDEX (FULL SCAN) OF 'PK_TEST_2M' (UNIQUE) (Cost=50 Card


          =20000 Bytes=140000)





   3    1     SORT (JOIN) (Cost=107 Card=20000 Bytes=140000)


   4    3       INDEX (FULL SCAN) OF 'PK_TEST_2M' (UNIQUE) (Cost=50 Ca


          rd=20000 Bytes=140000)
















Statistics

----------------------------------------------------------


          0  recursive calls


          0  db block gets


       1430  consistent gets


          0  physical reads


          0  redo size


     336647  bytes sent via SQL*Net to client


      15166  bytes received via SQL*Net from client


       1335  SQL*Net roundtrips to/from client


          1  sorts (memory)


          0  sorts (disk)


      20000  rows processed



*** SESSION ID<img src="images/smilies/2.gif" smilieid="203" border="0" alt="" />7.15) 2008-10-15 14:49:50.968







---- Sort Statistics ------------------------------

Input records                             20000


Output records                            20000

/*非必要的排序，可以看到比对次数为n-1*/

Total number of comparisons performed     19999


  Comparisons performed by in-memory sort 19999


Total amount of memory used               450560




*** 2008-10-15 14:57:41.234




---- Sort Statistics ------------------------------

Input records                             20000


Output records                            20000

/*必要的排序情况下的比对次数*/

Total number of comparisons performed     284517


  Comparisons performed by in-memory sort 284517


Total amount of memory used             &n

从trace 的结果看，我的说法是错误的，楼主说的对，b有序的情况下仍然会做sort，只是sort的成本相对比较低

有兴趣可以trace 看看

ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';