Oracle半连接的连接条件为like导致无法使用nl_sj的嵌套循环半连接
Oracle半连接的连接条件为like导致无法使用nl_sj的嵌套循环半连接
如同标题描述，感觉有点奇怪。
HASH JOIN SEMI JOIN不生效很容易理解，HASH JOIN算法会根据两个表连接字段值生成hash值，这样就丢失了包含关系而无法使用like。
但是NEST LOOP SEMI JOIN无法使用就不太理解为什么了。
数据库版本：11.2.0.4.0
模拟如下：
create table dgd (hsstr varchar2(20),exstr varchar2(20)); create table s (id number,hs_str varchar2(40));
执行如下半连接SQL，并强制使用HINT：nl_sj，连接条件为like无法生效，为等值连接则可以。
SQL1: SQL2: select * from dgd select * from dgd where exists ( select /*+ unnest nl_sj */ 1 where exists ( select /*+ unnest nl_sj */ 1 from s from s where dgd.hsstr like s.hs_str||'%' where dgd.hsstr = s.hs_str ); ); Plan hash value: 1104924466 Plan hash value: 3286862866 ------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 | | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 | |* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 1 | | 1 | NESTED LOOPS SEMI | | 1 | 1 | 0 |00:00:00.01 | 1 | | 2 | TABLE ACCESS FULL| DGD | 1 | 1 | 0 |00:00:00.01 | 1 | | 2 | TABLE ACCESS FULL | DGD | 1 | 1 | 0 |00:00:00.01 | 1 | |* 3 | TABLE ACCESS FULL| S | 0 | 1 | 0 |00:00:00.01 | 0 | |* 3 | VIEW | VW_SQ_1 | 0 | 1 | 0 |00:00:00.01 | 0 | ------------------------------------------------------------------------------------- | 4 | TABLE ACCESS FULL| S | 0 | 1 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter(:B1 LIKE "S"."HS_STR"||'%') 3 - filter("DGD"."HSSTR"="ITEM_1")
SQL1的大纲如下：
select * from table(dbms_xplan.display_cursor('f8znnbcfhjn6w',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DGD@SEL$1 3 - SEL$2 / S@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') OPT_PARAM('_bloom_filter_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing' 'none') OPT_PARAM('_bloom_pruning_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DGD"@"SEL$1") FULL(@"SEL$2" "S"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter(:B1 LIKE "S"."HS_STR"||'%') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "DGD"."HSSTR"[VARCHAR2,20], "DGD"."EXSTR"[VARCHAR2,20] 2 - "DGD"."HSSTR"[VARCHAR2,20], "DGD"."EXSTR"[VARCHAR2,20]
其中“IGNORE_OPTIM_EMBEDDED_HINTS”表示指示 CBO 忽略大多数其他提供的hints。
而被IGNORE_OPTIM_EMBEDDED_HINTS忽略的hint则会有提示类似“U - full(t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS”
上边大纲并没有类似提示信息。
普通的表内关联查询使用like是可以用到NEST LOOP的。
select * from dgd,s where dgd.hsstr like s.hs_str||'%'; Plan hash value: 2389320287 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 | | 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | | 2 | TABLE ACCESS FULL| DGD | 1 | 1 | 0 |00:00:00.01 | 1 | |* 3 | TABLE ACCESS FULL| S | 0 | 1 | 0 |00:00:00.01 | 0 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("DGD"."HSSTR" LIKE "S"."HS_STR"||'%')
暂时没搞懂为什么。
