Запрещение использования неправильных индексов

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

Чтобы запретить использование индекса, создайте простейшее из возможных выражений при упоминании индексированного столбца. Например, вам нужно предотвратить использование индекса по Status Cocle для недостаточно селективного условия по закрытым заказам, так как с течением времени количество закрытых заказов будет все существенней превышать количество открытых заказов:

O.Status Code=CL

Так как Status Cocle - это столбец символьного типа, простейшим выражением, запрещающим использование индекса без изменения результатов, будет простое присоединение пустой строки к концу Status Cocle.

В Oracle и DB2 мы можем использовать выражение O.Status Cocle =CL В SQL Server будет применяться выражение О. Status Cocle+ = CL Для столбцов числового типа можно прибавить О, получив следующее условие:

O.Region ID+0=137

Во всех базах данных есть функции определенного вида, значение которых приравнивается к первому аргументу, если он не равен nul 1, а в противном случае - ко второму аргументу. В Oracle это функция NVLO. В SQL Server и DB2 это COALESCEC ). Если оба аргумента выражения оперируют данными одного и того же поля, функция всегда возвращает один и тот же результат в виде самого столбца, независимо от его типа. Таким образом, мы получаем простой способ отключения использования индекса независимо от типа столбца.

Для Oracle зто будет выражение: N\/L(O.Orcler Date.O.Orcler Date) = <3начение>

В DB2 и SQL Server для достижения той же цели придется использовать выражение: COALESCE(0. Orcler Date. 0. 0гйег 0а1е)=<3начение>

Условие для соединения, запрещающее индексированный доступ к О. Regi on ID (но не к R. Regi on ID) может выглядеть следующим образом: О.Regi on ID+0=R.Regi on ID

Используя универсальный подход, то же соединение можно записать так: NVL(О.Regi on ID.О.Regi on ID)=R.Regi on ID

Использование желаемого порядка соединения

Кроме непредумышленного запрещения использования индексов, есть еще два случая, когда бывает невозможно провести соединение в нужном порядке.

Использование внешних соединений.

Отсутствие избыточных условий соединения.

Внешние соединения

Рассмотрим запрос с внешним соединением в записи Oracle: SELECT ...

FROM Employees E. Locations L WHERE E.Location ID=L.Location IO(+)

или в новом варианте записи операторов:

SELECT ...

FROM Employees Е LEFT OUTER JOIN Locations L ON E.Location ID=L.Location ID

Этот запрос получает записи сотрудников и соответствующие им записи об их местоположении. Если для сотрудника не указано местоположение, то используется значение nul 1. Основываясь на этом запросе, легко понять, что он не может эффективно работать, начиная с таблицы Locations и переходя к Employees, так как требуются даже те сотрудники, для которых местоположение не указано. Представьте случай, когда этот запрос является лишь шаблоном, к которому приложение добавляет условия, зависящие от критерия поиска, заданного конечным пользователем. Если пользователю требуются сотрудники с определенным местоположением, приложение может создать такой запрос: SELECT ...

FROM Employees Е LEFT OUTER JOIN Locations L

DN E.Location ID=L.Location ID WHERE L.Description=Headquarters

В случае внешнего соединения в порядке от Employees к Locations полю L.Description будет присвоено значение null и условие для L.Description будет ложным. Только внутреннее соединение вернет строки, которые отвечают ограничениям для L. Descri pti on, поэтому имеет смысл изменить порядок соединения на противоположный, от Locations к Employees. Однако существование внешнего соединения часто не позволяет автоматическому оптимизатору выбрать обратный порядок для внешнего соединения, поэтому это соединение необходимо явно сделать внутренним, чтобы добиться нужного порядка: SELECT ...

FROM Employees Е INNER JOIN Locations L

ON E.Location ID=L.Location ID WHERE L.Description-=Headquarters

Отсутствие избыточных условий соединения

Обычно в SQL-запросе между любым количеством таблиц количество соединений на единицу меньше количества таблиц. Например, между тремя таблицами вы ожидаете увидеть два соединения. Иногда запрос позволяет использовать дополнительное, избыточное соединение. Например, если у вас есть таблица Addresses, в которой хранятся все адреса, важные для компании, то между ней и таблицей Locations может существовать отношение вида один к нулю или один к одно-

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

FROM Employees Е. Locations L. Addresses A WHERE E.Location ID=L.Location ID

AND E.Location ID=A.Address ID

AND A.ZIP Code=95628

По свойству транзитивности (если a=b и b=c, то а=с) условие L.Location ID=A. Address ID должно быть истинным для всех строк, которые вернет запрос. Однако это условие не указано в запросе явно, и не все базы данных смогут вывести его и подставить в запрос. В этом случае лучшим планом будет взять все адреса с данным почтовым индексом и сразу же соединить их с Locati ons, чтобы отбросить все адреса кроме одного или двух, соответствующих адресам компании, а затем соединить с Employees. Так как этот порядок соединения требует отсутствующего условия соединения, позволяющего использовать индексированный путь от Addresses к Locations, необходимо явно указать отсутствующее условие соединения:

FROM Employees Е, Locations L. Addresses A WHERE E.Location ID=L.Locati on ID

AND E.Location ID=A.Address ID

AND L.Location ID=A.Address ID

AND A.ZIP Code=95628

Так как вы не хотите использовать соединение от Addresses к Employees напрямую, можно также удалить, если необходимо, избыточное условие соединения E.Location ID=A.Address ID, запретив нежелательную операцию соединения.

Запрещение соединения в неправильном порядке

Задание соединений в желаемом порядке с применением описанных способов предотвращения использования неправильных индексов также поможет запретить проводить соединения в нежелательном порядке. Что следует делать, если вы хотите, чтобы база данных создала некое соединение в определенном порядке не слишком рано в плане вьшолнения? Нельзя запрещать использовать индекс, поскольку однажды он может понадобиться, просто не очень скоро. Рассмотрим два соединения, в которых вы хотите, чтобы запрос начал со считывания из Т1, затем соединил результат с Т2, и затем с ТЗ:

... AND Tl.Key2 ID=T2.Key2 ID AND Tl.Key3 ID=T3.Key3 ID ...

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

... AND Tl.Key2 ID=T2.Key2 ID

AND Tl.Key3 ID+0*T2.Key2 ID=T3.Key3 ID ...

Мы знаем, что второй вариант логически эквивалентен первому. Однако база данных находит в левой части второго соединения выражение, которое зависит от Т1 и Т2 (не понимая, что значения из Т2 не могут изменить результат), поэтому она не будет пытаться выполнить соединение с ТЗ до соединения с Т2.