Устранение таблиц в других базах данных

Помимо MariaDB, устранение таблиц используется в Microsoft SQL Server 2005/2008 и Oracle 11g. Из этих двух Microsoft SQL Server 2005/2008, похоже, имеет наиболее продвинутую реализацию. Подтверждено, что Oracle 11g использует устранение таблиц, но не в той же степени.

Чтобы сравнить эти две системы, рассмотрим следующий запрос:

select
 A.colA
from
 tableA A
left outer join
 tableB B
on
 B.id = A.id;

Используя A в качестве левой таблицы, мы гарантируем, что запрос вернет как минимум столько строк, сколько в этой таблице. Для строк, где условие объединения (B.id = A.id) не выполняется, выбранный столбец (A.colA) по-прежнему будет содержать исходное значение. Строка B.*, которая не была видна, будет содержать все NULL.

Однако результат запроса может фактически содержать больше строк, чем в таблице A, если в таблице B есть дубликаты столбца B.id. Если таблица A содержит строку [1, "val1"], а таблица B содержит строки [1, "other1a"], [1, "other1b"], то две строки будут соответствовать условию объединения. Единственный способ узнать, какой будет результат, — это фактически обработать обе таблицы во время выполнения.

Однако, предположим, что таблица B содержит строки, которые позволяют установить уникальное ограничение на столбец B.id, например, что часто является случаем с первичным ключом. В этом случае мы знаем, что получим ровно столько строк, сколько в таблице A, так как объединение с таблицей B не может ввести дубликаты. Кроме того, как и в примере запроса, если мы не выбираем столбцы из таблицы B, обращение к этой таблице во время выполнения не требуется. Мы можем удалить всю операцию объединения из плана выполнения.

Как SQL Server 2005/2008, так и Oracle 11g используют устранение таблиц в описанном выше случае. Давайте рассмотрим более сложный запрос, в котором Oracle терпит неудачу.

select
 A.colA
from
 tableA A
left outer join
 tableB B
on
 B.id = A.id
and
 B.fromDate = (
   select
     max(sub.fromDate)
   from
     tableB sub
   where
     sub.id = A.id
 );

В этом примере мы добавили ещё одно условие объединения, которое гарантирует, что мы выбираем только соответствующую строку из таблицы B с самым поздним значением fromDate. В этом случае таблица B будет содержать дубликаты столбца B.id, поэтому для обеспечения уникальности первичный ключ также должен содержать столбец fromDate. Другими словами, первичный ключ таблицы B — (B.id, B.fromDate).

Кроме того, поскольку подзапрос гарантирует, что мы выбираем только самое позднее значение B.fromDate для заданного B.id, мы знаем, что максимум одна строка будет соответствовать условию объединения. Опять же, объединение с таблицей B не может повлиять на количество строк в наборе результатов. Поскольку мы не выбираем столбцы из таблицы B, всю операцию объединения можно исключить из плана выполнения.

SQL Server 2005/2008 также использует устранение таблиц в этой ситуации. Мы не нашли способа заставить Oracle 11g использовать его для этого типа запросов. Такие запросы возникают в двух ситуациях. Либо когда у вас есть денормализованная модель, состоящая из таблицы фактов с несколькими связанными таблицами измерений, либо когда у вас есть сильно нормализованная модель, где каждый атрибут хранится в своей таблице. Пример с подзапросом часто используется при хранении исторических/версионных данных.

См. также

• Эта страница основана на следующей записи блога об устранении таблиц: http://s.petrunia.net/blog/?p=58