Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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 is 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, Appendix A, 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

Standard
Compliance
Comments

Mimer SQL extension
The EXPLAIN command is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Phone: +46 18 780 92 00
info@mimer.com
Mimer SQL Documentation TOC PREV NEXT INDEX