EXPLAIN PLAN

Function

EXPLAIN PLAN saves information about an execution plan into the PLAN_TABLE table. Different from the EXPLAIN statement, EXPLAIN PLAN only saves plan information and does not print information on the screen.

Syntax

ExplainPlan ::= EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
FOR statement ;

Parameter Description

• PLAN: saves plan information into PLAN_TABLE. If information is stored successfully, "EXPLAIN SUCCESS" is returned.

• STATEMENT_ID: tags each query. The tag information will be stored in PLAN_TABLE.

  NOTE: If the EXPLAIN PLAN statement does not contain SET STATEMENT_ID, STATEMENT_ID is empty by default. In addition, the value of STATEMENT_ID cannot exceed 30 bytes. Otherwise, an error will be reported.

Precautions

• EXPLAIN PLAN cannot be executed on a database node.

• Plan information cannot be collected for SQL statements that failed to be executed.

• Data in PLAN_TABLE is in a session-level lifecycle. Sessions are isolated from users, and therefore users can only view the data of the current session and current user.

Example 1

You can perform the following steps to collect execution plans of SQL statements by running EXPLAIN PLAN:

1. Run the EXPLAIN PLAN statement.

   NOTE: After the EXPLAIN PLAN statement is executed, plan information is automatically stored in PLAN_TABLE. INSERT, UPDATE, and ANALYZE cannot be performed on PLAN_TABLE. For details about PLAN_TABLE, see PLAN_TABLE.

   explain plan set statement_id='TPCH-Q4' for
   select
   o_orderpriority,
   count(*) as order_count
   from
   orders
   where
   o_orderdate >= '1993-07-01'::date
   and o_orderdate < '1993-07-01'::date + interval '3 month'
   and exists (
   select
   *
   from
   lineitem
   where
   l_orderkey = o_orderkey
   and l_commitdate < l_receiptdate
   )
   group by
   o_orderpriority
   order by
   o_orderpriority;

2. Query PLAN_TABLE.

   SELECT * FROM PLAN_TABLE;

   

3. Delete data from PLAN_TABLE.

   DELETE FROM PLAN_TABLE WHERE xxx;