什么是半连接？
类似如下的SQL。
SELECT *
FROM T1 -- 主SQL
WHERE T1.B = :1
AND T1.A IN ( SELECT T2.A
FROM T2 -- 子SQL
WHERE T2.B = :2
)
或
SELECT *
FROM T1 -- 主SQL
WHERE T1.B = :1
AND EXISTS (SELECT *
FROM T2 -- 子SQL
WHERE T2.A = T1.A
AND T2.B = :2
)
半连接的执行方式主要分为如下；
1. Filter 方式
2. Join 方式
根据选择方式的不同，SQL的性能会有很大的差异。
所以，理解半连接的执行方式，选择合理的执行方式将会使半连接运行得更有效率。
1.FILETR 执行方式
Filter方式是，主SQL有多少条记录，子SQL就扫描多少次的执行方式。
即，主SQL的结果集每一行根据连接条件与子SQL连接，如果返回TRUE，则，子SQL不会再继续扫描，输出主SQL的数据。
如果，主SQL的结果集有100万条记录，那么子SQL最多会扫描100万次。但是，如果子SQL的连接条件没有恰当的索引，那么会怎样？
可以想象到子SQL会反复100万次的 Full Table Scan。 这样的SQL不仅是慢，而且会影响整个DB的性能。
反之，如果主SQL的结果集只有2条记录，选择的是Filter方式的话，子SQL最多会扫描2次，而且子SQL的连接条件有 Unique Index 存在，
那执行效率肯定是非常非常有效率的。
那么，主SQL的结果集记录数很多，而且选择了 Filter 方式，性能是不是肯定会不好？
结论是，也不一定。 如果主SQL的结果集记录数很多，但是子SQL的连接字段的值都相同，那么子SQL只会扫描一次，也就不出现性能问题。
这个是 Oracle 内部对 Filter 方式处理的时候，对 Input 值 Cache 保存 ,如果是相同的值，即 Cache里有的值，就直接从 Cache里取值。
但是，一般情况来说子SQL的连接字段的值相同情况很少，所以，一般大表的 Filter 方式性能不好的情况居多。
也就是说，如果选择的是 Filter 方式，需要确认子SQL的连接字段是否有重复值情况，来进一步判断对性能是否有影响。
对于上面说的 Filter 方式，下面看几个例子。
测试用脚本准备：
建测试表：
CREATE TABLE T1 AS
SELECT * FROM ALL_OBJECTS ;
CREATE TABLE T2 AS
SELECT * FROM ALL_OBJECTS ;
收集统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYSTEM',tabname => 'T1',cascade => TRUE,estimate_percent => 100);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYSTEM',tabname => 'T2',cascade => TRUE,estimate_percent => 100);
建索引：
CREATE INDEX IDX_T2_01 ON T2(OBJECT_ID) ;
CREATE INDEX IDX_T2_02 ON T2(OBJECT_TYPE) ;
例子1： 主SQL的 SQL 输出结果集很多，连接字段的值 Unique 的情况。
SELECT T1.OBJECT_ID
,T1.OBJECT_NAME
,T1.OBJECT_TYPE
FROM T1
WHERE T1.OBJECT_ID BETWEEN 10000 AND 60000
AND EXISTS ( SELECT /*+ NO_UNNEST*/ 1
FROM T2
WHERE T2.OBJECT_ID = T1.OBJECT_ID);
Plan hash value: 1607081204
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 48955 |00:00:00.09 | 53858 |
|* 1 | FILTER | | 1 | | 48955 |00:00:00.09 | 53858 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 47924 | 48955 |00:00:00.02 | 4242 |
|* 3 | INDEX RANGE SCAN | IDX_T2_01 | 48955 | 1 | 48955 |00:00:00.04 | 49616 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter(("T1"."OBJECT_ID"<=60000 AND "T1"."OBJECT_ID">=10000))
3 - access("T2"."OBJECT_ID"=:B1)
从上面的执行计划可以看到，
首先，Id=1 的行里 Operation 是 FILTER ,可以肯定执行计划选择了 Filter 方式。
其实，Id=2 的 TABLE ACCESS FULL 是最先执行的，并且根据条件 "T1"."OBJECT_ID"<=60000 AND "T1"."OBJECT_ID">=10000 进行了过滤。
过滤后，实际结果集（A-Rows）是 48955 行。
之后，为了得到最终结果集与 T2 进行半连接，连接条件是 "T2"."OBJECT_ID"=:B1 。
这个时候根据 T1 表的结果集传过来的值，T2 表利用索引 IDX_T2_01 反复执行（Starts）了 48955 次，最终返回 48955 条记录。
为了得到 48955 条记录，因为选择了 Filter 方式，T2 表反复执行了 48955 次。
结果，SQL 的总 Buffers 量 53858 中，大部分是在 T2 的反复读取索引中发生 ，发生了 49616 Buffers 。
例子2： 主SQL的 SQL 输出结果集很少，连接字段的值 Unique 的情况。
SELECT T1.OBJECT_ID
,T1.OBJECT_NAME
,T1.OBJECT_TYPE
FROM T1
WHERE T1.OBJECT_ID BETWEEN 10000 AND 10010
AND EXISTS ( SELECT /*+ NO_UNNEST*/ 1
FROM T2
WHERE T2.OBJECT_ID = T1.OBJECT_ID);
Plan hash value: 1607081204
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 1039 |
|* 1 | FILTER | | 1 | | 11 |00:00:00.01 | 1039 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 12 | 11 |00:00:00.01 | 1023 |
|* 3 | INDEX RANGE SCAN | IDX_T2_01 | 11 | 1 | 11 |00:00:00.01 | 16 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter(("T1"."OBJECT_ID"<=10010 AND "T1"."OBJECT_ID">=10000))
3 - access("T2"."OBJECT_ID"=:B1)
从上面的执行计划可以看到，
执行计划也是选择了 Filter 方式。 但是，最先执行的 T1 的结果集（A-Rows）很少，只有 11 条记录。
所以，T2 表也根据索引 IDX_T2_01，只执行（Starts）了 11 次。 SQL 性能是良好的。
SQL 的总 Buffers 1039 中，大部分是在 T1 的 TABLE ACCESS FULL 发生的，发生了 1023 Buffers。
如果，这部分也使用索引，那性能肯定会更好。
例子3： 主SQL的 SQL 输出结果集很多，连接字段的值唯一值少的情况。
SELECT T1.OBJECT_ID
,T1.OBJECT_NAME
,T1.OBJECT_TYPE
FROM T1
WHERE T1.OBJECT_ID BETWEEN 10000 AND 60000
AND EXISTS ( SELECT /*+ NO_UNNEST*/ 1
FROM T2
WHERE T2.OBJECT_TYPE = T1.OBJECT_TYPE);
Plan hash value: 375412890
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 48955 |00:00:00.04 | 4330 |
|* 1 | FILTER | | 1 | | 48955 |00:00:00.04 | 4330 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 47924 | 48955 |00:00:00.03 | 4242 |
|* 3 | INDEX RANGE SCAN | IDX_T2_02 | 44 | 2 | 44 |00:00:00.01 | 88 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter(("T1"."OBJECT_ID"<=60000 AND "T1"."OBJECT_ID">=10000))
3 - access("T2"."OBJECT_TYPE"=:B1)
从上面的执行计划可以看到，执行计划也是选择了 Filter 方式。
最先执行的 T1 的结果集（A-Rows）很多，有 48955 条记录。但是，T2 表根据索引 IDX_T2_02，只执行（Starts）了 44 次。
因为，连接字段（OBJECT_TYPE）的值有很多相同的值，种类有 44种。相同的值，第一次会保存在 Cache里，第二次的时，如果 Cache里有的值，就直接从 Cache里取值，不执行半连接。
所以，虽然主SQL的结果集很多，连接字段的值唯一值少的情况，性能良好。
总结，Filter 方式，
主SQL的 SQL 输出结果集很多，连接字段的值唯一值多的情况，性能不好。
但是，主SQL的 SQL 输出结果集很少，或者，连接字段的值唯一值少的情况，性能良好。
Filter 方式，始终是先执行主SQL，再根据主SQL传过来值，确认后再执行子SQL的方式。
像这样 Filter 方式只固有一种处理方式，所以对多样的SQL情况，不能灵活应对。
以上原因，选择了子查询的 Filter 方式时，性能不好的情况居多。
如果，SQL 的执行计划里看到了 Filter 方式执行的时候，首先要确认，子查询的连接条件是否存在索引。
因为，用 Filter 方式执行，子查询用 Full Table Scan 方式处理的话，会有很严重的性能问题发生。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13696961/viewspace-2123589/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13696961/viewspace-2123589/
扫一扫
662
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
