A hint that controls the behavior of set operations.
Since there is no direct location to add hints for set operation clauses, the hints described in this topic must use the qb name to specify the set operation object they affect. For detailed usage, see the examples of each hint.
Hint type |
Description |
|---|---|
PQ_SET |
Controls the distributed execution mode for set operations. |
USE_HASH_SET |
Enables the optimizer to use the HASH method in set operations. Its inverse operation is NO_USE_HASH_SET. |
NO_USE_HASH_SET |
Prohibits the optimizer from using the HASH method in set operations. Its inverse 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: Identifies the left branch for theUNIONorINTERSECTset operation.method_listspecifies the specific distributed execution method.
The specific method description and examples are as follows:
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) | Limited to UNION ALL |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 NONE RANDOM) | Limited to UNION ALL |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 RANDOM NONE RANDOM) | Limited to UNION ALL |
| DIST_HASH_HASH | PQ_SET ( @SET$1 SEL$1 HASH HASH) | Limited to union/intersect of two branches, with SEL$1 as the left branch |
| DIST_NONE_PARTITION | PQ_SET ( @SET$1 SEL$2 NONE PARTITION ) | Limited to union/intersect of two branches, with SEL$2 as the left branch |
| DIST_PARTITION_NONE | PQ_SET ( @SET$1 SEL$1 PARTITION NONE ) | Limited to union/intersect of two branches, with SEL$1 as the left branch |
Examples
In the query example below, pq_set(@set$1 local local) specifies to pull data directly back to the local node for distributed execution. pq_set(@set$1 sel$2 hash hash) instructs to redistribute data using the HASH method for set operations, with sel$2 serving as the left branch of the set operation.
-- Specify a specific distributed execution method for set operations 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 controls the specific algorithm used for set operations. Its reverse operation is NO_USE_HASH_SET.
Syntax
/*+ USE_HASH_SET [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, USE_HASH_SET is used to enable the HASH algorithm.
-- Use the USE_HASH_SET hint to force set operations to use the HASH method.
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 controls the specific algorithm used for set operations. Its inverse operation is USE_HASH_SET.
Syntax
/*+ NO_USE_HASH_SET [ ( [ @ qb_name ] ) ] */
Example
In the query example below, NO_USE_HASH_SET is used to disable the HASH algorithm. Since OceanBase supports both HASH and MERGE set operation algorithms, using the NO_USE_HASH_SET hint effectively instructs the set operation to use the MERGE algorithm. The UNION ALL set operation does not have a deduplication semantics and is not controlled by this hint.
-- Use the NO_USE_HASH_SET hint to instruct the optimizer to avoid using hash methods in 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