EXPLAIN

Returns explain information for a statement.

 

Usage

Embedded, Interactive, ODBC, JDBC

Description

Explain information for a SELECT, UPDATE, INSERT or DELETE statement is returned as a result set.

The actual SELECT, UPDATE, INSERT or DELETE statement is not executed.

The EXPLAIN output is typically used to help in the process of constructing efficient queries.

Notes

Mimer SQL generates XML-based explain data. The EXPLAIN command can be used to read this data. Other ways are DbVisualizer Pro which has a graphical explain, and BSQL’s explain that returns the raw XML-based output. See Mimer SQL User's Manual, Mimer SQL Explain for more information about these alternatives, and how to read and interpret the explain data.

Example

SQL>explain

SQL&select cou.country, cur.currency from currencies cur, countries cou

SQL&where  cou.country in ('Belgium', 'Norway')

SQL&and    cou.currency_code = cur.code;

        ID      PARENT OPERATION

OPERATIONTYPE                              SCANORDER             ACC_COST

               HITS               VISITS

TABLE

ALIAS

INDEX

INDEXONLY

================================================================================

          1           0 select

-                                                  -                    6

                  2                    6

-

 

-

 

-

 

-

===

         2           1 inner join

-                                                  -                    6

                  2                    6

-

 

-

 

-

 

-

===

         3           2 index scan, table lookup

leading keys                                       1                    4

                  2                    4

countries

 

cou

 

cnt_country_exists

 

FALSE

===

         4           2 table lookup

unique                                             2                    1

                  1                    1

currencies

 

cur

 

SQL_PRIMARY_KEY_0000023475

 

-

===

 

                 4 rows found

 

The above output tells it’s a SELECT statement. SCANORDER 1 shows that the countries table is read first. The unique key cnt_country_exists index is used to scan the table. We have a condition on the first column in the index (cou.country = 'Belgium'), which is why the scan is leading keys.

The index cnt_country_exists has both the country column and the primary key code column. The VISITS count is 4 because two rows are read in the index, and two rows from the base table. This will result in a HITS count of 2 rows.

The join node contains the cost of processing the two tables.

When there are no temporary tables involved the cost is equal to the total number of visits.

Standard Compliance