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

Пример 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: Подзапросы для других ссылок не отображаются.