Background documentationEXPLAIN Statement for Joins Locate this document in the navigation structure

 

If you use the EXPLAIN statement for an SQL statement that contains joins, the following information will appear:

  • Order in which the database system processes the tables when executing the SELECT statement

  • Whether the database system can access the rows of a new table directly from the values in the join columns of the old temporary results table, or whether it can only access them via an inversion.

  • The search strategy the database system uses to search each new table if it cannot access the rows of this table directly or using an inversion.

Example

The following example uses the demo database DEMODB with the demo data in the schema HOTEL, see Concepts of the Database System, Objects in the Schema HOTEL.

EXPLAIN Statement

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 for CITY: TABLE SCAN

  • Search strategy 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 SORT COLUMN)

RESULT IS COPIED COSTVALUE IS

13

The database system accesses the table CITY and uses the search strategy TABLE SCAN. For the join with the table CUSTOMER it chooses the JOIN VIA KEY RANGE strategy using the ZIP column.

More Information

You can find additional simple example SQL statements in the SQL Tutorial, Joins: Information From Several Tables.