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:

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;
Copy

In the profile of this query, the WithClause [4] node has multiple parent nodes:

Tree of operator nodes in the query profile

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

parent

parentOperators

GET_QUERY_OPERATOR_STATS

PARENT_OPERATOR_ID

PARENT_OPERATORS

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;
Copy

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;
Copy

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", ...},
    ...
Copy
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]},
    ...
Copy

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;
Copy
SET lid = LAST_QUERY_ID();
Copy
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS($lid));
Copy

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

Language: English