Use "OR" in SQL with caution
一般来说，关联子查询的效率是比较低下的，因此在写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
注意红色高亮的那行，是用一个OR把一个IN字句连在一起，看看执行计划...
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>
时间降低到0.11S， 同是consistent gets 降低到165! 这个是很显著的提升了！造成这种变化的主要原因就在于执行计划的10, 表LO_USER_GROUP_MANGER和GRANTEE_MEMBER现在是通过HASH JOIN方式执行的，而不是低效率的FILTER操作！
以后如果SQL语句中有这种OR字句的话，要多多留意啊！
--------------------------------------
Regards,
FangwenYu
