Hint type |
Description |
|---|---|
| PQ_SUBQUERY | Provides fine-grained control over the behavior of subqueries in parallel processing. |
| PUSH_SUBQ | Used to instruct the optimizer to execute subqueries that have not been rewritten as joins as early as possible. |
| NO_PUSH_SUBQ | Yes PUSH_SUBQ is the reverse operation of. It is used by the optimizer to place subqueries that have not been rewritten as joins at the end of the execution plan. |
PQ_SUBQUERY Hint
The PQ_SUBQUERY hint provides fine-grained control over the behavior of subqueries in parallel processing, instructing the optimizer how to handle subqueries in a parallel query (PQ) environment, especially when distributed data distribution is involved.
The PQ_SUBQUERY hint supports two main control methods: approximate control and precise control.
Inexact control methods
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] [table_list] [op_order_no] sub_qb_name_list method_list) */
Parameter description
@qb_name: Optional. Specifies an alias for the current query block.table_list: Optional. Specifies the list represented, which is used only in the subplan filter method allocated during connection establishment.op_order_no: The sequence number of the operation node.sub_qb_name_list: The list of aliases for the subquery blocks.method_list: The distributed data distribution method. The specific methods are as follows:DIST_BASIC_METHOD: no specific method is specified.DIST_PARTITION_WISE: Distribute data by partition, using theNONE NONEmethod.DIST_PULL_TO_LOCAL: Pull data to the local device using theLOCAL LOCALmethod.DIST_PARTITION_NONE: Partition processing is performed, but no subsequent method is specified. In this case,PARTITION NONEis used.DIST_NONE_ALL: Do not partition the data. Broadcast the data to all nodes by usingNONE ALL.
Examples
PQ_SUBQUERY(@SEL$1 0 SEL$1 PARTITION NONE) -- Can be abbreviated as PQ_SUBQUERY(SEL$1)
PQ_SUBQUERY(@SEL$1 1 SEL$2 PARTITION NONE)
CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT)
PARTITION BY HASH(c1) PARTITIONS 10;
EXPLAIN
SELECT
(SELECT t_inner.c1 FROM t1 t_inner WHERE t_inner.c1 = t_outer.c3) AS a
FROM t1 t_outer
ORDER BY c3;
The return result is as follows:
+--------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------+
| ========================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |3 |157 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10002|3 |156 | |
| |2 | └─SUBPLAN FILTER | |3 |155 | |
| |3 | ├─EXCHANGE IN DISTR | |3 |42 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10001|3 |41 | |
| |5 | │ └─EXCHANGE IN MERGE SORT DISTR| |3 |40 | |
| |6 | │ └─EXCHANGE OUT DISTR |:EX10000|3 |40 | |
| |7 | │ └─SORT | |3 |39 | |
| |8 | │ └─PX PARTITION ITERATOR | |3 |38 | |
| |9 | │ └─TABLE FULL SCAN |t_outer |3 |38 | |
| |10| └─PX PARTITION ITERATOR | |1 |38 | |
| |11| └─TABLE FULL SCAN |t_inner |1 |38 | |
| ========================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(subquery(1))]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(subquery(1))]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([subquery(1)]), filter(nil), rowset=16 |
| exec_params_([t_outer.c3(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false |
| 3 - output([t_outer.c3]), filter(nil), rowset=16 |
| 4 - output([t_outer.c3]), filter(nil), rowset=16 |
| (#keys=1, [t_outer.c3]), is_single, dop=1 |
| 5 - output([t_outer.c3]), filter(nil), rowset=16 |
| sort_keys([t_outer.c3, ASC]) |
| 6 - output([t_outer.c3]), filter(nil), rowset=16 |
| dop=1 |
| 7 - output([t_outer.c3]), filter(nil), rowset=16 |
| sort_keys([t_outer.c3, ASC]) |
| 8 - output([t_outer.c3]), filter(nil), rowset=16 |
| force partition granule |
| 9 - output([t_outer.c3]), filter(nil), rowset=16 |
| access([t_outer.c3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([t_outer.__pk_increment]), range(MIN ; MAX)always true |
| 10 - output([t_inner.c1]), filter(nil), rowset=16 |
| affinitize, partition wise, force partition granule |
| 11 - output([t_inner.c1]), filter([t_inner.c1 = :0]), rowset=16 |
| access([t_inner.c1]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t_inner.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------------------------+
44 rows in set
Precise control method
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] win_group ...) */
Parameter description
win_group: A tuple that declares the grouping for window functions and their internal behavior (index list).dist_method: Specifies the distributed execution method. Valid values are the same as above.PARTITION_SORT: Specifies whether to apply the partition sort algorithm when using hash distributed execution (HASH) or no distributed execution (NONE). The default is not to use it.PUSHDOWN: Specifies whether to push down window functions during hash distribution execution.
Examples
PQ_SUBQUERY (@SEL$1 (t_a t_b t_c) 0 SEL$2 PARTITION NONE)
-- Can be omitted as PQ_SUBQUERY ((t_a t_b t_c) SEL$2 PARTITION NONE)
EXPLAIN
SELECT /*+ PQ_SUBQUERY (@SEL$1 (t_a, t_b, t_c) 0 SEL$2 PARTITION NONE) */
t_a.c1 AS a_c1,
t_b.c1 AS b_c1,
t_c.c1 AS c_c1,
t_d.c1 AS d_c1
FROM t1 t_a
LEFT JOIN t1 t_b ON t_a.c3 = t_b.c4
INNER JOIN t1 t_c ON t_a.c1 = t_c.c1
RIGHT JOIN t1 t_d ON t_a.c3 = t_d.c4
WHERE t_a.c1 = t_d.c3
AND t_c.c2 + (SELECT c4 FROM t1 WHERE c1 = t_c.c3 AND c2 = t_a.c2 AND c3 = t_b.c1) = t_a.c3;
The return result is as follows:
+--------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------+
| =========================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |302 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10004|1 |300 | |
| |2 | └─SUBPLAN FILTER | |1 |298 | |
| |3 | ├─EXCHANGE IN DISTR | |3 |184 | |
| |4 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10003|3 |180 | |
| |5 | │ └─HASH JOIN | |3 |172 | |
| |6 | │ ├─EXCHANGE IN DISTR | |3 |43 | |
| |7 | │ │ └─EXCHANGE OUT DISTR |:EX10000|3 |42 | |
| |8 | │ │ └─PX PARTITION ITERATOR | |3 |38 | |
| |9 | │ │ └─TABLE FULL SCAN |t_d |3 |38 | |
| |10| │ └─HASH RIGHT OUTER JOIN | |3 |128 | |
| |11| │ ├─EXCHANGE IN DISTR | |3 |41 | |
| |12| │ │ └─EXCHANGE OUT DISTR |:EX10001|3 |40 | |
| |13| │ │ └─PX PARTITION ITERATOR| |3 |38 | |
| |14| │ │ └─TABLE FULL SCAN |t_b |3 |38 | |
| |15| │ └─EXCHANGE IN DISTR | |3 |86 | |
| |16| │ └─EXCHANGE OUT DISTR |:EX10002|3 |84 | |
| |17| │ └─PX PARTITION ITERATOR| |3 |78 | |
| |18| │ └─HASH JOIN | |3 |78 | |
| |19| │ ├─TABLE FULL SCAN |t_a |3 |38 | |
| |20| │ └─TABLE FULL SCAN |t_c |3 |38 | |
| |21| └─PX PARTITION ITERATOR | |1 |39 | |
| |22| └─TABLE FULL SCAN |t1 |1 |39 | |
| =========================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t_a.c1, t_b.c1, t_c.c1, t_d.c1)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t_a.c1, t_b.c1, t_c.c1, t_d.c1)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([t_b.c1], [t_a.c1], [t_d.c1], [t_c.c1]), filter([t_c.c2 + subquery(1) = t_a.c3]), rowset=16 |
| exec_params_([t_c.c3(:0)], [t_a.c2(:1)], [t_b.c1(:2)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false |
| 3 - output([t_b.c1], [t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_d.c1], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| 4 - output([t_b.c1], [t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_d.c1], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| (#keys=1, [t_c.c3]), is_single, dop=1 |
| 5 - output([t_b.c1], [t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_d.c1], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| equal_conds([t_a.c1 = t_d.c3], [t_a.c3 = t_d.c4]), other_conds(nil) |
| 6 - output([t_d.c1], [t_d.c4], [t_d.c3]), filter(nil), rowset=16 |
| 7 - output([t_d.c1], [t_d.c4], [t_d.c3]), filter(nil), rowset=16 |
| dop=1 |
| 8 - output([t_d.c1], [t_d.c4], [t_d.c3]), filter(nil), rowset=16 |
| force partition granule |
| 9 - output([t_d.c1], [t_d.c4], [t_d.c3]), filter(nil), rowset=16 |
| access([t_d.c1], [t_d.c4], [t_d.c3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([t_d.__pk_increment]), range(MIN ; MAX)always true |
| 10 - output([t_b.c1], [t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| equal_conds([t_a.c3 = t_b.c4]), other_conds(nil) |
| 11 - output([t_b.c1], [t_b.c4]), filter(nil), rowset=16 |
| 12 - output([t_b.c1], [t_b.c4]), filter(nil), rowset=16 |
| dop=1 |
| 13 - output([t_b.c1], [t_b.c4]), filter(nil), rowset=16 |
| force partition granule |
| 14 - output([t_b.c1], [t_b.c4]), filter(nil), rowset=16 |
| access([t_b.c1], [t_b.c4]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([t_b.__pk_increment]), range(MIN ; MAX)always true |
| 15 - output([t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| 16 - output([t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| dop=1 |
| 17 - output([t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| partition wise, force partition granule |
| 18 - output([t_c.c3], [t_a.c2], [t_a.c1], [t_a.c3], [t_c.c1], [t_c.c2]), filter(nil), rowset=16 |
| equal_conds([t_a.c1 = t_c.c1]), other_conds(nil) |
| 19 - output([t_a.c1], [t_a.c3], [t_a.c2]), filter(nil), rowset=16 |
| access([t_a.c1], [t_a.c3], [t_a.c2]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([t_a.__pk_increment]), range(MIN ; MAX)always true |
| 20 - output([t_c.c1], [t_c.c3], [t_c.c2]), filter(nil), rowset=16 |
| access([t_c.c1], [t_c.c3], [t_c.c2]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([t_c.__pk_increment]), range(MIN ; MAX)always true |
| 21 - output([t1.c4]), filter(nil), rowset=16 |
| affinitize, partition wise, force partition granule |
| 22 - output([t1.c4]), filter([t1.c1 = :0], [t1.c2 = :1], [t1.c3 = :2]), rowset=16 |
| access([t1.c1], [t1.c2], [t1.c3], [t1.c4]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------------------------------------------------+
80 rows in set
PUSH_SUBQ Hint
The PUSH_SUBQ hint instructs the optimizer to execute subqueries that have not been rewritten as joins as early as possible. Typically, if not rewritten as joins, these subqueries are evaluated after all table join calculations in the execution plan. If a subquery has a low computation cost and can filter out a large amount of data, executing it in advance may improve plan performance. Note that this hint becomes invalid if the subquery is rewritten as a join using the /+unnest/ operator. Applicable scenarios include:
- Subqueries have low execution overhead but can quickly filter out large amounts of data.
- You should use the results of subqueries as early as possible to narrow down the data range.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies an alias for the subquery (for more information, see QB_NAME parameter above). This is used to explicitly identify the object affected by the hint. If omitted, the hint applies by default to the subquery where it is located.
Example:
In the query, use the PUSH_SUBQ hint to instruct the optimizer to execute the subquery as early as possible, thereby filtering data from the tbl1 table in advance.
SELECT /*+ PUSH_SUBQ(@"SEL$2") */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);
NO_PUSH_SUBQ Hint
The NO_PUSH_SUBQ hint is the reverse operation of PUSH_SUBQ, instructing the optimizer to execute subqueries that have not been rewritten as joins last. It is suitable when a subquery has a high cost or cannot significantly reduce the number of rows. Application scenarios include:
- The subquery has a high execution cost, or its result has little impact on the data volume.
- The execution of subqueries should be delayed until other filtering conditions take effect, to reduce the amount of input data for them.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies an alias for the subquery.
Example:
Use the NO_PUSH_SUBQ hint in queries to instruct the optimizer to push subqueries until the last possible execution moment.
SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);