How do I view the execution plan of a SQL statement in Lindorm - Lindorm

You can use the EXPLAIN syntax to view the execution plan of a SQL statement. For example, you can use this syntax to check whether indexes are efficiently used in the execution of a query statement when you want to optimize the statement.

Applicable engines and versions

The EXPLAIN syntax is applicable to LindormTable and LindormTSDB.
LindormTable 2.6.3 and later versions support execution plans of different depths.

Syntax

explain_statement         ::= EXPLAIN [ PLAN ] [ explain_depth_expression ] 
                              [ FOR ] select_statement;
explain_depth_expression  ::= { WITH TYPE | WITHOUT IMPLEMENTATION }

Parameters

Plan depth (explain_depth_expression)

Important

The Lindorm SQL version is 2.6.4 or later. For more information about how to view the Lindorm SQL version, see SQL versions.

The execution plans returned by the EXPLAIN syntax have different depths. The following table describes the three depths and how to specify the them in the statement.

Depth	Depth expression	Description
Physical plan	N/A	Physical plans that are optimized and filtered by the optimizer.
Logical plan	WITHOUT IMPLEMENTATION	Logical plans that are not optimized.
Type plan	WITH TYPE	Plans that contains only the types of each column in the result sets of the query.

SELECT statement (select_statement)

The select_statement parameter in the syntax is the query statement whose execution plan you want to view.

Examples

In the following examples, the test table is created by executing the following statement:

CREATE TABLE test (
    p1 VARCHAR(255) NOT NULL,
    c1 VARCHAR(255),
    PRIMARY KEY (p1)
);

Data is written to some columns of the test table by executing the following statement:

INSERT INTO test (p1, c1) VALUES
('3abc9378', '10'),
('3def9378', NULL),
('3ghi1234', '15'),
('3jkl5678', NULL),
('3mno9378', '20'),
('3pqr0000', '25'),
('3stu9378', '30'),
('3vwx0000', NULL),
('3yz_9378', '35'),
('3_1239378', '40');

The following examples show how to view the execution plan of different depths for the same query statement:

View the physical plan

EXPLAIN SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;

View the logical plan

EXPLAIN WITHOUT IMPLEMENTATION FOR SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;

View the type plan

EXPLAIN WITH TYPE FOR SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;