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

1. Используя индекс на фильтре для Е, база данных должна начать с получения 0,1 x С строк из ведущей таблицы Е.

2. Следуя вложенным циклам по направлению к главной таблице D, главный коэффициент соедршения показывает, что база данных получит 0,1 х С х 0,98 строк из D.

3. Складывая результаты щагов 1 и 2, получим общее количество строк из обеих таблиц: (0,1 х С) + (0,1 х С х 0,98).

4. Вынесем за скобки С и 0,1 из обоих выражений. Общее колтество строк, полученных базой данных, равно С х (0,1 х (1 -t- 0,98)) или 0,198 х С.

Начиная с противоположного конца соединения, также выразим стоимость через С, но зададим через С размер D. Зная размер D, вьфаженный через С, стоимость запроса в терминах С вычислим следующим образом.

1. Для каждой строки из D база данных находит приблизительно 20 строк в Е, что показывает детальный коэффициент соединения. Однако только 98 % строк в Е в принципе подходят к строкам из D, поэтому общее количество строк С должно быть в 20/0,98 больще, чем количество строк, полученных из D. Следовательно, количество строк, полученных из D, равно С х 0,98/20.

2. Используя индекс для получения только отфильтрованных строк из D, мы получим половину строк из ведущей таблицы, или С х 0,5 х 0,98/20.

3. При выполнении соединения с Е база данных получает в 20 раз больше строк из этой таблицы, то есть 20 х С х 0,5 х 0,98/20 или С х 0,5 х 0,98.

4. Складывая количества строк из двух таблиц и вынося за скобки общие множители, получим общую стоимость: С х (0,5 х 0,98 х ((1 / 20) +1)) или 0,5145 х С.

В действительности абсолютная стоимость нас не интересует. Нам важна относительная, поэтому наршучший план можно выбрать исходя из того, что, каково бы ни было С, 0,198 x С всегда будет меньше 0,5145 х С. В диаграмме запроса есть вся информация, которую вам необходимо знать: что план, начинающийся с Е и присоединяющий D, будет намного быстрее (приблизительно в 2,6 раз), чем план, начинающийся с D и присоединяющий Е. Если вы найдете другие, близкие значения, то стоит подумать о том, что оценка стоимости на основе количества строк была не очень верна, но об этом я расскажу позже. Самое главное, что следует запомнить, так это то, что диаграмма запроса отвечает на основные вопросы, относящиеся к оптимизации запроса. Вам просто необходим эффективный способ использования диаграмм запросов для понимания и настройки сложных запросов.

Создание диаграмм запросов

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

1. Начните с произвольно выбранного псевдонима таблицы из раздела FROM и поместите его в середину пустой страницы. Эту таблицу я буду называть центральной таблицей, подразумевая, что она будет текущей точкой, начиная с которой мы будет добавлять дальнейшие элементы в диаграмму запроса. Для нашего примера в качестве начальной точки я выберу псевдоним Е, просто потому что он первым встречается в запросе.

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

В нашем примере соединения с первичным ключом таблицы Empl oyees, предположительно EmployeelD, нет. Если вы хотите убедиться, что Department ID - это достаточно хорошее имя для первичного ключа Empl oyees, то можете проверить объявленные гспючи и уникальные индексы таблицы. Если вы подозреваете, что соединение на самом деле может быть уникальным, но разработчик базы данных не объявил и не включил уникальность при помощи объявленного ключа или индекса, то можете проверить уникальность при помощи SELECT COLINT(*). COUNT(DISTINCT Department ID) FROM Empl oyees:. Однако вы, наверное, будете удивлены, насколько редко возникают какие-либо сомнения насчет того, с какой стороны соединение является уникальным, так как очень часто помогают названия столбцов и таблиц. Так что обычно вы можете работать, используя собственные догадки, и проверяя их, только если полученный результат будет работать хуже, чем вы ожидали или хуже, чем требуется.

3. Найдите условия соединения, идущие от внешнего ключа центральной таблицы к первичному ключу другой таблицы, и нарисуйте для таких соединений стрелки, указывающие вниз от центральной таблицы. У нижнего конца каждой стрелки напишите псевдонимы таблиц, с которыми проводится соединение. Если связь представляет внешнее соединение, в центре связи добавьте острие стрелки, указывающее на дополнительную таблицу.

4. Сместите фокус на другой, пока что не рассмотренный узел в диаграмме и повторяйте шаги 2 и 3, пока не соберете узлы, представляющие все псевдонимы в разделе FROM, и стрелки, представляющие все соединения. (Например, я представляю многоэлементное соединение как одну стрелку от составного внешнего ключа к составному первичному ключу.) Обычно вниз на узел будет указывать только одна стрелка, поэтому вы будете искать новые указывающие вниз соединения из узлов, уже находящихся на нижнем конце соединения (со стрелкой). Так получается перевернутая древовидная структура, ниспадающая из одной детальной таблицы наверху.

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

единения. Практически всегда для дополнительной таблицы внешнего соединения (на стороне со знаком (+) соединения, в старой записи Oracle, или в новом стиле - сразу за ключевыми словами LEFT OUTER) условия фильтрации не указаны, поэтому коэффициент фильтрации равен 1,0, что обозначается просто фактом отсутствия числа на диаграмме.

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

ОБЕСПЕЧЕНИЕ УНИКАЛЬНОСТИ

Иногда условие фильтрации настолько близко к гарантированному уникальному соответствию, что хочется считать его уникальным. Обычно это можно делать без опаски, но я обнаружил, что полезно сначала попытаться сделать уникальность полной, добавив несколько отсутствующих условий или исправив дизайн базы данных. Например, у вас может быть фильтр Order ID=: 1 для таблицы Orders, и первичный ключ для Orders, состоящий из столбцов Orcler ID и Company ID, причем у Company ID единственное, доминирующее значение встречается в 99,9 % случаев. Наиболее вероятная причина, почему разработчик не указал никаких условий для Company ID, - он просто забыл, что иногда этот столбец принимает значение, не равное доминантному, и что Оrder ID отдельно не всегда указывает на уникальный заказ. Я обнаружил, что, когда условия фильтрации близки к уникальным, то практически всегда разработчик хотел сделать их уникальными, и следует добавить отсутствующее условие или условия, чтобы добиться запланированной функциональности.

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

Если у вас есть настоящие данные, использующиеся для работы приложения, то они являются идеальным источником коэффициентов фильтрации и соединения. Для примера 5.1, чтобы точно определить зти коэффициенты, нужно выполнить следующие запросы (от Q1 до Q5):

Q1: SELECT COUNTC*) Al FROM Employees

WHERE Exempt F1ag=V: Al: 1000

02: SELECT COUNTC*) A2 FROM Employees: A2: 10000

03: SELECT COUNT(*) A3 FROM Departments

WHERE US Based F1ag=Y: A3: 245

Q4: SELECT COUNTC*) A4 FROM Departments: A4: 490

05: SELECT COUNTC*) A5 FROM Employees E, Departments D

WHERE E.Department ID=D.Department ID: A5: 9800