Читать онлайн «Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ». Страница 104

Алиасы, указанные в запросе, используются в плане, выводимом оптимизатором. Для совместимости при создании пользовательского плана хорошей идеей является использование того же способа идентификации таблиц, как и в запросе. При этом, несмотря на то, что синтаксический анализатор не допускает смешивания идентификаторов таблиц и их алиасов в запросах, предложение PLAN допускает любое смешивание[82]. Например, следующий вариант является приемлемым. Обратите внимание, что оптимизатор допускает использование и идентификаторов таблиц в предложении PLAN.

SQL> SELECT Е.*, P.* FROM EMPLOYEE E, PROJECT P

CON> PLAN JOIN (PROJECT NATURAL, EMPLOYEE NATURAL);

PLAN JOIN (P NATURAL, E NATURAL)

Соединение с индексированными ключами равенства

Такое соединение денормализует отношение один-ко-многим - каждый служащий имеет одну или более записей истории заработной платы:

SELECT Е.*, S.OLD_SALARY, S.NEW_SALARY

FROM EMPLOYEE E

JOIN SALARY_HISTORY S

ON S.EMP_NO = E.EMP_NO;

PLAN JOIN (S NATURAL, E INDEX (RDB$PRIMARY7))

Оптимизатор выбирает цикл по (потенциально) самому длинному детальному потоку для поиска релевантных строк с использованием индекса уникального первичного ключа таблицы EMPLOYEE. В этом примере либо количество строк в каждой таблице будет приблизительно равным, либо количество строк в таблице SALARY_HISTORY не превысит количество строк в таблице EMPLOYEE в той мере, чтобы не достичь преимуществ уникального индекса в качестве ключа соответствия. Это внутреннее соединение, и оптимизатор разумно предполагает, что именно правый поток определит размер реки.

Давайте посмотрим, как оптимизатор трактует те же самые потоки, когда соединение является внешним левым:

SELECT Е. *, S.OLD_SALARY, S.NEW_SALARY

FROM EMPLOYEE E

LEFT JOIN SALARY_HISTORY S

ON S.EMP_NO = E.EMP_NO;

PLAN JOIN (E NATURAL, S INDEX (RDB$FOREIGN21))

В этот раз одна строка будет возвращена для каждой строки правого потока, независимо от того, будет ли существовать соответствующий ключ в управляющем потоке. Размер реки здесь не имеет значения, поскольку внешние соединения однозначно определяют, какая таблица должна быть в левой стороне, чтобы по ней проводить цикл просмотра. Это алгоритм внешнего соединения, который определяет метод доступа, не измеряющий потоки. С таблицей EMPLOYEE В левой части не существует возможности создать внешнее соединение путем просмотра таблицы SALARY_HISTORY с поиском в EMPLOYEE.

Поскольку оптимизатор не делает выбора относительно того порядка, в котором будут соединяться потоки, он просто выбирает наиболее подходящий индекс из SALARY_HISTORY.

Когда размер имеет значение

В следующем примере размер таблицы не виден при использовании индекса уникального первичного ключа. Таблица DEPARTMENT содержит 21 строку, таблица PROJECT - 6 строк, и оптимизатор выбирает меньший индекс внешнего ключа для оптимизации поиска соответствия по большей таблице:

SELECT * FROM DEPARTMENT D

JOIN PROJECT P

ON D.MNGR_NO = P. TEAM_LEADER ;

PLAN JOIN (D NATURAL,P INDEX (RDB$FOREIGN13))

Соединение с индексированным предложением ORDER BY

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

SQL> SELECT P.*, E.FULL_NAME FROM PROJECT P JOIN EMPLOYEE E

ON E.EMP_NO = Р. TEAM_LEADER ORDER BY P.PROJ_NAME ;

PLAN JOIN (Р ORDER RDB$11, E INDEX (RDB$PRIMARY7))

Уникальный индекс для EMPLOYEE выбирается по причине неявного условия фильтра в критерии соединения. Запрос сокращает количество служащих, которые не являются руководителями, а уникальный индекс позволяет исключить сканирование таблицы EMPLOYEE. Выбор индекса фильтрации может также повлиять на необходимость использования навигационного индекса в PROJ_NAME для сортировки[83].

Оптимизатор выбирает индекс правой стороны, потому что правый поток будет того же размера, что и левый или (потенциально) большего размера. Опять же, оптимизатор не может сказать, что это отношение является отношением один к одному. Столбец PROJ_NAME, задающий порядок выходного набора, имеет уникальный индекс, созданный ограничением UNIQUE, чтобы использовать для сортировки, и оптимизатор выбирает этот индекс. Индекс сортировки появляется в плане первым, указывая серверу на необходимость сортировки левого потока перед тем, как он будет отыскивать соответствие ключа соединения в правом потоке.

Соединение равенства при отсутствии доступных индексов

Таблицы в следующем запросе являются неиндексированными копиями таблиц PROJECT и EMPLOYEE (см. сноску 4 ранее в этой главе):

SQL> SELECT PI.*, EL. FULL_NAME FROM PROJECT1 PI JOIN EMPLOYEEL EL ON EL.EMP_NO = PL.TEAM_LEADER ORDER BY PI. PROJ_NAME;

PLAN SORT (MERGE (SORT (EL NATURAL) , SORT (PI NATURAL)))

Потоки с обеих сторон будут сортироваться, а затем сливаться, полученная река снова будет сортироваться, потому что ни один из потоков не имеет требуемого порядка сортировки.

Трехстороннее соединение с индексированными равенствами

Рассмотрим тройное эквисоединение в следующем примере:

SQL> SELECT P.PROJ_NAME, D.DEPARTMENT, PDB. PROJECTED_BUDGET FROM PROJECT P

JOIN PROJ_DEPT_BODGET PDB ON P.PROJ_ID = PDB.PROJ_ID JOIN DEPARTMENT D ON PDB.DEPT_NO = D.DEPT_NO;

PLAN JOIN (D NATURAL, PDB INDEX (RDB$FOREIGN18), P INDEX (RDB$PRIMARY12))

Поскольку доступно множество подходящих индексов, оптимизатор выбирает метод доступа JOIN. Индекс, связывающий поток PDB с таблицей DEPARTMENT, будет использован для выбора потока DEPARTMENT. При обработке результирующей реки и потока PROJECT эквисоединение между первичным ключом таблицы PROJECT и большей по размеру (потенциально) реки дает возможность формировать реку с использованием индекса первичного ключа PROJECT для выборки данных из реки.

Трехстороннее соединение

только с одним индексированным равенством

Для этого примера мы используем неиндексированные копии таблиц PROJECT и EMPLOYEE для демонстрации того, как оптимизатор будет использовать доступный индекс, когда он может применять лучший вариант условий неиндексированного эквисоединения:

SQL> SELECT PI.PROJ_NAME, DL.DEPARTMENT, PDB.PROJECTED_BUDGET FROM PROJECT1 PI

JOIN PROJ_DEPT_BUDGET PDB ON PI . PROJ_ID = PDB.PROJ_ID

JOIN DEPARTMENT1 Dl ON PDB. DEPT_NO = Dl. DEPT_NO;

PLAN MERGE (SORT

(PI NATURAL), SORT (JOIN (Dl NATURAL, PDB INDEX (RDB$FOREIGN18))))

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

После этого результирующая река и поток PROJECT сортируются. В завершение (в самом внешнем цикле) два сортированных потока объединяются в один.

Запросы с множеством планов

Когда в запросе задаются подзапросы и объединения, используется несколько операторов SELECT. Оптимизатор конструирует независимый план для каждого оператора SELECT. Возьмем следующий пример:

SELECT

P.PROJ_NAME,

(SELECT E.FULL_NAME FROM EMPLOYEE E

WHERE P.TEAM_LEADER = E.EMP_NO) AS LEADER_NAME

FROM PROJECT P

WHERE P.PRODUCT = 'software'

PLAN (Е INDEX (RDB$PRIMARY7) )

PLAN (Р INDEX (PRODTYPEX))

Первый план выбирает индекс первичного ключа таблицы EMPLOYEE для просмотра кодов TEAM_LEADER в первичной таблице подзапроса. Индекс PRODTYPEX для таблицы PROJECT используется для фильтрации строк в таблице PRODUCT, поскольку первым элементом ключа в этом индексе является столбец PRODUCT.

Интересно то, что если изменить тот же запрос, включив предложение упорядочения, то оптимизатор изменит свой выбор индекса для фильтрации и выберет уникальный индекс по PROJ_NAME для навигации по упорядочиваемому столбцу:

SELECT

P.PROJ_NAME,

(SELECT E.EULL_NAME FROM EMPLOYEE E

WHERE P.TEAM_LEADER = E.EMP_NO) AS LEADER_NAME

FROM PROJECT P

WHERE P.PRODUCT = 'software' ORDER BY 1;

PLAN (E INDEX (RDB$PRIMARY7))

PLAN (P ORDER RDB$11)

Задание вашего собственного плана

Синтаксис выражений, который использует оптимизатор для создания плана и передачи его серверу Firebird доступен в SQL в предложении PLAN. Это позволяет вам определять ваш собственный план, ограничивая оптимизатор в его выборе.

Предложение PLAN может быть задано почти в любом операторе SELECT, включая операторы, используемые в создании просмотров, в хранимых процедурах и подзапросах. Firebird версии 1.5 и выше также допускает предложения PLAN и в триггерах. Множество планов может быть указано независимо для запроса и любого подзапроса. При этом нет требования "все или ничего" - любое предложение плана является необязательным.

Предложение PLAN генерируется для оператора SELECT В хранимой процедуре выбора. Поскольку выходом хранимой процедуры выбора является виртуальный набор, любые условия будут основываться на доступе NATURAL. При этом любой оператор SELECT в хранимой процедуре будет оптимизирован, и для него можно применять пользовательский план.

! ! !

ПРИМЕЧАНИЕ. Конструирование пользовательского плана для оператора SELECT в просмотре создает собственные проблемы для разработчика. Более подробную информацию см. в разд. "Использование планов запросов для просмотров" главы 24.