Returns explain information for a statement.
Explain information for a
DELETEstatement is returned as a result set.
DELETEstatement is not executed.
EXPLAINoutput is typically used to help in the process of constructing efficient queries.
Mimer SQL generates xml-based explain data. The
EXPLAINcommand 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.
ExampleSQL>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
SCANORDER1 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
VISITScount is 4 because two rows are read in the index, and two rows from the base table. This will result in a
HITScount 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.
Mimer Information Technology AB
Phone: +46 18 780 92 00