Я настраиваю запрос на SQL Server 2005. Обратите внимание, что настоящий вопрос находится в конце. У меня есть следующий запрос, и pto, и ph имеют около 30 миллионов строк. Первоначально запрос выполняется очень медленно (3 минуты). Поэтому я добавил два индекса на pto и ph соответственно.
SELECT
MAX(ph.txn_date_time)
FROM
pto AS pto WITH (NOLOCK)
INNER JOIN ph AS ph WITH (NOLOCK) ON ph.receipt_id = pto.receipt_id
WHERE
pto.subtype = 'ff'
AND pto.Units_No > 0
AND ph.branch_id = 5
CREATE NONCLUSTERED INDEX [IX_pto_subTypeUnitReceipt] ON [dbo].[pto]
(
[SUBTYPE] ASC,
[Units_No] ASC,
[RECEIPT_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Indexes]
CREATE NONCLUSTERED INDEX [IX_ph_branchReceiptTxn] ON [dbo].[ph]
(
[BRANCH_ID] ASC,
[RECEIPT_ID] ASC,
[TXN_DATE_TIME] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Indexes]
Теперь запрос выполняется за 350 мс. Здорово. План выполнения также очень прост: он использует созданный индекс из двух таблиц и выполнял хэш-соединение в столбце receive_id, а затем Stream Aggregate для выполнения MAX (ph.txn_date_time). Таким образом, каждый столбец в запросе покрывается двумя добавленными индексами.
Возникает вопрос, почему он использовал хеш-соединение в столбце Receive_id? Я имею в виду, что, поскольку RECEIPT_ID в обоих индексах отсортированы, оптимизатор должен был использовать соединение слиянием. Чтобы понять, почему я изменил первый индекс на ниже (поместите RECEIPT_ID перед Units_No).
CREATE NONCLUSTERED INDEX [IX_pto_subTypeUnitReceipt] ON [dbo].[pto]
(
[SUBTYPE] ASC,
[RECEIPT_ID] ASC,
[Units_No] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Indexes]
И теперь я вижу объединение слиянием в столбце RECEIPT_ID. Запрос также выполняется за 170 мс. Теперь очевидно, что оптимизатор считает, что RECEIPT_ID в обоих индексах отсортирован, поэтому используется объединение слиянием. Но я не понимаю, почему в первом случае так не думает?
