一般来说，关联子查询的效率是比较低下的，因此在写SQL的时候尽量避免这种写法，要变成表连接的方式(有时候可能需要外连接）。一般来说关联子查询有以下两种方式....
(1) 在SELECT子句中
SELECT t1.col1, (SELECT t2.col1 FROM t2 WHERE t1.col1 = t2.col1) FROM t1;
这种情况可以改成外连接方式...
SELECT t1.col1, t2.col1 FROM t1 LEFT JOIN t2 ON (t1.col1 = t2.col2)
(2) 在 (NOT) EXIST子句中
SELECT t1.col1 FROM t1 WEHRE EXISTS ( SELECT NULL FROM t2 WHERE t1.col1 = t2.col2 )
这种方式下SQL优化器一般会改成semi-join方式执行。因此效率也不会差。
但是，如果你的SQL语句中出现了OR EXISTS，优化器可能就不会把关联子查询非嵌套话了。一般这种情况下，可以把SQL拆开，用UNION (ALL)改成两个SQL语句来做。
因为EXISTS一般是跟关联查询有关，所以这中改法很常见。但是今天碰到了个IN字句，本来以为优化器会对IN字句直接改成JION的方式，但是结果不是这样的。
本来SQL是这样的...
SELECT distinct CONNECT_BY_ROOT grantee_id AS user_id, group_grantee_id AS group_id, LEVEL AS distance FROM grantee_member gm START WITH gm.grantee_id > 0 OR gm.grantee_id IN (SELECT group_id FROM lo_user_group_manager) CONNECT BY PRIOR group_grantee_id = grantee_id
SQL> SELECT distinct 2 CONNECT_BY_ROOT grantee_id AS user_id, 3 group_grantee_id AS group_id, 4 LEVEL AS distance 5 FROM grantee_member gm 6 START WITH gm.grantee_id > 0 7 OR gm.grantee_id IN 8 (SELECT group_id FROM lo_user_group_manager) 9 CONNECT BY PRIOR group_grantee_id = grantee_id; 1208 rows selected. Elapsed: 00:00:00.42 Execution Plan ---------------------------------------------------------- Plan hash value: 576962400 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 | | 1 | HASH UNIQUE | | 11 | 77 | 4 (25)| 00:00:01 | |* 2 | CONNECT BY WITH FILTERING | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| GRANTEE_MEMBER | | | | | |* 4 | FILTER | | | | | | | 5 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 1165 | 4660 | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | U1_LO_USER_GROUP_MGR | 2 | 8 | 1 (0)| 00:00:01 | | 7 | NESTED LOOPS | | | | | | | 8 | CONNECT BY PUMP | | | | | | |* 9 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 15 | 105 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") 4 - filter("GM"."GRANTEE_ID">0 OR EXISTS (SELECT 0 FROM "LO_USER_GROUP_MANAGER" "LO_USER_GROUP_MANAGER" WHERE "GROUP_ID"=:B1)) 6 - access("GROUP_ID"=:B1) 9 - filter("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 48263 consistent gets 0 physical reads 0 redo size 19880 bytes sent via SQL*Net to client 1230 bytes received via SQL*Net from client 82 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1208 rows processed SQL>
时间花了0.42s, consistent gets数 是 48263。
注意到执行计划的4，是个"FILTER"操作，是个关联子查询的FILTER操作--在GROUP_ID上有个bind variable - :B1, 是从6中取到的。说明是从LO_USER_GROUP_MANAGER中没得到一行记录，就进行4中的filter操作一次。很显然是很低效率的，从gets数就可以看得很清楚。
但是如果把OR拆开成用UNION连接的语句的话，情况有什么变化呢?
SQL> SELECT 2 CONNECT_BY_ROOT grantee_id AS user_id, 3 group_grantee_id AS group_id, 4 LEVEL AS distance 5 FROM grantee_member gm 6 START WITH gm.grantee_id > 0 7 --OR gm.grantee_id IN 8 -- (SELECT group_id FROM lo_user_group_manager) 9 CONNECT BY PRIOR group_grantee_id = grantee_id 10 UNION 11 SELECT 12 CONNECT_BY_ROOT grantee_id AS user_id, 13 group_grantee_id AS group_id, 14 LEVEL AS distance 15 FROM grantee_member gm 16 START WITH gm.grantee_id IN 17 (SELECT group_id FROM lo_user_group_manager) 18 CONNECT BY PRIOR group_grantee_id = grantee_id; 1208 rows selected. Elapsed: 00:00:00.11 Execution Plan ---------------------------------------------------------- Plan hash value: 867899047 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 210 | 8 (63)| 00:00:01 | | 1 | SORT UNIQUE | | 30 | 210 | 8 (63)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | CONNECT BY WITH FILTERING | | | | | | |* 4 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 1160 | 8120 | 3 (0)| 00:00:01 | |* 5 | HASH JOIN | | | | | | | 6 | CONNECT BY PUMP | | | | | | | 7 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 15 | 105 | 3 (0)| 00:00:01 | |* 8 | CONNECT BY WITH FILTERING | | | | | | | 9 | TABLE ACCESS BY INDEX ROWID| GRANTEE_MEMBER | | | | | |* 10 | HASH JOIN | | 7 | 56 | 5 (20)| 00:00:01 | | 11 | INDEX FULL SCAN | U1_LO_USER_GROUP_MGR | 15 | 60 | 1 (0)| 00:00:01 | | 12 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 1165 | 4660 | 3 (0)| 00:00:01 | | 13 | NESTED LOOPS | | | | | | | 14 | CONNECT BY PUMP | | | | | | |* 15 | INDEX FAST FULL SCAN | PK_GRANTEE_MEMBER | 15 | 105 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") 4 - filter("GM"."GRANTEE_ID">0) 5 - access("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") 8 - access("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") 10 - access("GM"."GRANTEE_ID"="GROUP_ID") 15 - filter("GRANTEE_ID"=PRIOR "GROUP_GRANTEE_ID") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 165 consistent gets 0 physical reads 0 redo size 18522 bytes sent via SQL*Net to client 1230 bytes received via SQL*Net from client 82 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1208 rows processed SQL>
以后如果SQL语句中有这种OR字句的话，要多多留意啊！