Hints for controlling the behavior of set operations.
Since there is no direct way to add hints to set operation clauses, the hints described in this section must be specified using qb name to indicate the set operation object they apply to. For detailed usage, see the examples for each hint.
| Hint Type | Description |
|---|---|
PQ_SET |
Controls the distributed execution method for set operations. |
USE_HASH_SET |
Enables the optimizer to use the HASH method in set operations. Its reverse operation is NO_USE_HASH_SET. |
NO_USE_HASH_SET |
Disables the optimizer from using the HASH method in set operations. Its reverse operation is USE_HASH_SET. |
PQ_SET Hint
The PQ_SET hint specifies the data distribution method for set operations during distributed execution.
Syntax
PQ_SET ( [ @qb_name ] [left_branch_qb_name] method_list )
Parameters
left_branch_qb_name: specifies the left branch of theUNIONorINTERSECToperation.method_listspecifies the distributed execution method.
The following table describes the distributed execution methods and provides examples.
| Data distribution method | Outline data hint description | Description |
|---|---|---|
| DIST_BASIC_METHOD | PQ_SET ( @SET$1 ), no hint is added to the outline | |
| DIST_PARTITION_WISE | PQ_SET ( @SET$1 NONE NONE) | |
| DIST_PULL_TO_LOCAL | PQ_SET ( @SET$1 LOCAL LOCAL) | |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 RANDOM NONE) | Only for UNION ALL |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 NONE RANDOM) | Only for UNION ALL |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 RANDOM NONE RANDOM) | Only for UNION ALL |
| DIST_HASH_HASH | PQ_SET ( @SET$1 SEL$1 HASH HASH) | Only for two-branch union/intersect operations, with SEL$1 as the left branch |
| DIST_NONE_PARTITION | PQ_SET ( @SET$1 SEL$2 NONE PARTITION ) | Only for two-branch union/intersect operations, with SEL$2 as the left branch |
| DIST_PARTITION_NONE | PQ_SET ( @SET$1 SEL$1 PARTITION NONE ) | Only for two-branch union/intersect operations, with SEL$1 as the left branch |
Examples
The following query examples show how to use pq_set(@set$1 local local) for direct data retrieval to the local node in a distributed execution, and how to use pq_set(@set$1 sel$2 hash hash) to redistribute data using the HASH method for set operations with sel$2 as the left operand of the set operation.
-- Specify a distributed execution mode for set operations by using the PQ_SET hint.
CREATE TABLE tp1(c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 10;
EXPLAIN BASIC SELECT /*+ PQ_SET(@set$1 local local) */ c2, c3 FROM tp1 a UNION SELECT c2, c3 FROM tp1 b;
+------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME | |
| ----------------------------------------- |
| |0 |HASH UNION DISTINCT | | |
| |1 |├─PX COORDINATOR | | |
| |2 |│ └─EXCHANGE OUT DISTR |:EX10000| |
| |3 |│ └─PX PARTITION ITERATOR| | |
| |4 |│ └─TABLE FULL SCAN |A | |
| |5 |└─PX COORDINATOR | | |
| |6 | └─EXCHANGE OUT DISTR |:EX20000| |
| |7 | └─PX PARTITION ITERATOR| | |
| |8 | └─TABLE FULL SCAN |B | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([UNION([1])], [UNION([2])]), filter(nil), rowset=16 |
| 1 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| 2 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| dop=1 |
| 3 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| force partition granule |
| 4 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| access([A.C2], [A.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([A.__pk_increment]), range(MIN ; MAX)always true |
| 5 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| 6 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| dop=1 |
| 7 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| force partition granule |
| 8 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| access([B.C2], [B.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([B.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------+
34 rows in set
EXPLAIN BASIC
SELECT /*+ PQ_SET(@set$1 sel$2 hash hash) */ c2, c3 FROM tp1 a
UNION
SELECT c2, c3 FROM tp1 b;
+-----------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME | |
| ----------------------------------------------- |
| |0 |PX COORDINATOR | | |
| |1 |└─EXCHANGE OUT DISTR |:EX10002| |
| |2 | └─HASH UNION DISTINCT | | |
| |3 | ├─EXCHANGE IN DISTR | | |
| |4 | │ └─EXCHANGE OUT DISTR (HASH)|:EX10000| |
| |5 | │ └─PX PARTITION ITERATOR | | |
| |6 | │ └─TABLE FULL SCAN |B | |
| |7 | └─EXCHANGE IN DISTR | | |
| |8 | └─EXCHANGE OUT DISTR (HASH)|:EX10001| |
| |9 | └─PX PARTITION ITERATOR | | |
| |10| └─TABLE FULL SCAN |A | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(UNION([1]), UNION([2]))]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(UNION([1]), UNION([2]))]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([UNION([1])], [UNION([2])]), filter(nil), rowset=16 |
| 3 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| 4 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| (#keys=2, [B.C2], [B.C3]), dop=1 |
| 5 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| force partition granule |
| 6 - output([B.C2], [B.C3]), filter(nil), rowset=16 |
| access([B.C2], [B.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([B.__pk_increment]), range(MIN ; MAX)always true |
| 7 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| 8 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| (#keys=2, [A.C2], [A.C3]), dop=1 |
| 9 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| force partition granule |
| 10 - output([A.C2], [A.C3]), filter(nil), rowset=16 |
| access([A.C2], [A.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([A.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------+
39 rows in set
USE_HASH_SET Hint
The USE_HASH_SET hint specifies the algorithm used for set operations. Its opposite is NO_USE_HASH_SET.
Syntax
/*+ USE_HASH_SET [ ( [ @ qb_name ] ) ] */
Examples
The following example shows how to use the USE_HASH_SET hint to force the use of the HASH algorithm for set operations.
-- Use the USE_HASH_SET hint to force the use of the HASH method for set operations.
EXPLAIN BASIC
SELECT /*+ USE_HASH_SET(@set$1) */ c1, c2, c3 FROM t1
UNION
SELECT c1, c2, c3 FROM t1;
+--------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------+
| ============================= |
| |ID|OPERATOR |NAME| |
| ----------------------------- |
| |0 |HASH UNION DISTINCT| | |
| |1 |├─TABLE FULL SCAN |T1 | |
| |2 |└─TABLE FULL SCAN |T1 | |
| ============================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([UNION([1])], [UNION([2])], [UNION([3])]), filter(nil), rowset=16 |
| 1 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------+
18 rows in set
NO_USE_HASH_SET Hint
The NO_USE_HASH_SET hint specifies the algorithm used for set operations. The reverse of this hint is USE_HASH_SET.
Syntax
/*+ NO_USE_HASH_SET [ ( [ @ qb_name ] ) ] */
Examples
The following example shows how to use the NO_USE_HASH_SET hint to prevent the optimizer from using the hash method for set operations. OceanBase Database supports two algorithms for set operations: hash and merge. If you use the NO_USE_HASH_SET hint, the optimizer uses the merge algorithm for set operations. The UNION ALL set operation does not have the deduplication semantics and is not affected by this hint.
-- Use the NO_USE_HASH_SET hint to instruct the optimizer to avoid using the hash method for set operations.
EXPLAIN BASIC
SELECT /*+ NO_USE_HASH_SET(@set$1) */ c1, c2, c3 FROM t1
UNION
SELECT c1, c2, c3 FROM t1;
+--------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------+
| ============================== |
| |ID|OPERATOR |NAME| |
| ------------------------------ |
| |0 |MERGE UNION DISTINCT| | |
| |1 |├─SORT | | |
| |2 |│ └─TABLE FULL SCAN |T1 | |
| |3 |└─SORT | | |
| |4 | └─TABLE FULL SCAN |T1 | |
| ============================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([UNION([1])], [UNION([2])], [UNION([3])]), filter(nil), rowset=16 |
| 1 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| sort_keys([T1.C1, ASC], [T1.C2, ASC], [T1.C3, ASC]) |
| 2 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
| 3 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| sort_keys([T1.C1, ASC], [T1.C2, ASC], [T1.C3, ASC]) |
| 4 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------+
24 rows in set