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
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.
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:
- Run the EXPLAIN PLAN statement.
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;
- Query PLAN_TABLE.
SELECT * FROM PLAN_TABLE;
- Delete data from PLAN_TABLE.
DELETE FROM PLAN_TABLE WHERE xxx;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot