EXPLAIN

You can precede an SQL statement with EXPLAIN, to produce text output explaining how the optimizer will handle the command or request. EXPLAIN is discussed in detail in the Kognitio Guide.

Usage

EXPLAIN query-statement

Notes

The query is not executed by Kognitio, when preceded by EXPLAIN, rather the optimizer’s execution plan for it is returned.

Times are reported in seconds, but estimates are more accurate if you collect and update statistics on the table before submitting the EXPLAIN command.

Example

Two tables, EDP_BIG1 and EDP_BIG2 are on disk only, and have the following CREATE TABLE statements:

CREATE TABLE edp_big1 (c0 INT NOT NULL,
                       c1 DECIMAL(9, 2),
                       c2 TIMESTAMP,
                       c3 INT NOT NULL,
                       c4 VARCHAR(128) ,
                       PRIMARY KEY(c0),
                       UNIQUE(c3))

CREATE TABLE edp_big2(c0 INT NOT NULL,
                      c1 FLOAT,
                      c2 DATE,
                      c3 INT NOT NULL,
                      c4 CHAR(8),
                      PRIMARY KEY(c0),
                      UNIQUE (c3))

Run EXPLAIN to obtain the query plan for the following query.

EXPLAIN SELECT b1.c0, b2.c4
FROM edp_big1 b1, edp_big2 b2
WHERE b1.c1 < 0.0 AND b1.c0 = b2.c0
DESCRIBE TEXT

1 We apply 1 shared lock on table EDP_BIG1(1042), 1 shared lock on
table EDP_BIG2(1044).

2 We create an empty temporary table TT1 in RAM which will be hashed.

3 We select 1 column from disk table EDP_BIG1(1042) with local
conditions.The results are inserted into the hashed temporary table TT1.
The result set will contain approximately 90000 rows and has an
estimated cost of 25.730.

4 We create an empty temporary table TT2 in RAM which will be hashed.

5 We select 2 columns from disk table EDP_BIG2(1044). The results are
inserted into the hashed temporary table TT2. The result set will
contain approximately 1000000 rows and has an estimated cost of 56.904.

6 We perform an equi join between temporary table TT1 and temporary
table TT2 using 1 join column. From these rows, a result set will be
generated containing 2 columns. The results will be prepared to be
fetched by the interpreter. Approximately 90000 rows will be in the
result set with an estimated cost of 123.170.

7 We fetch rows and send them to the user.

8 We drop the temporary tables TT1 and TT2.

-- Estimated Total Cost : 205.822