查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT SQL_ID 245z7n1cxaf3m, child number 0 ------------------------------------- SELECT /*+ leading(t1) use_nl(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id Plan hash value: 1967407726 -------------------------------------------------------------------------------- ----- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff ers | -------------------------------------------------------------------------------- ----- | 0 | SELECT STATEMENT | | 1 | | 300 |00:00:00.25 | 29 747 | | 1 | NESTED LOOPS | | 1 | 300 | 300 |00:00:00.25 | 29 747 | | 2 | TABLE ACCESS FULL| T1 | 1 | 300 | 300 |00:00:00.01 | 27 | |* 3 | TABLE ACCESS FULL| T2 | 300 | 1 | 300 |00:00:00.25 | 29 720 | -------------------------------------------------------------------------------- ----- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."ID"="T2"."T1_ID") Note PLAN_TABLE_OUTPUT - dynamic sampling used for this statement (level=2) 已选择24行。Nested sort join中，驱动表被访问0或1次，被驱动表被访问0或者n次,n是驱动表返回的结果集条数
然后同样可以进行hash join、merge join的实践，hash join用/+ leading(t1) use_hash(t2) /
Hash join中驱动表被访问0或者1次，被驱动表也一样
merge sort join中驱动表被访问0或者1次，被驱动表也一样
（2）表连接顺序影响
对于前面的用t1为驱动表的情况，现在换一下顺序，
SQL>SELECT /*+ leading(t2) use_nl(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id; SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT SQL_ID fgw5v7y16yn4m, child number 0 ------------------------------------- SELECT /*+ leading(t2) use_nl(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id Plan hash value: 4016936828 -------------------------------------------------------------------------------- ----- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff ers | -------------------------------------------------------------------------------- ----- | 0 | SELECT STATEMENT | | 1 | | 300 |00:00:00.30 | 70 139 | | 1 | NESTED LOOPS | | 1 | 300 | 300 |00:00:00.30 | 70 139 | | 2 | TABLE ACCESS FULL| T2 | 1 | 9485 | 10000 |00:00:00.01 | 119 | |* 3 | TABLE ACCESS FULL| T1 | 10000 | 1 | 300 |00:00:00.29 | 70 020 | -------------------------------------------------------------------------------- ----- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."ID"="T2"."T1_ID") Note PLAN_TABLE_OUTPUT - dynamic sampling used for this statement (level=2) 已选择24行。可以看出表连接顺序对NL连接是有影响的，同理实验，可以看出对hash join也是有影响的，而merger join不影响
（3）表连接排序
对于这几种表连接，可以用set autotrace on方式查看sorts属性，可以得出只有merge join是有排序的，Nl连接和hash join是无序的
（4）各表连接失效情况
hash join不支持的条件是“>、<、<>、like”的连接方式，merge join不支持的条件是“<>、like”支持“<、>”的情况，而nl连接没有限制，这是几种表连接方法的区别
EXIST一定比IN查询快？
count(列名)一定比count(*)查询快？
