Too Early Materialization of Subquery in FROM Clause Causes Bad Execution Plan (Doc ID 1916035.1)

Last updated on FEBRUARY 07, 2017

Applies to:

MySQL Server - Version 5.1 to 5.7 [Release 5.1 to 5.7]
Information in this document applies to any platform.

Symptoms

In some SQL commands delayed subquery materialization may result in less examined rows and faster execution times. E.g. command below will retrieve all rows from table a, and then apply condition from WHERE clause:

select col1, col2 from (select col1, col2, col3 from a) a1 where col3 = 1;

In particular, Optimizer might be smart enough to transform it into SQL command:

select col1, col2 from a where col3 = 1;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms