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)