Hint type |
Description |
|---|---|
| PQ_SUBQUERY | Provides fine-grained control over the behavior of subqueries in parallel processing. |
| PUSH_SUBQ | Indicates the optimizer to execute unrewritten subqueries as early as possible.
NoteFor OceanBase Database V4.3.5, the |
| NO_PUSH_SUBQ | The reverse of PUSH_SUBQ, instructing the optimizer to execute unrewritten subqueries at the end.
NoteFor OceanBase Database V4.3.5, the |
PQ_SUBQUERY Hint
The PQ_SUBQUERY hint provides fine-grained control over the behavior of subqueries in parallel processing. It instructs the optimizer on how to handle subqueries in a Parallel Query (PQ) environment, especially when dealing with distributed data distribution.
The PQ_SUBQUERY hint includes two main control modes: the approximate control mode and the precise control mode.
Approximate control mode
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] [table_list] [op_order_no] sub_qb_name_list method_list) */
Parameter description
@qb_name: Optional. Specifies the alias of the current query block.table_list: Optional. Specifies the list of tables. This parameter is used only in the subplan filter method for joins.op_order_no: The sequence number of the operation node.sub_qb_name_list: The list of aliases for subquery blocks.method_list: The distributed data distribution method. The specific distribution methods are as follows:DIST_BASIC_METHOD: No specific method is specified.DIST_PARTITION_WISE: Data is distributed by partition using theNONE NONEmethod.DIST_PULL_TO_LOCAL: Data is pulled to the local node using theLOCAL LOCALmethod.DIST_PARTITION_NONE: Data is partitioned, but no subsequent method is specified, usingPARTITION NONE.DIST_NONE_ALL: Data is not partitioned and is broadcasted to all nodes usingNONE ALL.
Example
PQ_SUBQUERY(@SEL$1 0 SEL$1 PARTITION NONE) -- Can be omitted 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 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 mode
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] win_group ...) */
Parameter description
win_group: (Index list) Declares the grouping and internal behavior of window functions.dist_method: Specifies the distributed execution method, with the same options as above.PARTITION_SORT: Whether to apply the partition sort algorithm when using hash-based distributed execution (HASH) or non-distributed execution (NONE). Default is not to use it.PUSHDOWN: Whether to apply window function pushdown when using hash-based distributed execution.
Example
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 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
Note
For OceanBase Database V4.3.5, the PUSH_SUBQ hint is supported starting from V4.3.5 BP2.
The PUSH_SUBQ hint instructs the optimizer to execute subqueries that have not been rewritten as joins as early as possible. Typically, these subqueries, if not rewritten as joins, are executed after all table joins in the execution plan. If the subquery has a low computational cost and can filter a large amount of data, executing it early may improve plan performance. However, if the subquery is rewritten as a join (/+unnest/), this hint is ineffective. The following scenarios are applicable:
- The subquery has a low execution cost but can quickly filter a large amount of data.
- The subquery's results need to be used early to narrow down the data range.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies the alias of the subquery (for more information, see the QB_NAME parameter above). This parameter is used to clarify the target of the hint. If omitted, the hint applies to the subquery in which it is placed by default.
Example:
Use the PUSH_SUBQ hint in a query to instruct the optimizer to execute the subquery early to filter data in the tbl1 table.
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
Note
For OceanBase Database V4.3.5, the NO_PUSH_SUBQ hint is supported starting from V4.3.5 BP2.
The NO_PUSH_SUBQ hint is the opposite of the PUSH_SUBQ hint. It instructs the optimizer to execute subqueries that have not been rewritten as joins at the end. This is applicable when the subquery has a high cost or cannot significantly reduce the number of rows. The following scenarios are applicable:
- The subquery has a high execution cost or its result has a minimal impact on the data volume.
- The subquery needs to be executed after other filtering conditions take effect to reduce the input data volume.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies the alias of the subquery.
Example:
Use the NO_PUSH_SUBQ hint in a query to instruct the optimizer to execute the subquery at the end.
SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);