| Hint type | Description |
|---|---|
| PQ_SUBQUERY | Provides fine-grained control over subquery behavior in parallel processing. |
| PUSH_SUBQ | Indicates the optimizer to execute subqueries that are not rewritten as joins as early as possible.
NoteFor OceanBase Database V4.3.5, the |
| NO_PUSH_SUBQ | Is the opposite of PUSH_SUBQ, instructing the optimizer to execute subqueries that are not rewritten as joins at the end.
NoteFor OceanBase Database V4.3.5, the |
PQ_SUBQUERY Hint
The PQ_SUBQUERY hint provides fine-grained control over subquery behavior in parallel processing, indicating how the optimizer should handle subqueries in a Parallel Query (PQ) environment, especially when dealing with distributed data distribution.
The PQ_SUBQUERY hint includes two main control modes: non-precise control mode and precise control mode.
Non-precise 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 for the current query block.table_list: Optional. Specifies a list of tables, used only in subplan filter methods for joins.op_order_no: The sequence number of the operation node.sub_qb_name_list: A list of aliases for subquery blocks.method_list: The distributed data distribution method. Specific distribution methods are as follows:DIST_BASIC_METHOD: No specific method is specified.DIST_PARTITION_WISE: Data is distributed by partitions using theNONE NONEmethod.DIST_PULL_TO_LOCAL: Data is pulled to the local node using theLOCAL LOCALmethod.DIST_PARTITION_NONE: Partitioned processing is performed, but no subsequent method is specified, usingPARTITION NONE.DIST_NONE_ALL: No partitioning is performed, and data is broadcast 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 options as described above.PARTITION_SORT: Whether to apply the partition sort algorithm when using hash distributed execution (HASH) or no distributed execution (NONE). Default is not to use it.PUSHDOWN: Whether to apply window function pushdown when using hash 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 into joins as early as possible. Typically, these subqueries, if not rewritten into joins, are executed after all table joins in the execution plan. If a subquery has low computational cost and can filter a large amount of data, early execution may improve plan performance. However, if the subquery is rewritten into a join (/+unnest/), this hint is ineffective. Applicable scenarios include:
- The subquery has low execution overhead but can quickly filter a large amount of data.
- The subquery's results need to be used early to narrow down the data scope.
Syntax:
/*+ PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies the alias for the subquery (for detailed information, see QB_NAME parameter above). It is used to clarify the target of the hint. If omitted, the hint applies to the subquery where it is located by default.
Example:
Use the PUSH_SUBQ hint in a query to prompt the optimizer to execute the subquery early, to filter data in 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
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 into joins at the end. It is suitable for scenarios where the subquery has high execution cost or cannot significantly reduce the number of rows. Applicable scenarios include:
- The subquery has high execution overhead or its result has little impact on the data volume.
- The subquery needs to be executed after other filtering conditions take effect to reduce the amount of input data.
Syntax:
/*+ NO_PUSH_SUBQ[(@qb_name)] */
Parameter description:
@qb_name: Optional. Specifies the alias for the subquery.
Example:
Use the NO_PUSH_SUBQ hint in a query to prompt 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);