EXPLAIN Statement for SELECT Statements with Subqueries

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 result rows. However, the number of result 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.

SQL Statement With Subquery (I)

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

Result of the EXPLAIN Statement
TABLENAME	STRATEGY	PAGECOUNT
`HOTEL`	`TABLE SCAN`	`8`
`CITY`	`TABLE SCAN`	`11`
	`RESULT IS COPIED COSTVALUE IS`	`101`

This SQL statement selects those city names after which a hotel is named. Because a suitable index does neither exist 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.

SQL Statement With Subquery (II)

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

Result of the EXPLAIN Statement
TABLENAME	STRATEGY	PAGECOUNT
`RESERVATION`	`TABLE SCAN`	`24`
`CUSTOMER`	`NO STRATEGY NOW (ONLY AT EXECUTION TIME)`
	`RESULT IS COPIED COSTVALUE IS`

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 selected.

More Information

SUBQ CONDITION FOR KEY

SUBQ CONDITION FOR INDEX

Analyzing Search Strategies Using the EXPLAIN Statement

List of All Search Strategies

SQL Reference Manual, Subquery (subquery)

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