1.定义
The purpose of join elimination is to remove redundant joins—in other words, to completely avoid executing a join even if a SQL statement explicitly calls for it. The key information used by the query optimizer to decide whether it’s sensible to implement this query transformation is the availability of a foreign key that‘s either enforced or marked RELY. In addition, as of version 11.2, self-joins based on the primary key are also considered. This heuristic-based query transformation is especially useful when views containing joins are used. Note, however, that join elimination can also be applied to SQL statements without views.简单说就是消除不必要的连接,详细使用规则见分类。
2.参数
KSPPINM KSPPSTVL KSPPDESC---------------------------------------- ---------- ------------------------------------------------------------
_optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
hint:eliminate_join/no_eliminate_join
3.分类
1)主外键消除连接操作
- 关联条件为主外键字段
- 查询列只有子表
2)自连接消除连接操作
- 连接条件为主键或唯一索引字段
3)外连接消除连接操作
- 查询列所有字段都来自外连接驱动表
- 连接字段为被消除表上的主键或者唯一索引字段
4)连接位图索引消除连接操作
- 连接条件和连接位图索引的连接条件一致
- 查询列只有建立连接位图索引的表的字段
- 谓词条件有连接位图索引字段
4.测试
这里简单的测试第3种分类:外连接消除连接操作
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14243 | 1237K| 52 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| LXY | 14243 | 1237K| 52 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$FFB75F5A / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$FFB75F5A" "T"@"SEL$1")
OUTLINE(@"SEL$1")
ELIMINATE_JOIN(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$FFB75F5A")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
可以看到查询块SEL$1的T1表被消除了,从执行计划也没有看到T1的相关对象出现,可以从10053中看到转换过程
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$FFB75F5A nbfros=1 flg=0
fro(0): flg=0 objn=14727 hint_alias="T"@"SEL$1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME","T"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T"."OBJECT_ID" "OBJECT_ID","T"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T"."OBJECT_TYPE" "OBJECT_TYPE","T"."CREATED" "CREATED","T"."LAST_DDL_TIME" "LAST_DDL_TIME","T"."TIMESTAMP" "TIMESTAMP","T"."STATUS" "STATUS","T"."TEMPORARY" "TEMPORARY","T"."GENERATED" "GENERATED","T"."SECONDARY" "SECONDARY","T"."NAMESPACE" "NAMESPACE","T"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T"
Query block SEL$FFB75F5A (#0) simplified
CVM: Considering view merge in query block SEL$FFB75F5A (#0)
OJE: Begin: find best directive for query block SEL$FFB75F5A (#0)
OJE: Considering outer-join elimination on query block SEL$FFB75F5A (#0) --这里明确指出了是属于outer-join elimination
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$FFB75F5A (#0)
query block SEL$1 transformed to SEL$FFB75F5A (#0) --查询块从SEL$1转换成了 SEL$FFB75F5A
sql文本:
- create unique index i_1 on lxy_1(object_id);
-
- select t.* from lxy t,lxy_1 t1 where t.object_id=t1.object_id(+);
执行计划:
Plan hash value: 1583004392--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14243 | 1237K| 52 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| LXY | 14243 | 1237K| 52 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$FFB75F5A / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$FFB75F5A" "T"@"SEL$1")
OUTLINE(@"SEL$1")
ELIMINATE_JOIN(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$FFB75F5A")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
可以看到查询块SEL$1的T1表被消除了,从执行计划也没有看到T1的相关对象出现,可以从10053中看到转换过程
10053:
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
join elimination之前:
Query block (0x89b1dde0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME","T"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T"."OBJECT_ID" "OBJECT_ID","T"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T"."OBJECT_TYPE" "OBJECT_TYPE","T"."CREATED" "CREATED","T"."LAST_DDL_TIME" "LAST_DDL_TIME","T"."TIMESTAMP" "TIMESTAMP","T"."STATUS" "STATUS","T"."TEMPORARY" "TEMPORARY","T"."GENERATED" "GENERATED","T"."SECONDARY" "SECONDARY","T"."NAMESPACE" "NAMESPACE","T"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T","SYS"."LXY_1" "T1" WHERE "T"."OBJECT_ID"="T1"."OBJECT_ID"(+)
表移除:
JE: eliminate table: LXY_1 (T1)
Registered qb: SEL$FFB75F5A 0x89b1dde0 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T1"@"SEL$1")
新查询块注册:
---------------------*************************
Join Elimination (JE)
*************************
join elimination之前:
Query block (0x89b1dde0) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME","T"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T"."OBJECT_ID" "OBJECT_ID","T"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T"."OBJECT_TYPE" "OBJECT_TYPE","T"."CREATED" "CREATED","T"."LAST_DDL_TIME" "LAST_DDL_TIME","T"."TIMESTAMP" "TIMESTAMP","T"."STATUS" "STATUS","T"."TEMPORARY" "TEMPORARY","T"."GENERATED" "GENERATED","T"."SECONDARY" "SECONDARY","T"."NAMESPACE" "NAMESPACE","T"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T","SYS"."LXY_1" "T1" WHERE "T"."OBJECT_ID"="T1"."OBJECT_ID"(+)
表移除:
JE: eliminate table: LXY_1 (T1)
Registered qb: SEL$FFB75F5A 0x89b1dde0 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T1"@"SEL$1")
新查询块注册:
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$FFB75F5A nbfros=1 flg=0
fro(0): flg=0 objn=14727 hint_alias="T"@"SEL$1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME","T"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T"."OBJECT_ID" "OBJECT_ID","T"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T"."OBJECT_TYPE" "OBJECT_TYPE","T"."CREATED" "CREATED","T"."LAST_DDL_TIME" "LAST_DDL_TIME","T"."TIMESTAMP" "TIMESTAMP","T"."STATUS" "STATUS","T"."TEMPORARY" "TEMPORARY","T"."GENERATED" "GENERATED","T"."SECONDARY" "SECONDARY","T"."NAMESPACE" "NAMESPACE","T"."EDITION_NAME" "EDITION_NAME" FROM "SYS"."LXY" "T"
Query block SEL$FFB75F5A (#0) simplified
CVM: Considering view merge in query block SEL$FFB75F5A (#0)
OJE: Begin: find best directive for query block SEL$FFB75F5A (#0)
OJE: Considering outer-join elimination on query block SEL$FFB75F5A (#0) --这里明确指出了是属于outer-join elimination
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$FFB75F5A (#0)
query block SEL$1 transformed to SEL$FFB75F5A (#0) --查询块从SEL$1转换成了 SEL$FFB75F5A
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31347199/viewspace-2122228/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31347199/viewspace-2122228/