| Hint | Description |
|---|---|
| PQ_SUBQUERY | Provides fine-grained control over the behavior of subqueries in parallel processing. |
| PUSH_SUBQ | Instructs the optimizer to execute subqueries that have not been rewritten as joins as early as possible.
NoteIn OceanBase Database V4.3.5, the |
| NO_PUSH_SUBQ | The reverse operation of PUSH_SUBQ, instructing the optimizer to execute subqueries that have not been rewritten as joins last.
NoteIn OceanBase Database V4.3.5, the |
PQ_SUBQUERY hint
The PQ_SUBQUERY hint provides fine-grained control over the behavior of subqueries in parallel queries (PQ) and indicates how the optimizer should handle subqueries in PQ environments, especially when distributed data distribution is involved.
The PQ_SUBQUERY hint offers two main control modes: imprecise control mode and precise control mode.
Imprecise control mode
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] [table_list] [op_order_no] sub_qb_name_list method_list) */
Parameters
@qb_name: optional, specifies the alias of the current query block.table_list: optional, specifies the alias list of the representative table, used only in the join allocation subplan filter method.op_order_no: the sequential number of the operation node.sub_qb_name_list: the alias list of the subquery block.method_list: the data distribution method. Specific distribution methods include:DIST_BASIC_METHOD: unspecified method.DIST_PARTITION_WISE: data is distributed partition-wise using theNONE NONEmethod.DIST_PULL_TO_LOCAL: data is pulled to the local node using theLOCAL LOCALmethod.DIST_PARTITION_NONE: partitioning is performed, but no specific method is specified.DIST_NONE_ALL: no partitioning is performed, and data is broadcast to all nodes using theNONE ALLmethod.
Examples
PQ_SUBQUERY(@SEL$1 0 SEL$1 PARTITION NONE) -- Can be omitted to 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 mode
Syntax
/*+ PQ_SUBQUERY ([ @qb_name ] win_group ...) */
Parameters
win_group: (index list) declares the groups of window functions and the tuples of internal behavior.dist_method: specifies the distributed execution method. For details, see the description of the same parameter in imprecise control mode.PARTITION_SORT: specifies whether to apply the partition sort algorithm when hash distributed execution (HASH) or non-distributed execution (NONE) is used. The default is not to apply this algorithm.PUSHDOWN: specifies whether to apply window function pushdown when hash distributed execution is used.
Examples
PQ_SUBQUERY (@SEL$1 (t_a t_b t_c) 0 SEL$2 PARTITION NONE)
-- Can be omitted to 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
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 unrewritten subqueries as early as possible. Generally, if subqueries are not rewritten as joins, they are placed after all table joins in the execution plan. If a subquery has a low computation cost but can filter out a large amount of data, it is recommended to execute the subquery earlier to improve the performance of the plan. Note that if the subquery is rewritten as a join (/+unnest/), this hint becomes invalid. This hint applies to the following scenarios:
- The execution overhead of the subquery is low, but it can filter out a large amount of data.
- It is necessary to narrow down the data range as soon as possible based on the result of the subquery.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
The parameters are described as follows:
@qb_name: the alias of the subquery. This parameter is optional. If you specify this parameter, you can explicitly indicate the hint-targeted object. If the parameter is not specified, the hint takes effect on the subquery that contains the hint.
Examples:
The following example shows how to use the PUSH_SUBQ hint to instruct the optimizer to execute the subquery as early as possible to filter out 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 operation of the PUSH_SUBQ hint. It instructs the optimizer to execute unrewritten subqueries after all other operations. This hint is suitable for subqueries with high execution overhead or subqueries whose results do not significantly reduce the number of rows. This hint applies to the following scenarios:
- The execution overhead of the subquery is high, or its result does not significantly reduce the number of rows.
- You want to delay the execution of the subquery until other filtering conditions take effect to reduce the input data volume for the subquery.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
The parameters are described as follows:
@qb_name: the alias of the subquery. This parameter is optional.
Examples:
The following example shows how to use the NO_PUSH_SUBQ hint to instruct the optimizer to execute the subquery after all other operations.
SELECT /*+ NO_PUSH_SUBQ */ *
FROM tbl1, tbl2
WHERE tbl1.col1 = (SELECT MAX(tbl3.col1)
FROM tbl3
WHERE tbl3.col2 = tbl2.col2);