Lu, Huayi e6fa411af0 DWS DEV 830.201 version

Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>

2024-05-16 07:24:04 +00:00

15 KiB

Raw Permalink Blame History

EXPLAIN PLAN

Function

You can run the EXPLAIN PLAN statement to save the information about an execution plan to the PLAN_TABLE table. Different from the EXPLAIN statement, EXPLAIN PLAN only stores plan information and does not print it on the screen.

Syntax

1
2
3
EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
FOR statement ;

Parameter Description

PLAN
Stores plan information in PLAN_TABLE. If the storing is successful, EXPLAIN SUCCESS is returned.
STATEMENT_ID
Tags a query. The tag information will be stored in PLAN_TABLE.

If the EXPLAIN PLAN statement does not contain SET STATEMENT_ID, the value of 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 DNs.
Plan information cannot be collected for SQL statements that failed to be executed.
Data in PLAN_TABLE is in a session-level life cycle. Sessions are isolated from users and thereby users can view data of only the current session and current user.
PLAN_TABLE cannot be joined with GDS foreign tables.
For a query that cannot be pushed down, object information cannot be collected and only such information as REMOTE_QUERY and CTE can be collected. For details, see Example 2.

Example 1

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

Import TPC-H sample data.

Run the EXPLAN 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 the PLAN_TABLE system view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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.
1
SELECT * FROM PLAN_TABLE;
Delete data from PLAN_TABLE.
1
DELETE FROM PLAN_TABLE WHERE xxx;

Example 2

For a query that cannot be pushed down, only such information as REMOTE_QUERY and CTE can be collected from PLAN_TABLE after EXPLAIN PLAN is executed.

The optimizer generates a plan for pushing down statements. In this case, only REMOTE_QUERY can be collected.

1
2
3
4
5
  explain plan set statement_id = 'test remote query' for
  select
  current_user
  from 
  customer;

Query PLAN_TABLE.

1
SELECT * FROM PLAN_TABLE;

Parent topic: DML Syntax

15 KiB Raw Permalink Blame History