在驱动表的谓词条件列上 (T3 表的 n 列) 加上索引
SQL> create index t3_n on t3(n);
Index created.
SQL> select /*+ leading(t3) use_nl(t4) */ * from t3, t4
2 where t3.id = t4.t3_id and t3.n = 1100;
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 89hnfwqakjghg, child number 0
-------------------------------------
select /*+ leading(t3) use_nl(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100
Plan hash value: 2304842513
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 17 | 1 |
| 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 17 | 1 |
| 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 7 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 1 |00:00:00.01 | 4 | 1 |
|* 4 | INDEX RANGE SCAN | T3_N | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
|* 5 | INDEX RANGE SCAN | T4_T3_ID | 1 | 10 | 10 |00:00:00.01 | 3 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | T4 | 10 | 10 | 10 |00:00:00.01 | 10 | 0 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T3"."N"=1100)
5 - access("T3"."ID"="T4"."T3_ID")
在执行计划中可以看到在驱动表上的谓词条件列上加上索引后, buffer 从 29 继续下降到了 17
四. 小结
由此可见, 在 sql 调优时如果遇到表的连接方式是 nested loop：
首先，要确保结果集小的表为驱动表，结果集多的表为被驱动表。这不意味着记录多的表不能作为驱动表, 只要通过谓词条件过滤后得到的结果集比较小，也可以作为驱动表。
其次，在驱动表的谓词条件列以及被驱动表的连接列上加上索引，能够显著的提高执行性能。
最后，如果要查询的列都在索引中，避免回表查询列信息时，又将进一步提高执行性能。