-- Demo case: Limitation of SJC (Set Join Conversion) transformation incl. work-around -- Date: 23-07-2017 -- References: https://blog.dbi-services.com/oracle-121021-set-to-join-conversion/ -- Demo run on Oracle version: 12.2.0.1 -- Setup data create table t1 (a number, b number, c varchar2(2)); create table t2 (a number, b number, c varchar2(2)); insert into t1 values (1,1,'X'); insert into t1 values (1,2,'X'); insert into t1 values (2,1,'X'); insert into t1 values (2,2,'R'); insert into t1 values (3,1,'X'); insert into t2 values (1,1,'X'); insert into t2 values (1,2,'X'); insert into t2 values (2,1,'X'); insert into t2 values (2,2,'X'); commit; -- Run query without SJC (disabled by default) select a,b,c from t1 minus select a,b,c from t2; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 8 (100)| | | | | | 1 | MINUS | | | | | | | | | | 2 | SORT UNIQUE | | 5 | 145 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 3 | TABLE ACCESS FULL| T1 | 5 | 145 | 3 (0)| 00:00:01 | | | | | 4 | SORT UNIQUE | | 4 | 116 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 5 | TABLE ACCESS FULL| T2 | 4 | 116 | 3 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - STRDEF[22], STRDEF[22], STRDEF[2] 2 - (#keys=3) "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 3 - (rowset=256) "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 4 - (#keys=3) "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 5 - (rowset=256) "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] SJC: Considering set-join conversion in query block SET$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Checking validity of SJC on query block SET$1 (#0) SJC: SJC bypassed: Not enabled by hint/parameter. -- Run query with SJC (enabled by setting "_convert_set_to_join"=TRUE) alter session set "_convert_set_to_join"=TRUE; select a,b,c from t1 minus select a,b,c from t2; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | 1 | HASH UNIQUE | | 5 | 290 | 7 (15)| 00:00:01 | 1754K| 1754K| 495K (0)| |* 2 | HASH JOIN ANTI | | 5 | 290 | 6 (0)| 00:00:01 | 1355K| 1355K| 832K (0)| | 3 | TABLE ACCESS FULL| T1 | 5 | 145 | 3 (0)| 00:00:01 | | | | | 4 | TABLE ACCESS FULL| T2 | 4 | 116 | 3 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 2 - (#keys=3) "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 3 - "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] 4 - "A"[NUMBER,22], "B"[NUMBER,22], "C"[VARCHAR2,2] JC: Considering set-join conversion in query block SET$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Checking validity of SJC on query block SET$1 (#0) SJC: Passed validity checks. SJC: SJC: Applying SJC on query block SET$1 (#0) Registered qb: SEL$09AAA538 0x81bf8d10 (SET QUERY BLOCK SET$1; SET$1) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$09AAA538 nbfros=2 flg=0 fro(0): flg=0 objn=78224 hint_alias="T1"@"SEL$1" fro(1): flg=0 objn=78225 hint_alias="T2"@"SEL$2" -- Run query with SJC (enabled by setting "_convert_set_to_join"=TRUE) but with constant in SELECT LIST alter session set "_convert_set_to_join"=TRUE; select a,b,'X' c from t1 minus select a,b,'X' c from t2; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 8 (100)| | | | | | 1 | MINUS | | | | | | | | | | 2 | SORT UNIQUE | | 5 | 130 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 3 | TABLE ACCESS FULL| T1 | 5 | 130 | 3 (0)| 00:00:01 | | | | | 4 | SORT UNIQUE | | 4 | 104 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 5 | TABLE ACCESS FULL| T2 | 4 | 104 | 3 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - STRDEF[22], STRDEF[22], STRDEF[1] 2 - (#keys=3) "A"[NUMBER,22], "B"[NUMBER,22], 'X'[1] <<<<< Problem 'X'[1] 3 - (rowset=256) "A"[NUMBER,22], "B"[NUMBER,22] 4 - (#keys=3) "A"[NUMBER,22], "B"[NUMBER,22], 'X'[1] <<<<< Problem 'X'[1] 5 - (rowset=256) "A"[NUMBER,22], "B"[NUMBER,22] SJC: Considering set-join conversion in query block SET$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Checking validity of SJC on query block SET$1 (#0) SJC: SJC bypassed: MINUS subquery returns more than one column. SJC: SJC bypassed: invalidated. -- Run query with SJC (enabled by setting "_convert_set_to_join"=TRUE) but with constant in SELECT LIST incl. work-around alter session set "_convert_set_to_join"=TRUE; select a,b,c from (select /*+ NO_MERGE */ a,b,'X' c from t1) minus select a,b,c from (select /*+ NO_MERGE */ a,b,'X' c from t2); --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | 1 | HASH UNIQUE | | 5 | 290 | 7 (15)| 00:00:01 | 1754K| 1754K| 524K (0)| |* 2 | HASH JOIN ANTI | | 5 | 290 | 6 (0)| 00:00:01 | 1448K| 1448K| 677K (0)| |* 3 | VIEW | | 5 | 145 | 3 (0)| 00:00:01 | | | | | 4 | TABLE ACCESS FULL| T1 | 5 | 130 | 3 (0)| 00:00:01 | | | | | 5 | VIEW | | 4 | 116 | 3 (0)| 00:00:01 | | | | | 6 | TABLE ACCESS FULL| T2 | 4 | 104 | 3 (0)| 00:00:01 | | | | --------------------------------------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"[NUMBER,22], "B"[NUMBER,22], "C"[CHARACTER,1] 2 - (#keys=2) "A"[NUMBER,22], "B"[NUMBER,22], "C"[CHARACTER,1] 3 - "A"[NUMBER,22], "B"[NUMBER,22], "C"[CHARACTER,1] 4 - "A"[NUMBER,22], "B"[NUMBER,22] 5 - "A"[NUMBER,22], "B"[NUMBER,22] 6 - "A"[NUMBER,22], "B"[NUMBER,22] SJC: Considering set-join conversion in query block SET$1 (#1) ************************* Set-Join Conversion (SJC) ************************* SJC: Checking validity of SJC on query block SET$1 (#1) SJC: Passed validity checks. SJC: SJC: Applying SJC on query block SET$1 (#1) Registered qb: SEL$A0F18C0E 0xce727498 (SET QUERY BLOCK SET$1; SET$1) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$A0F18C0E nbfros=2 flg=0 fro(0): flg=1 objn=0 hint_alias="from$_subquery$_001"@"SEL$1" fro(1): flg=1 objn=0 hint_alias="from$_subquery$_003"@"SEL$3"