对于集合操作，CBO的处理之前两篇文章也介绍过，比如子查询为集合操作的子查询展开、内嵌视图为集合操作的连接谓词推入等等，这篇文章主要介绍集合操作相关的另外一种查询转换SJC – set join conversion
The purpose of set to join conversion is to avoid sort operations in compound queries involving INTERSECT and MINUS. This query transformation also postpones the elimination of duplicates to the end of processing for such queries.
A compound query based on the INTERSECT and MINUS set operators is basically carried out in the following way:
- Every component query is independently executed, the result set is sorted, and duplicates are eliminated.
- Then the set operations are executed, and the final result set is determined.
This way of executing a query involving an INTERSECT or MINUS operation isn’t always efficient. For example, when the component queries return a lot of data, but the majority of data is eliminated by the set operator, most of the data that’s later eliminated ends up being unnecessarly sorted. The set to join conversion avoids that inefficiency by transforming the query in a way that allows rows to be thrown out prior to the sort rather than after it. In addition, since the set operator is replaced by a join, additional access paths are enabled. This is a heuristic-based query transformation that, by default, isn’t enabled.The set_to_join hint has to be specified to take advantage of it.
当查询出现INTERSECT或者MINUS的集合操作时，CBO可以将其转换为连接操作。这样的好处是尽量减少集合操作带来的排序消耗。但是默认是关闭该转换的，可以使用hint强制执行SJC
SJC参数与hint：
|
1 2 3 4 5 |
KSPPINM KSPPSTVL KSPPDESC ------------------------------ ---------- ------------------------------------------------------------ _convert_set_to_join FALSE enables conversion of set operator to join hint：set_to_join/no_set_to_join |
SJC示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
1)集合操作未转换成连接操作 sql文本 select /*+qb_name(nb)*/object_id,object_name from lxy minus select /*+qb_name(sb)*/object_id,object_name from lxy_1; 执行计划 Plan hash value: 2981050614 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14243 | 667K| | 309 (1)| 00:00:04 | | 1 | MINUS | | | | | | | | 2 | SORT UNIQUE | | 14243 | 333K| 456K| 154 (1)| 00:00:02 | | 3 | TABLE ACCESS FULL| LXY | 14243 | 333K| | 52 (0)| 00:00:01 | | 4 | SORT UNIQUE | | 14245 | 333K| 456K| 154 (1)| 00:00:02 | | 5 | TABLE ACCESS FULL| LXY_1 | 14245 | 333K| | 52 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 3 - NB / LXY@NB 5 - SB / LXY_1@SB Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"NB" "LXY"@"NB") FULL(@"SB" "LXY_1"@"SB") OUTLINE(@"SB") OUTLINE(@"NB") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SB") OUTLINE_LEAF(@"NB") ALL_ROWS DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - STRDEF[BM VAR, 22], STRDEF[BM VAR, 128] 2 - (#keys=2) "OBJECT_ID"[NUMBER,22], "OBJECT_NAME"[VARCHAR2,128] 3 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 4 - (#keys=2) "OBJECT_ID"[NUMBER,22], "OBJECT_NAME"[VARCHAR2,128] 5 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 统计信息 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 380 consistent gets 0 physical reads 0 redo size 410 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed sql成本评估为309，从标红的地方可以看出该sql做了两次排序操作。 2)将集合操作转化成连接 sql文本 select /*+qb_name(nb) set_to_join(@SET$1)*/object_id,object_name from lxy minus select /*+qb_name(sb)*/object_id,object_name from lxy_1; 执行计划 Plan hash value: 1133661454 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 142 | 6816 | 105 (1)| 00:00:02 | | 1 | HASH UNIQUE | | 142 | 6816 | 105 (1)| 00:00:02 | |* 2 | HASH JOIN ANTI | | 142 | 6816 | 104 (0)| 00:00:02 | | 3 | TABLE ACCESS FULL| LXY | 14243 | 333K| 52 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| LXY_1 | 14245 | 333K| 52 (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$49349034 3 - SEL$49349034 / LXY@NB 4 - SEL$49349034 / LXY_1@SB Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"SEL$49349034") USE_HASH(@"SEL$49349034" "LXY_1"@"SB") LEADING(@"SEL$49349034" "LXY"@"NB" "LXY_1"@"SB") FULL(@"SEL$49349034" "LXY_1"@"SB") FULL(@"SEL$49349034" "LXY"@"NB") OUTLINE(@"SET$1") SET_TO_JOIN(@"SET$1") OUTLINE_LEAF(@"SEL$49349034") 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): --------------------------------------------------- 2 - access(SYS_OP_MAP_NONNULL("OBJECT_ID")=SYS_OP_MAP_NONNULL("OBJECT _ID") AND SYS_OP_MAP_NONNULL("OBJECT_NAME")=SYS_OP_MAP_NONNULL("OBJECT_N AME")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=2) "OBJECT_ID"[NUMBER,22], "OBJECT_NAME"[VARCHAR2,128] 2 - (#keys=2) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 3 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 4 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 该sql使用了set_to_join的hint，从outline和执行计划都可以发现MINUS集合操作被转换成了反连接操作。 统计信息 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 380 consistent gets 0 physical reads 0 redo size 410 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed 排序被消除 10053 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) |