Особый случай 169

ду Al и А2. Примером такой таблицы комбинаций может быть таблица, содержащая сочетания актер-фильм для базы данных об истории кинематографа, связывающая таблицы Movies и Actors, отношения между которыми принадлежат виду многие ко многим . В такой таблице комбинаций естественно использовать первичный ключ, состоящий из двух частей, то есть из идентификаторов связанных таблиц - в нашем случае Movi e ID и Actor ID. Как это часто бывает, у таблицы комбинаций есть индекс по комбинации внешних ключей, указывающих на таблицы А2 и А1. Для нашего примера предположим, что порядок ключей в индексе таков: сначала внешний ключ, указывающий на А2, затем внешний ключ, указывающий на А1.

Рассмотрим стоимость доступа к каждой таблице в том плане, который я составил ранее, считая его решением для рис. 6.4. Можно найти низкую стоимость доступа для таблиц под М, затем намного большую стоимость доступа к М, так как вы получаете существенно больше строк из этой таблицы, чем из предыдущих, и доступ к этим строкам требует операций физического ввода-вывода. После обработки М база данных присоединяет такое же количество строк из А1 (так как для М нет фильтра), но стоимость каждой такой строки гораздо меньше, поскольку они полностью кэшированы. Затем фильтр в А1 сбрасывает количество строк обратно до небольшого количества для оставшихся в плане таблиц. Таким образом, стоимость практически полностью определяется стоимостью доступа к М, поэтому полезно было бы снизить именно эту составляющую.

Как это случается в подобных необычных случаях, вы находите возможность обойти внешний ключ в М, указывающий на А2, и перейти к указывающему на А1 внешнему ключу, который хранится в том же многостолбцовом индексе в М, даже не обращаясь к таблице М. Позже базе данных потребуется считать строки из самой таблицы М, чтобы получить внешний ключ, указываюший на A3 и, возможно, считать столбцы из списка SELECT запроса. Однако вы можете отложить обращение к этой таблице до того момента, как база данных пройдет фильтрованные таблицы А1 и С1. Таким образом, базе данных потребуется получить только 18 % тех строк из М (0,3 X 0,6, учитывая коэффициенты фильтрации 0,3 для А1 и 0,6 для С1), которые потребовалось бы считать, если бы база данных перешла к таблице М сразу же после обращения к индексу для М. Это существенно снижает стоимость запроса, так как стоимость считывания строк из таблицы М преобладает в данном специфическом случае.

Ни в одной базе данных разъединение считываний из индекса и считываний из таблицы не дается легко. Считывание из таблицы обычно автоматически следует сразу же за считыванием из индекса, даже если это не оптимально. Но Oracle позволяет вьшолнить фокус, решающий эту проблему, так как в SQL в Oracle можно явно указывать идентификаторы строк. В данном случае наилучший порядок соединения - (В4. С5. С4. А2. ВЗ. С2. СЗ. D1. D2. Ml. А1. В1. С1, МТ. A3. 85. Сб. В2).Здесь Ml - это индекс по М (FkeyТоА2, FkeyToAl), который в порядке соединения находится на исходном месте М. МТ - это таблица М, доступ к которой производится по плану позже через ROWID из Ml. МТ вставлена в порядок соединения после обработки фильтров для А1 и С1. Трюк заключается в том, чтобы обратиться к таблице М дважды в разделе FROM, один раз только для доступа к индексу и один раз для прямого соединения по ROWID, как показано далее. Имя индекса М(FkeyТоА2.FkeyToAl) - это M DoubleKeyIncl:

Select /*+ ORDERED INDEX(MI M DoubleKeyIncl) */ МТ.СоИ. МТ.Со12....

FROM В4. С5. С4. А2. 83. С2. СЗ. D1. 02. М Ml. Al. Bl. Cl. М МТ. A3. В5. Сб. В2 WHERE ...

AND А2.Pkey=MI.FKeyToA2 AND Al.Pkey=MI.FKeyToAl AND MI.ROWID=MT.ROWID AND...

Для подобного необычного случая два соединения с М действительно дешевле одного! Обратите внимание на две подсказки в этом варианте запроса.

Выражение ORDERED указывает, что таблицы должны соединяться в том порядке, в каком перечислены в разделе FROM.

Выражение INDEX(MI M DoubleKeyIncl) гарантирует использование правильного индекса в той точке порядка соединения, где вам требуется только доступ к индексу Ml.

Для правильного вьшолнения оставшейся части плана могут потребоваться другие подсказки. Также обратите внимание на необычное соединение идентификатор строки с идентификатором строки между Ml и МТ, и что единственные ссылки на Ml есть только в разделе FROM и в условиях раздела WHERE. Для этих ссылок требуются только данные (два внешних ключа и идентификатор строки), хранящиеся в индексе. Это очень важно - Oracle не переходит к таблице М сразу же после того, как считывает индекс по первичному ключу М только потому, что Ml обращается только к индексированным столбцам и идентификаторам строки, которые также хранятся в индексе. Все столбцы в операторе SELECT и везде в условиях раздела WHERE (например, соединение с A3, которое не показано) получены из МТ. Поэтому оптимизатор считает, что все столбцы, необходимые для Ml, уже находятся в индексе. Он считает это соединение исключительно индексным. Прямое соединение с МТ по ROWID выполняется позже в порядке соединения, и все столбцы таблицы М выбираются из МТ.

Однако только что описанный мной метод используется очень редко по нескольким причинам.

Комбинированные индексы из двух необходимых вам внешних ключей в правильном порядке встречаются редко.

Обычно корневая детальная таблица не увеличивает стоимость настолько, в относительных и абсолютных единицах, чтобы оправдать старания.

Выгоды редко оправдывают создание нового многостолбцового индекса, если он еще не существует.

Решение специфичной проблемы для других серверов

Если в разделе WHERE вы не можете указывать напрямую идентификаторы строк, может ли этот трюк сработать в той же форме? Единственная часть настройки, которая зависит от идентификаторов строк, - это соединение между Ml и МТ. Эти псевдонимы таблиц также можно соединить по полному первичному ключу. Стой-

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

Сложный пример

Теперь я продемонстрирую пример с менее очевидным порядком соединения, который потребует больше внимания ко всему облаку соединения. Вы обнаружите, что в случаях, подобных тому, который я описываю в этом разделе, порядок следования очередных таблиц для достижения наилучшего соединения может быть разбросан по всей диаграмме запроса. Рассмотрим задачу на рис. 6.9. Перед тем как читать дальше, попытайтесь решить ее самостоятельно.

М 0.001

A3 0.5

С10.6

С2 0.8 СЗ

▼

04 0.8 С5 0.2

С6 0.9

0108 D20? Рис. 6.9. Другая задача с тем же скелетом соединения

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

ПРИМЕЧАНИЕ-

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