EXPLAIN Statement for Joins

The following information is displayed when the EXPLAIN statement is used for joins.

?     The order in which the tables are processed when the SELECT statement is executed

?     Whether the rows of a new table can be accessed directly from the values in the join columns of the old temporary results table, or whether they can be accessed through an inversion

?     The search strategy used to search the new table, if the rows of this table cannot be accessed directly or using an inversion

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

EXPLAIN
SELECT customer.cno, customer.title, customer.name,
       customer.zip, city.name, city.state, customer.address
  FROM   hotel.customer, hotel.city
  WHERE  customer.zip = city.zip

Join tables: CUSTOMER and CITY
Qualification:
customer.zip = city.zip
Search strategy used for CITY: TABLE SCAN
Search strategy used for CUSTOMER: JOIN VIA KEY RANGE

Result of the EXPLAIN Statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

CITY

TABLE SCAN

1

CUSTOMER

JOIN VIA KEY RANGE

1

TABLE TEMPORARY SORTED

ZIP

(USED KEY COLUMN)

RESULT IS COPIED
COSTVALUE IS


13

The join processing begins with the access to the table CITY. This is performed with the search strategy TABLE SCAN.

For the join transfer to the table CUSTOMER with the strategy JOIN VIA KEY RANGE, the column ZIP is used.

For the join, a result set is always generated (RESULT IS COPIED).

You will find additional simple example SQL statements in the SQL Tutorial, Joins: Information from Multiple Tables.

See also:

Search strategies for Joins

List of All Search Strategies

Reference Manual, EXPLAIN Statement (explain_statement), JOIN Predicate (join_predicate)