Hints that control 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 more information, 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_list: specifies 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 in 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 supported for UNION ALL. |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 NONE RANDOM) | Only supported for UNION ALL. |
| DIST_SET_RANDOM | PQ_SET ( @SET$1 RANDOM NONE RANDOM) | Only supported for UNION ALL. |
| DIST_HASH_HASH | PQ_SET ( @SET$1 SEL$1 HASH HASH) | Only supported for two-branch UNION or INTERSECT operations, with SEL$1 as the left branch. |
| DIST_NONE_PARTITION | PQ_SET ( @SET$1 SEL$2 NONE PARTITION ) | Only supported for two-branch UNION or INTERSECT operations, with SEL$2 as the left branch. |
| DIST_PARTITION_NONE | PQ_SET ( @SET$1 SEL$1 PARTITION NONE ) | Only supported for two-branch UNION or INTERSECT operations, with SEL$1 as the left branch |
Examples
The following examples use pq_set(@set$1 local local) to specify local data redistribution in a distributed execution plan, use pq_set(@set$1 sel$2 hash hash) to specify redistribution in a distributed execution plan by using the HASH algorithm, and use sel$2 as the left operand in the set operation.
-- Specify that set operations use specific distributed execution plans by using PQ_SET hints.
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 query 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 to use 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: the hash method and the merge method. If you use the NO_USE_HASH_SET hint, the optimizer uses the merge method for set operations. The UNION ALL set operation does not have deduplication semantics, and therefore, it 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