Show TOC

EXPLAIN Statement for SELECT Statements with SubqueriesLocate this document in the navigation structure

Use

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

TABLENAME

COLUMN_OR_INDEX

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

TABLENAME

COLUMN_OR_INDEX

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.