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, 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. |