GET_QUERY_OPERATOR_STATS and EXPLAIN Functions and Commands: Parent Operators Represented by Arrays¶
Attention
This behavior change is in the 2023_05 bundle.
For the current status of the bundle, refer to Bundle History.
The following commands and functions provide details about the execution of a query:
EXPLAIN, EXPLAIN USING TABULAR, and EXPLAIN USING JSON (this does not affect EXPLAIN USING TEXT)
The output of these commands and functions includes information about each operator node in the tree of operator nodes that comprise a query.
Background¶
Currently, this information includes a column or property that identifies a single parent node. This does not handle situations in which an operator node has multiple parent nodes.
For example, suppose that you are producing a profile of the following query:
WITH wv AS (
SELECT a FROM t WHERE a % 2 = 1
)
SELECT a FROM wv WHERE a % 3 = 1
UNION ALL
SELECT a FROM wv WHERE a % 5 = 1;
In the profile of this query, the WithClause [4] node has multiple parent nodes:
For the query above, the output of the GET_QUERY_OPERATOR_STATS and EXPLAIN commands and functions have a column or property that
identifies WithReference [3] as the parent node of WithClause [4]. However, there are two parent nodes:
WithReference [3] and WithReference [8].
Changes in Output¶
In the current release, the existing column or property for the parent node is replaced by one of the following columns or properties that contains an array of the IDs of the parent nodes:
SQL Command or Function |
Existing Column or Property |
New Column or Property |
|---|---|---|
EXPLAIN and SYSTEM$EXPLAIN_PLAN_JSON |
|
|
GET_QUERY_OPERATOR_STATS |
|
|
Changes in the Tabular Output of the EXPLAIN Command and the EXPLAIN_JSON Function¶
Suppose that you execute the following statement:
EXPLAIN WITH wv AS (
SELECT a FROM t WHERE a % 2 = 1
)
SELECT a FROM wv WHERE a % 3 = 1
UNION ALL
SELECT a FROM wv WHERE a % 5 = 1;
The output changed as described below:
- Previously:
The output includes the parent column, which contains a single parent node ID:
+------+------+--------+---------------+ ... | step | id | parent | operation | ... +------+------+--------+---------------+ ... | NULL | NULL | NULL | GlobalStats | ... | 1 | 0 | NULL | Result | ... | 1 | 1 | 0 | UnionAll | ... | 1 | 2 | 1 | Filter | ... | 1 | 3 | 2 | WithReference | ... | 1 | 4 | 3 | WithClause | ... ...
- Currently:
The output includes the parentOperators column, which contains an array of parent node IDs:
+------+------+-----------------+---------------+ ... | step | id | parentOperators | operation | ... |------+------+-----------------+---------------+ ... | NULL | NULL | NULL | GlobalStats | ... | 1 | 0 | NULL | Result | ... | 1 | 1 | [0] | UnionAll | ... | 1 | 2 | [1] | Filter | ... | 1 | 3 | [2] | WithReference | ... | 1 | 4 | [3, 8] | WithClause | ... ...
For the EXPLAIN_JSON function, if the plan passed to the function does not include information about the parent operators, the
parentOperators column will be NULL.
Changes in the JSON Output of the EXPLAIN Command and SYSTEM$EXPLAIN_PLAN_JSON¶
Suppose that you execute a statement that produces the JSON output of the query plan. For example:
EXPLAIN USING JSON WITH wv AS (
SELECT a FROM t WHERE a % 2 = 1
)
SELECT a FROM wv WHERE a % 3 = 1
UNION ALL
SELECT a FROM wv WHERE a % 5 = 1;
The output changed as described below:
- Previously:
The output includes the parent property, which contains a single parent node ID:
{ ... "Operations": [[ ... {"id":1,"parent":0,"operation":"UnionAll"}, {"id":2,"parent":1,"operation":"Filter", ...}, {"id":3,"parent":2,"operation":"WithReference"}, {"id":4,"parent":3,"operation":"WithClause", ...}, ...
- Currently:
The output includes the parentOperators property, which contains an array of parent node IDs:
{ ... "Operations":[[ ... {"id":1,"operation":"UnionAll","parentOperators":[0]}, {"id":2,"operation":"Filter",... , "parentOperators":[1]}, {"id":3,"operation":"WithReference","parentOperators":[2]}, {"id":4,"operation":"WithClause",... ,"parentOperators":[3,8]}, ...
Changes in the Output of the GET_QUERY_OPERATOR_STATS Function¶
Suppose that you execute the following statements:
WITH wv AS (
SELECT a FROM t WHERE a % 2 = 1
)
SELECT a FROM wv WHERE a % 3 = 1
UNION ALL
SELECT a FROM wv WHERE a % 5 = 1;
SET lid = LAST_QUERY_ID();
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS($lid));
The output changed as described below:
- Previously:
The output includes the PARENT_OPERATOR_ID column, which contains a single parent node ID:
+-----+---------+-------------+--------------------+---------------+ ... | ... | STEP_ID | OPERATOR_ID | PARENT_OPERATOR_ID | OPERATOR_TYPE | ... +-----+---------+-------------+--------------------+---------------+ ... | ... | 1 | 0 | NULL | Result | ... | ... | 1 | 1 | 0 | UnionAll | ... | ... | 1 | 2 | 1 | Filter | ... | ... | 1 | 3 | 2 | WithReference | ... | ... | 1 | 4 | 3 | WithClause | ... ...
- Currently:
The output includes the PARENT_OPERATORS column, which contains an array of parent node IDs:
|-----+---------+-------------+------------------+---------------+ ... | ... | STEP_ID | OPERATOR_ID | PARENT_OPERATORS | OPERATOR_TYPE | ... |-----+---------+-------------+------------------+---------------+ ... | ... | 1 | 0 | NULL | Result | ... | ... | 1 | 1 | [ | UnionAll | ... | | | | 0 | | ... | | | | ] | | ... | ... | 1 | 2 | [ | Filter | ... | | | | 1 | | ... | | | | ] | | ... | ... | 1 | 3 | [ | WithReference | ... | | | | 2 | | ... | | | | ] | | ... | ... | 1 | 4 | [ | WithClause | ... | | | | 3, | | ... | | | | 8 | | ... | | | | ] | | ... ...
Ref: 1175