WHERE и JOIN порядок работы

Мой вопрос похож на этот порядок операций SQL, но с небольшим завихрением, поэтому я думаю, что справедливо спросить.

Я использую Teradata. И у меня есть 2 таблицы: table1, table2.

table1 имеет только столбец id.
table2 имеет следующие столбцы: id, val

Возможно, я ошибаюсь, но я думаю, что эти два утверждения дают одинаковые результаты.

Заявление 1.

SELECT table1.id, table2.val
FROM table1
INNER  JOIN table2
ON table1.id = table2.id
WHERE table2.val<100

Заявление 2.

SELECT table1.id, table3.val
FROM table1
INNER JOIN (
    SELECT *
    FROM table2
    WHERE val<100
)  table3
ON table1.id=table3.id

Мои вопросы: оптимизатор запросов будет достаточно умен, чтобы - сначала выполнить предложение WHERE, затем JOIN позже в Statement 1
- знать, что таблица 3 фактически не нужна в заявлении 2

Я новичок в SQL, поэтому, пожалуйста, просветите меня, если я что-то не понимаю.

Ответы

Ответ 1

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

вы не говорите, какую базу данных, но вот несколько способов:
MySql EXPLAIN
SQL Server SET SHOWPLAN_ALL (Transact-SQL)
Планирование Oracle EXPLAIN

что объясняется в teradata?
Teradata Захват и сравнение планов быстрее с оформлением журнала Visual Explain и XML

Ответ 2

В зависимости от доступности статистики и индексов для рассматриваемых таблиц механизм перезаписи запроса в оптимизаторе может или не может сканировать Table2 для записей, где val < 100 перед сканированием Table1.

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

SELECT table1.id, table3.val
FROM table1
INNER JOIN (
    SELECT table2.id, tabl2.val
    FROM table2
    WHERE val<100
    GROUP BY 1,2
)  table3
ON table1.id=table3.id

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

Механизм перезаписи запросов постоянно обновляется с одного выпуска до следующего, а сведения о том, как он работает, можно найти в Руководство по обработке транзакций SQL для Teradata 13.0.

Ответ 3

Если мне что-то не хватает, зачем вам нужно Table1?

Просто запрос Table2

Select id, val  
From table2  
WHERE val<100

или вы используете строки в таблице1 в качестве фильтра? т.е. имеет ли таблица1 только подмножество идентификаторов в таблице2?

Если это так, тогда это тоже будет работать...

 Select id, val  
 From table2  
 Where val<100 
   And id In (Select id 
              From table1)

Но чтобы ответить на ваш вопрос, да, оптимизатор запросов должен быть достаточно интеллектуальным, чтобы определить лучший порядок, в котором нужно выполнить шаги, необходимые для перевода ваших логических инструкций в физический результат. Он использует статистические данные, которые база данных поддерживает в каждой таблице, чтобы определить, что делать (какой тип логики объединения использовать, например), как и любой порядок выполнения операций, чтобы минимизировать дисковые операции и затраты на обработку.

Ответ 4

Q1. сначала выполните предложение WHERE, затем JOIN позже в Statement 1

Дело в том, что если вы переключите порядок внутреннего соединения, то есть таблицу2 INNER JOIN table1, тогда я думаю, что предложение WHERE может быть обработано до операции JOIN на этапе подготовки. Тем не менее, я думаю, даже если вы не измените исходный запрос, оптимизатор должен иметь возможность переключать свой порядок, если он считает, что операция соединения будет слишком дорогостоящей с извлечением всей строки, поэтому сначала будет применяться WHERE. Просто думаю.

Q2. что таблица 3 на самом деле не нужна в заявлении 2

Teradata будет интерпретировать ваш второй запрос таким образом, чтобы производная таблица была необходима, поэтому она будет поддерживать обработку таблицы 3.