Background documentationEXPLAIN Statement for SELECT Statements with Subqueries Locate this document in the navigation structure

 

To determine the cost of search strategies where the value of a column is compared with the hit rows of a subquery, the SQL Optimizer must know the number of hit rows. However, the number of hit rows is not known until the subquery has been processed.

For a SELECT statement with subqueries, the EXPLAIN statement determines the possible search strategies, but does not execute these subqueries. Therefore, the search strategy NO STRATEGY NOW (ONLY AT EXECUTION TIME) is usually displayed for the outer SQL statement.

Example

The following examples use the demo database DEMODB with the complete demo data in the schema HOTEL, (see Concepts of the Database System, Objects in the Schema HOTEL.

Example Example

EXPLAIN SELECT name

  FROM hotel.city

    WHERE name IN (SELECT name FROM hotel.hotel)

  • Qualification: name IN (SELECT name FROM hotel.hotel)

  • Primary key of CITY table: zip

  • Primary key of table HOTEL: cno

  • Indexes via qualified columns: No search strategy used

  • Search strategy for outer SELECT statement: TABLE SCAN

  • Search strategy for subquery: TABLE SCAN

End of the example.

This SQL statement selects those city names after which a hotel is named. Since a suitable index exists neither for the CITY table nor for the HOTEL table and the SELECT statements were not restricted to primary key columns, the database system uses the TABLE SCAN strategy for both SELECT statements.

Result of the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

HOTEL

TABLE SCAN

8

CITY

TABLE SCAN

11

RESULT IS COPIED COSTVALUE IS

101

Example Example

EXPLAIN SELECT cno, name

  FROM hotel.customer

    WHERE cno = ANY(SELECT cno FROM hotel.reservation WHERE arrival > '2005-01-01')

  • Qualification: cno = ANY(SELECT cno FROM hotel.reservation WHERE arrival > '2005-01-01')

  • Primary key of CUSTOMER table: cno

  • Primary key of RESERVATION tabel: rno

  • Indexes via qualified columns: No search strategy used

  • Search strategy for outer SELECT statement: NO STRATEGY NOW (ONLY AT EXECUTION TIME)

  • Search strategy for subquery: TABLE SCAN

End of the example.

The SQL statement selects those customers that have a reservation after a certain date. For the RESERVATION table, the database system selects the TABLE SCAN strategy. For the external SELECT statement, the NO STRATEGY NOW (ONLY AT EXECUTION TIME) strategy is displayed.

Result of the EXPLAIN statement

TABLENAME

COLUMN_OR_INDEX

STRATEGY

PAGECOUNT

RESERVATION

TABLE SCAN

24

CUSTOMER

NO STRATEGY NOW (ONLY AT EXECUTION TIME)

RESULT IS COPIED COSTVALUE IS

You will find additional simple example SQL statements in the SQL Tutorial, Subquery: Inner Queries.

More Information