Во время фазы разработки приложения не всегда можно точно знать, какие коэффициентов фильтрации следует ожидать во время работы приложения на реальных объемах данных. В этом случае нужно производить оценку, основываясь на знании работающего приложения, а не на малых искусственных объемах данных в тестовых базах данных.
Неподчеркнутые числа рядом с обоими концами связи представляют среднее количество строк, найденных в таблице на этом конце соединения для соответствующей строки на другом конце соединения. Они называются коэффициентами соединения. Коэффициент соединения в начале соединения — это детальный коэффициент соединения, а на конце соединения (со стрелкой) — главный коэффициент соединения.
Главные коэффициенты соединения всегда меньше или равны 1,0, так как уникальный ключ гарантирует обнаружение нескольких главных строк для одной детальной. Часто встречается случай, когда в детальной таблице внешний ключ обязателен и ссылочная целостность данных идеальна (что гарантирует существование подходящей главной строки), тогда главный коэффициент соединения равен в точности 1,0.
Детальные коэффициенты соединения могут быть равны любому неотрицательному числу. Они могут быть меньше 1,0, так как некоторые отношения главной и детальной таблиц разрешают существование нуля, одной или многих детальных строк, причем чаще всего встречается случай «один к нулю». В примере для средней строки Employees есть соответствующая строка (с которой она связана) в Departments в 98 % случаев, тогда как средняя строка Departments соответствует (связывается с) 20 строкам Employees. Нужно по возможности получать эти значения из полных, реальных распределений данных. Так же, как и с коэффициентами фильтрации, может потребоваться вычисление коэффициентов соединения во время фазы разработки приложения.
Диаграммы запросов полностью исключают любые упоминания списков столбцов и выражений, которые выбирает запрос (то есть все, что находится между SELECT и FROM). Производительность запроса практически полностью определяется тем, какие строки выбираются из базы данных, и каким образом они получаются. Что делается с этими строками, какие столбцы возвращаются, и какие выражения подсчитываются — это практически несущественно для производительности. Главное, но редкое исключение из этого правила — когда выбираются так мало столбцов из таблицы, что база данных может выполнить запрос, используя только данные из индекса, совершенно не обращаясь к основной таблице. Иногда доступ только к индексу может существенно сэкономить ресурсы, но он мало влияет на решения, которые принимаются относительно оставшейся части плана исполнения. Решать, нужно ли попробовать только индексный доступ, следует в последний момент процесса настройки и только если наилучший план без применения этой стратегии оказывается слишком медленным.
В диаграмме отсутствуют любые указания на сортировку (ORDER BY), группировку (GROUP BY) и фильтрацию после группировки (HAVING). Эти операции практически никогда не имеют большого значения для производительности запроса. Шаг сортировки, который они обычно включают, может влиять на скорость выполнения, но для изменения его стоимости мало что можно сделать, и эта стоимость обычно не так велика по сравнению с производительностью плохо выполняющегося запроса.
В диаграммах запроса имена таблиц обычно заменяются псевдонимами. Не имеет значения, из какой таблицы запрос считывает данные или какие сущности хранятся в таблицах. Нужно уметь преобразовывать результат обратно в действия в исходном SQL и в базе данных (такие действия, как создание нового индекса, например). Однако при решении абстрактной проблемы настройки, то чем более абстрактными будут названия узлов, тем лучше.
Детали условий соединения теряются, когда представляются соединения как простые стрелки с парой чисел, полученных откуда-то за пределами SQL. Если известна статистика соединения, то подробности (например, столбцы соединения и как они между собой связаны) не играют роли.
Диаграмма не указывает размеры таблиц. Однако можно сделать предположение о размерах таблиц, исходя из детального коэффициента соединения, который находится у верхнего конца связи. Имея диаграмму запроса, необходимо знать общие размеры таблиц, чтобы установить, сколько будет возвращено строк, и сколько времени займет выполнение запроса. Но оказывается, что эта информация не нужна для выяснения относительного времени выполнения различных вариантов, и, следовательно, для поиска лучшего. Это полезный результат, поскольку зачастую необходимо добиться хорошего выполнения запроса не только на единственном экземпляре базы данных, но на целом диапазоне экземпляров для множества пользователей. Для различных пользователей могут существовать таблицы различных абсолютных размеров, но относительные размеры обычно изменяются несущественно, а коэффициенты соединения и фильтрации и того меньше. В действительности они изменяются настолько мало, что различия можно игнорировать.
Подробности условий фильтрации теряются, когда они абстрагируются до обыкновенных чисел. Можно выбрать оптимальный путь к данным, ничего не зная о том, как, или по каким столбцам база данных исключает строки из результата выполнения запроса. Необходимо только знать, насколько эффективен в числовом отношении каждый фильтр для достижения поставленных целей исключения строк. Как только будет обнаружен абстрактный оптимальный план, потребуется вернуться к подробным условиям фильтрации, чтобы понять, что нужно изменить. Можно изменить индексы для достижения оптимального пути, или изменить SQL-код, чтобы заставить базу данных использовать уже существующие индексы, но, в любом случае, этот финальный шаг прост, если известно, каков оптимальный абстрактный план.
1.4 Создание диаграммы запроса
Ниже перечислены правила создания полной диаграммы запроса.
1. Начать с произвольно выбранного псевдонима таблицы из раздела FROM и поместить его в середину пустой страницы. Эта таблица будет называться центральной таблицей, подразумевая, что она будет текущей точкой, начиная с которой будут добавляться дальнейшие элементы в диаграмму запроса.
2. Найти условия соединения, соответствующие единственному значению первичного ключа центральной таблицы. Для каждого такого соединения нарисовать стрелку, указывающую вниз, на центральную таблицу, пометив начало стрелки псевдонимом на противоположной стороне соединения. Если связь представляет внешнее соединение, добавить посередине связи острие стрелки, направленное на дополнительную таблицу.
3. Найти условия соединения, идущие от внешнего ключа центральной таблицы к первичному ключу другой таблицы, и нарисовать для таких соединений стрелки, указывающие вниз от центральной таблицы. У нижнего конца каждой стрелки напишите псевдонимы таблиц, с которыми проводится соединение. Если связь представляет внешнее соединение, в центре связи добавить острие стрелки, указывающее на дополнительную таблицу.
4. Сместить фокус на другой, пока что не рассмотренный узел в диаграмме и повторять шаги 2 и 3, пока не будут собраны узлы, представляющие все псевдонимы в разделе FROM, и стрелки, представляющие все соединения. Обычно вниз на узел будет указывать только одна стрелка, поэтому придется искать новые указывающие вниз соединения из узлов, уже находящихся на нижнем конце соединения (со стрелкой). Так получается перевернутая древовидная структура, ниспадающая из одной детальной таблицы наверху.
5. Заполнив все узлы и связи, вписать числа для коэффициентов фильтрации и коэффициентов соединения, основываясь, если возможно, на статистике по таблицам для промышленного приложения. Если нет промышленных данных, то постараться представить коэффициенты как можно точнее. Нет необходимости добавлять коэффициенты соединения рядом со связями, представляющими внешние соединения. Практически всегда для дополнительной таблицы внешнего соединения (сразу за ключевыми словами LEFT OUTER) условия фильтрации не указаны, поэтому коэффициент фильтрации равен 1,0, что обозначается просто фактом отсутствия числа на диаграмме.
6. Нарисовать звездочку рядом с коэффициентом фильтрации для всех фильтров, которые гарантированно возвращают максимум одну строку. Это не функция, высчитанная на основе коэффициента и количества возвращенных строк из таблицы, так как условие может в среднем возвращать одну строку, но не обязательно она будет возвращать только одну строку. Чтобы гарантировать, что максимальное количество возвращенных строк будет равно единице, необходимо иметь уникальный индекс или понятные ограничения приложения, дающие реальную гарантию.
Пример.
Имеется запрос
SELECT C.Phone_Number, C.Honorific, C.First_Name, C.Last_Name,
C.Suffix, C.AddressJD, A.AddressJD, A.Street_Address_Linel,
A.Street_Address_Line2, A.City_Name, A.State_Abbreviation, A.ZIP_Code,
DD.Deferred_Shipment_Date, OD.Item_Count. DOT.Text, OT.Text,
P.Product_Description, S.Shipment_Date
FROM Orders O, Orderjtetails OD, Products P, Customers C, Shipments S,
Addresses A, Code_Translations DOT, Code_Translations OT
WHERE UPPER(C.Last_Name) LIKE :Last_Name||'%'
AND UPPER(C.First_Name) LIKE :First_Name||'%'
AND OD.OrderID = O.Order_ID
AND O.CustomerID = C.Customer_ID
AND OD.Product_ID = P.Product_ID(+)
AND OD.Shipment_ID = S.Shipment_ID(+)
AND S.Address_ID = A.Address_ID(+)
AND O.Status_Code = OT.Code
AND DT.CodeJype = 'ORDER_STATUS'
AND OD.Status_Code = ODT.Code
AND CDT.CodeJype = 'ORDERJIETAILJTATUS
AND O.Order_Date > :Now - 366
ORDER BY C.CustomerID, O.Drder_ID DESC, S.ShipmentID, DD.Order_Detail_ID;
Построенная по вышеперечисленным правилам для данного запроса диаграмма показана на рис.1.2.
Множество подробностей, присутствующих на полных диаграммах запросов, не обязательны, только для самых редких проблем. Для концентрации на необходимых элементах нужен только скелет диаграммы и приблизительные коэффициенты фильтрации. Изредка требуются коэффициенты соединений, но обычно только когда любой из детальных коэффициентов соединения меньше 1,5 или главный коэффициент соединения меньше 0,9. Это, в свою очередь, значит, что меньшее количество данных требует создания более простых диаграмм соединения. Нет необходимости узнавать количество строк для таблиц без фильтров. На практике в многосторонних соединениях обычно есть фильтры только для 3-5 таблиц, поэтому даже самый сложный запрос легко изобразить на диаграмме, не используя множество запросов для сбора статистики.