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

 

If you use the EXPLAIN statement on an SQL statement containing 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

Example Example

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

End of the example.

The database system accesses the table CITY and processes this with the search strategy TABLE SCAN. For the join with the table CUSTOMER used with the JOIN VIA KEY RANGE strategy, the database system uses the ZIP column.

Result for 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

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

More Information