Mimer SQL Engine Explain - Mimer Developer

How is a complex join evaluated? In which order are the tables accessed? Is that index really useful?

The answer to these question can be found using the Mimer SQL Explain facility.

There are various options and possibilities to get to the explain data, options that will be introduced here. Basically it depends on which type of Mimer SQL database server that is used, and how the environment is equipped.

Please note, as explained below, this article focuses on how to use the explain functionality provided for the Mimer SQL Engine database server, meaning Mimer SQL version 10 and earlier. See Explain for Mimer SQL 11 if using a Mimer SQL Experience database server.

When using the Mimer SQL Experience database server

Here follows the different ways to get the execution path information when using a Mimer SQL Experience database server:

From any SQL browser the keyword EXPLAIN can be used in front of the SQL statement. When executed, this command will fill a result set with the explain data.
From the Mimer BSQL command line tool the SET EXPLAIN ON; command can be used. When the explain facility is enabled in this way, the explain data will be displayed in an XML output format.
A convenient graphical explain utility is available if using the DbVisualizer Pro tool. Please note that this is an upgrade from the DbVisualizer product that is bundled with Mimer SQL.

Note! See the article called Explain for Mimer SQL 11 for further details on the explain functionality for the Mimer SQL Experience database server and how the explain data should be interpreted.

When using the Mimer SQL Engine database server

When using a Mimer SQL Engine database server the Explain facility in BSQL should be used. This feature is enabled using the SET EXPLAIN ON; command. This explain functionality is further described in this article.

Description

The Explain facility for a Mimer SQL Engine database server is activated by giving the BSQL statement:

SQL>set explain on;

If the Explain facility has been activated, all entered DML statements will return an execution plan in addition to being executed. Typical DML statements are SELECT, INSERT, UPDATE and DELETE. You can use views in your DML statement when explain mode is active. In that case Explain will show the operations on the tables used in the view. If you wish to avoid statements being executed when using the explain facility, you can use the set execute statement:

SQL>set execute off;
SQL>set execute on;

To return to the normal execution mode in BSQL use:

SQL>set explain off;

The following operations can be shown by the Explain facility:

Direct key lookup – Search primary key for exactly one record.
Direct index key lookup – Search unique index for exactly one record
Key lookup – Search primary key for range of values
Index lookup – Search index for range of values
Key scan – Search primary key, not using leading columns
Index scan – Search index, not using leading columns
Sequential read – Read all records from permanent or temporary table
Index sequential read – Read all records using index
Insert – Insert record into permanent or temporary table
Insert, aggregate – Insert record into temporary table and update aggregate function result
Update – Update record in permanent table
Delete – Delete record from permanent table
Call – Calling a user defined function routine or trigger

Note! If the “set execute off;” command is used, DDL statements are inhibited as well. For example, creating an index may seem to succeed, but it has not.

Environment

This article will show some examples of Explain output with some discussions of how to interpret this output. For these examples the following tables will be used.

CREATE TABLE items (
   item_id INTEGER,
   product_id INTEGER,
   release_date DATE,
   price DECIMAL(10,2));

CREATE TABLE orders (
   order_id integer,
   order_no VARCHAR(16),
   customer_id INTEGER,
   order_date DATE);

CREATE TABLE order_items (
   order_id INTEGER,
   seq_no INTEGER,
   item_id INTEGER,
   quantity SMALLINT);

These tables are simplified versions of some tables in the example database.

Example

Let’s get on with some examples:

 SQL>set explain on;
 SQL>select * from items where item_id = 12;

 L1:
 Sequential read MIMER_STORE.ITEMS , end of table goto end
 compare, no hit goto L1
 Record found, goto L1
 end:

As can be seen the output is in the form of a flow chart. The table ITEMS will be read record by record. For each record the database server will do a compare to see if the record matches the WHERE criteria. If the record does not match the criteria, the execution is continued at label L1, i.e. reading the next record. If the record matches the criteria the record is chosen and the execution will resume at label L1. The program will end when all rows in the table has been read. Sequential read operations are things to look out for when analyzing a query, especially when joins are involved.

 SQL>select * from orders as o , order_items as oi
 SQL& where o.order_id = 12
 SQL& and o.order_id = oi.order_id;

 L1:
 Sequential read MIMER_STORE.ORDERS , end of table goto end
 compare, no hit goto L1
 L2:
 Sequential read MIMER_STORE.ORDER_ITEMS , end of table goto L1
 compare, no hit goto L2
 Record found, resume at L2
 end:

The above will be a very slow query indeed. For each row in ITEMS all rows in order_items will be read. Even though if each table has a measly 1000 records the query will examine 1 million records.
Let’s add some keys to the tables…

 SQL>alter table items add constraint item_pk primary key(item_id);
 SQL>alter table orders add constraint orders_pk primary key(order_id);
 SQL>alter table order_items add constraint io_pk primary key(order_id,item_id);
 SQL>alter table order_items add constraint io_fk1 foreign key(item_id)
 SQL&references items;
 SQL>alter table order_items add constraint io_fk2 foreign key(order_id)
 SQL&references orders;

…and compare the execution plan for the join query above.

 SQL>select * from orders as o , order_items as oi
 SQL& where o.order_id = 12
 SQL& and o.order_id = oi.order_id;

 Direct key lookup MIMER_STORE.ORDERS , end of table goto end
 L1:
 Key lookup MIMER_STORE.ORDER_ITEMS , end of table goto end
 compare, no hit goto L1
 Record found, goto L1
 end:

In this case it is possible to retrieve the desired record from the ORDERS table directly by using the primary key. When retrieving records from the ORDER_ITEMS table, the primary key will be used. As we have a condition on the first column in the primary key, a key lookup will be done. This means that only the values in the range that matches the value from the order table will be read. This is the best case when having a one to many relation.
Explain will also give information if any index is used within a query.

 SQL>select order_id,item_id from order_items where item_id = 12;

 L1:
 Index scan using index MIMER_STORE.IO_FK1, end of table goto end
 compare, no hit goto L1
 Record found, goto L1
 end:

One thing that can be noted is that the base table will not be read! As all columns we are retrieving are present in the index there is no need to access the base table, all data is fetched from the index. In some cases it might be a benefit to add columns to an index just to make use of this feature. Indexes created internally for maintaining foreign key and unique constraints will be used by the database server as well as explicitly created indexes. An index scan operation is done when there is no condition on the leading column in the index.
Another thing to look out for when analyzing queries is the use of temporary tables. Temporary tables are used by the database server for sorting, eliminating duplicates and handling aggregations in conjunction with GROUP BY.

 SQL>select 'orders', order_id, order_date from orders
 SQL& union
 SQL&select 'items', item_id, release_date from items;

 L1:
 Sequential read MIMER_STORE.ORDERS , end of table goto L2
 Insert into temporary table (1)
 goto L1
 L2:
 Sequential read MIMER_STORE.ITEMS , end of table goto L3
 Insert into temporary table (1)
 goto L2
 L3:
 Sort temporary table (1)
 L4:
 Sequential read temporary table (1), end of table goto end
 Record found, goto L4
 end:

In this case the database server will use a temporary table for eliminating duplicates. If multiple temporary tables are used, these are distinguished by being assigned a sequential number, which is shown in parentheses.
In the above query there can be no duplicates, so using union all will be more efficient.

 SQL>select 'orders', order_id, order_date from orders
 SQL& union all
 SQL&select 'items', item_id, release_date from items;

 L1:
 Sequential read MIMER_STORE.ORDERS , end of table goto L2
 Record found, goto L1
 L2:
 Sequential read MIMER_STORE.ITEMS , end of table goto end
 Record found, goto L2
 end:

As can be seen there is no need for a temporary table in this case.
Another area where temporary tables are used is sorting.

 SQL>select order_id,item_id,quantity from order_items order by quantity;

 L1:
 Sequential read MIMER_STORE.ORDER_ITEMS , end of table goto L2
 Insert into temporary table (1)
 goto L1
 L2:
 Sort temporary table (1)
 L3:
 Sequential read temporary table (1), end of table goto end
 Record found, goto L3
 end:

As the column quantity is not part of any index or key, a temporary table is needed. If we sort on item_id the following happens:

 SQL>select order_id,item_id from order_items order by item_id desc;

 L1:
 Index sequential read using index MIMER_STORE.IO_FK1,
 end of table goto end
 Record found, goto L1
 end:

As the data can be retrieved in the correct order by using the index there is no need for a temporary table. Even if the sort order is descending, the index will be used as the database server will read the index records in reverse order.

Links

For details, see the Mimer SQL Documentation Set.

Note! Again, as mentioned above, for a Mimer SQL Experience database server, see the appendix called Mimer SQL Explain in the User’s Manual part of the documentation set, or read the article called Explain for Mimer SQL 11.

Graphic Element - Cube