1.定义:

The purpose of subquery coalescing is to combine equivalent semi- and anti-join subqueries into a single query block. The main reason for applying this heuristic-based query transformation, which is available as of version 11.2, is to reduce the number of table accesses, and thus to reduce the number of joins.

Coalescing two subqueries of the same type might drastically reduce the number of logical I/O as far as it can eliminate an entire table access. Coalescing two subqueries of different types might pre-empt the CBO from taking advantage of the unnesting transformation. Fortunately if you know how to coalesce two different subqueries you will know how to de-coalesce them to allow the CBO taking advantage of unnesting the subqueries with their main query blocks.

2.参数:

KSPPINM KSPPSTVL KSPPDESC
------------------------------ ---------- ------------------------------------------------------------
_optimizer_coalesce_subqueries TRUE consider coalescing of subqueries optimization

hint:coalesce_sq/no_coalesce_sq

3.测试：

sql文本：

SELECT /*+qb_name(main)*/*
FROM lxy t1
WHERE EXISTS (SELECT /*+qb_name(sub1)*/1 FROM lxy_1 t2 WHERE t2.object_id = t1.object_id AND t2.OWNER='SYS')
OR EXISTS (SELECT /*+qb_name(sub2)*/ 1 FROM lxy_1 t3 WHERE t3.object_id = t1.object_id AND t3.object_type='TABLE');

执行计划：

Plan hash value: 1212017683
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1771 | 176K| 104 (0)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI| | 1771 | 176K| 104 (0)| 00:00:02 |
| 2 | VIEW | VW_SQ_1 | 1771 | 23023 | 52 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LXY_1 | 1771 | 30107 | 52 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | LXY | 14243 | 1237K| 52 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$8BBAAD22
2 - SEL$8CB19D9E / VW_SQ_1@SEL$E8701212
3 - SEL$8CB19D9E / T2@SUB1
4 - SEL$8BBAAD22 / T1@MAIN
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$8CB19D9E" "T2"@"SUB1")
SWAP_JOIN_INPUTS(@"SEL$8BBAAD22" "VW_SQ_1"@"SEL$E8701212")
USE_HASH(@"SEL$8BBAAD22" "VW_SQ_1"@"SEL$E8701212")
LEADING(@"SEL$8BBAAD22" "T1"@"MAIN" "VW_SQ_1"@"SEL$E8701212")
NO_ACCESS(@"SEL$8BBAAD22" "VW_SQ_1"@"SEL$E8701212")
FULL(@"SEL$8BBAAD22" "T1"@"MAIN")
OUTLINE(@"SUB1")
OUTLINE(@"MAIN")
OUTLINE(@"SUB2")
COALESCE_SQ(@"SUB1")
OUTLINE(@"SEL$4B4BED4E")
OUTLINE(@"SEL$E8701212")
COALESCE_SQ(@"SUB2")
OUTLINE(@"SEL$5DD85A06")
UNNEST(@"SEL$5DD85A06")
OUTLINE_LEAF(@"SEL$8BBAAD22")
OUTLINE_LEAF(@"SEL$8CB19D9E")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="T1"."OBJECT_ID")
3 - filter("T2"."OWNER"='SYS' OR "T2"."OBJECT_TYPE"='TABLE')

从outline清楚的看到CBO对子查询进行了合并，谓词条件变成"T2"."OWNER"='SYS' OR "T2"."OBJECT_TYPE"='TABLE'，减少了LXY_1的扫描次数，从10053可以看到具体的转换。

10053：

转换前：

Query before EXDS SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."OWNER" "OWNER","T1"."OBJECT_NAME" "OBJECT_NAME","T1"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T1"."OBJECT_ID" "OBJECT_ID","T1"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."CREATED" "CREATED","T1"."LAST_DDL_TIME" "LAST_DDL_TIME","T1"."TIMESTAMP" "TIMESTAMP","T1"."STATUS" "STATUS","T1"."TEMPORARY" "TEMPORARY","T1"."GENERATED" "GENERATED","T1"."SECONDARY" "SECONDARY","T1"."NAMESPACE" "NAMESPACE","T1"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T1" WHERE EXISTS (SELECT /*+ QB_NAME ("SUB1") */ 1 "1" FROM "SYS"."LXY_1" "T2" WHERE "T2"."OBJECT_ID"="T1"."OBJECT_ID" AND "T2"."OWNER"='SYS') OR EXISTS (SELECT /*+ QB_NAME ("SUB2") */ 1 "1" FROM "SYS"."LXY_1" "T3" WHERE "T3"."OBJECT_ID"="T1"."OBJECT_ID" AND "T3"."OBJECT_TYPE"='TABLE')
Registered qb: SEL$4B4BED4E 0x89ce6750 (SUBQUERY COALESCE SUB1; SUB1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$4B4BED4E nbfros=1 flg=0
fro(0): flg=0 objn=14729 hint_alias="T2"@"SUB1"

Registered qb: SEL$5DD85A06 0x89ce6750 (SUBQUERY COALESCE SEL$4B4BED4E; SUB2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$5DD85A06 nbfros=1 flg=0
fro(0): flg=0 objn=14729 hint_alias="T2"@"SUB1"

转换后：
Query after subquery coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."OWNER" "OWNER","T1"."OBJECT_NAME" "OBJECT_NAME","T1"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T1"."OBJECT_ID" "OBJECT_ID","T1"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."CREATED" "CREATED","T1"."LAST_DDL_TIME" "LAST_DDL_TIME","T1"."TIMESTAMP" "TIMESTAMP","T1"."STATUS" "STATUS","T1"."TEMPORARY" "TEMPORARY","T1"."GENERATED" "GENERATED","T1"."SECONDARY" "SECONDARY","T1"."NAMESPACE" "NAMESPACE","T1"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T1" WHERE EXISTS (SELECT /*+ QB_NAME ("SUB1") */ 1 "1" FROM "SYS"."LXY_1" "T2" WHERE "T2"."OBJECT_ID"="T1"."OBJECT_ID" AND ("T2"."OWNER"='SYS' OR "T2"."OBJECT_TYPE"='TABLE'))

ITPub博客

Subquery Coalescing