多表关联分页语句，要利用索引已经排序特性、ROWNUM 的 COUNT STOPKEY 特性以及嵌套循环传值特性来优化。

    现在我们创建另外一个测试表 T_PAGE2。

    1. create table t_page2 as select * from dba_objects;
    1. Table created.

    现有如下分页语句。

    1. select *
    2.   from (select *
    3.           from (select a.owner,
    4.                        a.object_id,
    5.                        a.subobject_name,
    6.                        a.object_name,
    7.                        rownum rn
    8.                   from (select t1.owner,
    9.                                t1.object_id,
    10.                                t1.subobject_name,
    11.                                t2.object_name
    12.                           from t_page t1, t_page2 t2
    13.                          where t1.object_id = t2.object_id
    14.                          order by t2.object_name) a)
    15.          where rownum <= 10)
    16.  where rn >= 1;

    分页语句中排序列是 t_page2 的 object_name，我们需要对其创建一个索引。

    1. create index idx_page2_name on t_page2(object_name,0);
    1. Index created.

    现在强制 t_page2 走刚才创建的索引并且让其作为嵌套循环驱动表，t_page 作为嵌套循环被驱动表，利用 rownum 的 COUNT STOPKEY 特性，扫描到 10 条数据，SQL 就停止。现在我们查看强制走索引，强制走嵌套循环的 A-ROWS 执行计划。

    1. select *   from (select *           from (select
    2. a.owner,a.object_id,a.subobject_name,a.object_name, rownum rn
    3.         from (select /*+ index(t2 idx_page2_name) leading(t2) use_nl(t2,t1)  */
    4. t1.owner,t1.object_id,t1.subobject_name,t2.object_name
    5.          from t_page t1, t_page2 t2      where
    6. t1.object_id = t2.object_id      order by
    7. t2.object_name) a)    where rownum <= 10)  where rn >= 1;
    9. select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
    1. PLAN_TABLE_OUTPUT
    2. -------------------------------------------------------------------------------------
    3. SQL_ID  g0gpgftwrfwzt, child number 0
    4. -------------------------------------
    5. select *   from (select *           from (select
    6. a.owner,a.object_id,a.subobject_name,a.object_name, rownum rn
    7.         from (select /*+ index(t2 idx_page2_name) leading(t2) use_nl(t2,t1)  */
    8. t1.owner,t1.object_id,t1.subobject_name,t2.object_name
    9.          from t_page t1, t_page2 t2      where
    10. t1.object_id = t2.object_id      order by
    11. t2.object_name) a)    where rownum <= 10)  where rn >= 1
    13. Plan hash value: 4182646763
    15. -------------------------------------------------------------------------------------
    16. | Id |Operation                        |Name          |Starts|E-Rows|A-Rows| Buffers 
    17. -------------------------------------------------------------------------------------
    18. |  0 |SELECT STATEMENT                 |              |     1|      |    10|     29 |
    19. |* 1 | VIEW                            |              |     1|    10|    10|     29 |
    20. |* 2 |  COUNT STOPKEY                  |              |     1|      |    10|     29 |
    21. |  3 |   VIEW                          |              |     1| 61800|    10|     29 |
    22. |  4 |    COUNT                        |              |     1|      |    10|     29 |
    23. |  5 |     VIEW                        |              |     1| 61800|    10|     29 |
    24. |  6 |      NESTED LOOPS               |              |     1| 61800|    10|     29 |
    25. |  7 |      TABLE ACCESS BY INDEX ROWID|T_PAGE2       |     1| 66557|    10|     10 |
    26. |  8 |        INDEX FULL SCAN          |IDX_PAGE2_NAME|     1| 66557|    10|      4 |
    27. |  9 |      TABLE ACCESS BY INDEX ROWID|T_PAGE        |    10|     1|    10|     19 |
    28. |*10 |        INDEX RANGE SCAN         |IDX_PAGE      |    10|     1|    10|     13 |
    29. -------------------------------------------------------------------------------------
    31. Predicate Information (identified by operation id):
    32. ---------------------------------------------------
    34.    1 - filter("RN">=1)
    35.    2 - filter(ROWNUM<=10)
    36.   10 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

    从执行计划中我们看到，驱动表走的是排序列的索引，扫描了 10 行数据，传值 10 次给被驱动表，然后 SQL 停止运行，逻辑读一共 29 个，该执行计划是正确的，而且是最佳执行计划。

    大家思考一下，对于上面的分页语句，能否走 HASH 连接？如果 SQL 走了 HASH 连接，这时两个表关联之后得到的结果无法保证是有序的，这就需要关联完成后再进行一次排序（SORT ORDER BY），所以不能走 HASH 连接，同理也不能走排序合并连接。

    为什么多表关联的分页语句必须走嵌套循环呢？这是因为嵌套循环是驱动表传值给被驱动表，如果驱动表返回的数据是有序的，那么关联之后的结果集也是有序的，这样就可以消除 SORT ORDER BY。

    现有如下分页语句（排序列来自两个表）。

    1. select *
    2.   from (select *
    3.           from (select a.owner,
    4.                        a.object_id,
    5.                        a.subobject_name,
    6.                        a.object_name,
    7.                        rownum rn    
    8.                   from (select t1.owner,
    9.                                t1.object_id,
    10.                                t1.subobject_name,
    11.                                t2.object_name
    12.                           from t_page t1, t_page2 t2
    13.                          where t1.object_id = t2.object_id
    14.                          order by t2.object_name ,t1.subobject_name) a)
    15.          where rownum <= 10)
    16.  where rn >= 1;

    因为以上分页语句排序列来自多个表，这就需要等两表关联完之后再进行排序，这样无法消除 SORT ORDER BY，所以以上 SQL 语句无法优化，两表之间也只能走 HASH 连接。如果想优化上面分页语句，我们可以与业务沟通，去掉一个表的排序列，这样就不需要等两表关联完之后再进行排序。

    现有如下分页语句（根据外连接从表排序）。

    1. select *
    2.   from (select *
    3.           from (select a.owner,
    4.                        a.object_id,
    5.                        a.subobject_name,
    6.                        a.object_name,
    7.                        rownum rn
    8.                   from (select t1.owner,
    9.                                t1.object_id,
    10.                                t1.subobject_name,
    11.                                t2.object_name
    12.                           from t_page t1 left join t_page2 t2
    13.                          on t1.object_id = t2.object_id
    14.                          order by t2.object_name) a)
    15.          where rownum <= 10)
    16.  where rn >= 1;

    两表关联如果是外连接，当两表用嵌套循环进行连接的时候，驱动表只能是主表。这里主表是 t1，但是排序列来自 t2，在分页语句中，对哪个表排序，就应该让其作为嵌套循环驱动表。但是这里相互矛盾。所以该分页语句无法优化，t1 与 t2 只能走 HASH 连接。如果想要优化以上分页语句，我们只能让 t1 表中的列作为排序列。

    分页语句中也不能有 distinct、group by、max、min、avg、union、union all 等关键字。因为当分页语句中有这些关键字，我们需要等表关联完或者数据都跑完之后再来分页，这样性能很差。

    最后，我们总结一下多表关联分页优化思路。多表关联分页语句，如果有排序，只能对其中一个表进行排序，让参与排序的表作为嵌套循环的驱动表，并且要控制驱动表返回的数据顺序与排序的顺序一致，其余表的连接列要创建好索引。如果有外连接，我们只能选择主表的列作为排序列，语句中不能有 distinct、group by、max、min、avg、union、union all，执行计划中不能出现 SORT ORDER BY。