Оптимизатор может обрабатывать ссылки на производные таблицы, используя два подхода (которые также применяются к ссылкам на представления и общие табличные выражения):

Пример 1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

При слиянии производной таблицы derived_t1, запрос выполняется аналогично:

SELECT * FROM t1;

Пример 2:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

При слиянии производной таблицы derived_t2, запрос выполняется аналогично:

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

При материализации, derived_t1 и derived_t2 обрабатываются как отдельные таблицы в своих соответствующих запросах.

Оптимизатор обрабатывает производные таблицы, ссылки на представления и общие табличные выражения одинаково: он избегает ненужной материализации, когда это возможно, что позволяет продвигать условия из внешнего запроса в производные таблицы и создаёт более эффективные планы выполнения. (Для примера см. Раздел 10.2.2.2, «Оптимизация подзапросов с материализацией».)

Если слияние приведёт к внешнему блоку запроса, который ссылается на более чем 61 базовые таблицы, оптимизатор выбирает материализацию вместо этого.

Оптимизатор распространяет предложение ORDER BY в производной таблице или ссылке на представление во внешний блок запроса, если все эти условия выполняются:

В противном случае оптимизатор игнорирует предложение ORDER BY.

Существуют следующие способы повлиять на то, пытается ли оптимизатор объединить производные таблицы, ссылки на представления и общие табличные выражения во внешний блок запроса:

Если оптимизатор выбирает стратегию материализации вместо слияния для производной таблицы, он обрабатывает запрос следующим образом:

Рассмотрите следующее утверждение EXPLAIN для запроса SELECT, который содержит производную таблицу:

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

Оптимизатор избегает материализации производной таблицы, откладывая её до тех пор, пока результат не понадобится во время выполнения SELECT. В данном случае запрос не выполняется (потому что он встречается в утверждении EXPLAIN), поэтому результат никогда не понадобится.

Даже для выполняемых запросов отложенная материализация производной таблицы может позволить оптимизатору вообще избежать материализации. В этом случае выполнение запроса ускоряется на время, необходимое для выполнения материализации. Рассмотрим следующий запрос, который объединяет результат производной таблицы с другой таблицей:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

Если оптимизация обрабатывает t1 сначала, и предложение WHERE даёт пустой результат, объединение обязательно будет пустым, и производную таблицу не нужно материализовывать.

В случаях, когда для производной таблицы требуется материализация, оптимизатор может добавить индекс к материализованной таблице, чтобы ускорить доступ к ней. Если такой индекс позволяет выполнить доступ ref к таблице, это может значительно сократить объём считываемых данных во время выполнения запроса. Рассмотрим следующий запрос:

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

Оптимизатор создаёт индекс по столбцу f1 из derived_t2, если это позволит использовать доступ ref для плана выполнения с наименьшими затратами. После добавления индекса оптимизатор может рассматривать материализованную производную таблицу так же, как обычную таблицу с индексом, и таким же образом получает выгоду от созданного индекса. Накладные расходы на создание индекса незначительны по сравнению с затратами на выполнение запроса без индекса. Если доступ ref приведёт к более высоким затратам, чем какой-либо другой метод доступа, оптимизатор не создаёт индекс и ничего не теряет.

В выходных данных отслеживания оптимизатора объединённая производная таблица или ссылка на представление не отображается как узел. В плане верхнего запроса отображаются только лежащие в его основе таблицы.

То, что верно для материализации производных таблиц, также верно для общих табличных выражений (CTE). Кроме того, следующие соображения относятся конкретно к CTE.

Если CTE материализуется запросом, она материализуется один раз для запроса, даже если запрос ссылается на неё несколько раз.

Рекурсивное CTE всегда материализуется.

Если CTE материализуется, оптимизатор автоматически добавляет соответствующие индексы, если он оценивает, что индексация может ускорить доступ к CTE со стороны верхнего уровня. Это аналогично автоматической индексации производных таблиц, за исключением того, что если на CTE ссылаются несколько раз, оптимизатор может создать несколько индексов, чтобы ускорить доступ каждой ссылки наиболее подходящим образом.

Подсказки оптимизатора MERGE и NO_MERGE могут быть применены к CTE. Каждая ссылка на CTE в утверждении верхнего уровня может иметь свою собственную подсказку, что позволяет выборочно объединять или материализовывать ссылки на CTE. Следующее утверждение использует подсказки, чтобы указать, что cte1 должно быть объединено, а cte2 должно быть материализовано:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Предложение ALGORITHM для CREATE VIEW не влияет на материализацию для любого предложения WITH предшествующего утверждению SELECT в определении представления. Рассмотрим это утверждение:

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

Значение ALGORITHM влияет только на материализацию SELECT, а не на предложение WITH.

Как уже упоминалось ранее, CTE, если материализован, материализуется один раз, даже если к нему обращаются несколько раз. Для указания однократной материализации вывод трассировки оптимизатора содержит одно вхождение creating_tmp_table плюс одно или более вхождений reusing_tmp_table.

CTE похожи на производные таблицы, для которых узел materialized_from_subquery следует за ссылкой. Это верно для CTE, к которому обращаются несколько раз, поэтому нет дублирования узлов materialized_from_subquery (что могло бы создать впечатление, что подзапрос выполняется несколько раз и привести к излишне подробному выводу). Только одна ссылка на CTE имеет полный узел materialized_from_subquery с описанием плана подзапроса. Другие ссылки имеют уменьшенный узел materialized_from_subquery. Та же идея применяется к выводу EXPLAIN в формате TRADITIONAL: Подзапросы для других ссылок не отображаются.