© Суреш Айер (Suresh Aiyer)
© Database Programming & Design, vol.11, N 1-98
Статья была опубликована на сайте Сибирского Отделения РАН

14. Знайте свои данные.

Как отмечалось выше, вы должны быть близко знакомы со своими данными. Например, пусть имеется таблица с именем BOXER и двумя столбцами BOXER_NAME и SEX. Для столбца SEX существует неуникальный индекс. Если имеется равное число боксеров мужского и женского пола, то следующий запрос будет быстрее выполнен путем полного просмотра таблицы:

SELECT BOXER_NAME
FROM BOXER
WHERE SEX = 'F';

Можно гарантировать такой способ выполнения, включив в запрос указание FULL.

Если же таблица содержит 98% строк, относящихся к боксерам-мужчинам, то следующий запрос будет выполнен быстрее по причине использования индексного сканирования:

SELECT BOXER_NAME --+ INDEX (BOXER SEX)
FROM BOXER
WHERE SEX = 'F';

Этот пример иллюстрирует, насколько важно знать распределение данных. Эффективность выполнения SQL-запросов будет сильно меняться при росте размеров базы данных и изменении распределения данных. В Oracle 7.3 была включена функция HISTOGRAMS, позволяющая оптимизатору быть в курсе распределения данных в таблице и выбирать соответствующий план выполнения запроса.

15. Знайте, когда использовать просмотр больших таблиц.

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

16. Минимизируйте число просмотров таблиц

Обычно уменьшение числа просмотра таблиц в SQL-запросах приводит к повышению эффективности. Запросы с меньшим числом просмотров таблиц - более быстрые запросы. Вот пример. Таблица STUDENT содержит четыре столбца с именами NAME, STATUS, PARENT_INCOME и SELF_INCOME. Имя является первичным ключом. Значение статус равно 0 для независимых студентов и 1 - для зависимых студентов. Следующий запрос возвращает имена и величину доходов независимых и зависимых студентов. Форма запроса предполагает два просмотра таблицы STUDENT, создание временной таблицы для последующей обработки и сортировку для устранения дубликатов:

SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;

Тот же самый результат будет получен при выполнении запроса с одним просмотром таблицы:

SELECT NAME, PARENT_INCOME * STATUS + SELF_INCOME * (1 - STATUS)
FROM STUDENT;

17. Соединяйте таблицы в правильном порядке.

Порядок соединения таблиц в запросах с соединениями нескольких таблиц имеет критическое значение. Если таблицы соединяются в правильном порядке, то общее число обрабатываемых строк будет меньше. Всегда следует выполнять сначала максимально ограничивающий поиск, чтобы отфильтровать как можно большее число строк на ранних фазах выполнения запроса с соединениями. Тогда на следующих фазах соединения оптимизатору придется иметь дело с меньшим числом строк, что повысит эффективность. Следует убедиться, что главная таблица (просматриваемая во внешнем цикле соединения на основе вложенных циклов) содержит наименьшее число строк. При соединении основной и уточняющей таблиц (например, таблиц ORDERS и ORDER_LINE_ITEMS) убедитесь, что первой будет основная таблица; при обработке во внешнем цикле уточняющей таблицы обычно будет затронуто гораздо большее число строк.

При использовании оптимизатора, основанного на правилах, главная таблица должна указываться последней в списке раздела FROM. Если применяется метод вложенных циклов, следует обдумать целесообразность создания индекса для убыстрения поиска во внутреннем цикле. Средства Explain Plan и TKPROF позволяют получить информацию о применяемом методе выполнения соединения, порядке соединения таблиц и числе строк, обрабатываемых на каждой фазе соединения.

В случае применения оптимизатора на основе оценок порядок, в котором таблицы указаны в разделе FROM, не является существенным для оптимизатора, который всегда пытается найти наилучший план выполнения запроса. Для управления порядком соединения таблиц можно использовать указание ORDERED. При выполнении следующего запроса таблицы были бы соединены в том порядке, в котором они указаны в разделе WHERE:

SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDERS_LINE_ITEMS.PRODUCTNO    --+ORDERED
FROM ORDERS, ORDERS_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;

18. При возможности используйте только поиск через индексы.

Тогда для выполнения запросов оптимизатор будет нуждаться только в поиске в индексе, а не в таблице, и эффективность будет лучше. Оптимизатор будет использовать только поиск в индексе, если вся информация, необходимая для выполнения запроса, содержится в самом индексе. Если для таблицы EMP существует составной индекс на столбцах LNAME и FNAME, то при выполнении следующего запроса будет использован только поиск в индексе:

SELECT FNAME
FROM EMP
WHERE LNAME = 'SMITH';

SELECT FNAME, SALARY
FROM EMP
WHERE LNAME = 'SMITH';

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

19. Избыточность полезна.

Помещайте в раздел WHERE как можно больше информации. Например, если указан раздел WHERE COL1 = COL2 AND COL1 = 10, оптимизатор сможет вывести, что COL2 = 10. Но при задании раздела в форме WHERE COL1 = COL2 AND COL2 = COL3, оптимизатор не будет считать, что COL1 = COL3.

20. Старайтесь писать как можно более простые и тупые операторы SQL.

Оптимизатор может не справиться со слишком сложными операторами SQL; иногда написание нескольких более простых операторов позволяет добиться лучшей эффективности, чем задание одного сложного оператора. Основанный на оценках оптимизатор СУБД Oracle не является абсолютно устойчивым. Он находится на стадии разработки и улучшается в каждом новом выпуске системы. Поэтому следует все время смотреть на оценки стоимости, выдаваемые средством Explain Plan. "Стоимость" - это понятие относительное; никто не знает, что значит числовая оценка стоимости, но чем меньше это значение, тем лучше эффективность выполнения оператора SQL. При настройке оператора нужно стремиться к снижению этой оценки.

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

21. Одного и того же можно добиться разными способами.

Во многих случаях одни и те же результаты могут быть получены с использованием разных операторов SQL. Для выполнения таких операторов могут применяться разные пути доступа. Например, оператор MINUS может выполняться гораздо быстрее, чем запросы с WHERE NOT IN (SELECT) или WHERE NOT EXISTS. Предположим, что имеются индексы на столбце STATE и столбце AREA_CODE. Несмотря на наличие этих индексов для выполнения следующего запроса потребуется полный просмотр таблицы (по причине использования предиката NOT IN):

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN ('VA', 'DC', 'MD')
 AND AREA_CODE NOT IN (804, 410);

Однако этот запрос может быть переписан с использованием оператора MINUS, что позволит использовать индексное сканирование:

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN ('VA', 'DC', 'MD')
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE NOT IN (804, 410);

Если в разделе WHERE запроса содержится OR, такой запрос может быть переписан с заменой OR на UNION. Прежде, чем решиться использовать вариант SQL-запроса, тщательно сравните планы выполнения всех возможных вариантов.

22. Используйте специальные столбцы.

Не забывайте о наличии специальных столбцов ROWID и ROWNUM. Помните, что доступ к строке по ROWID является самым быстрым. Вот пример оператора UPDATE, в котором используется сканирование по ROWID:

SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;

UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;

Значение ROWID в базе данных не является константой, поэтому не задавайте явных значений ROWID в операторах SQL и приложениях.

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

SELECT EMPLOYEE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;

23. Явные курсоры предпочтительнее неявных.

При использовании неявных курсоров требуется лишнее чтение. Для работы с явными курсорами используются операторы SQL DECLARE, OPEN, FETCH и CLOSE. Неявные курсоры в СУБД Oracle открываются для операторов DELETE, UPDATE, INSERT и SELECT.

24. Исследуйте возможности опции параллельного выполнения запросов и используйте ее преимущества.

Эта опция дает возможность параллельного выполнения операторов SQL с целью убыстрения. В Oracle7 параллельно могли выполняться только запросы с полным просмотром таблицы. В Oracle8 могут быть распараллелены и запросы с индексным сканированием в заданном диапазоне значений ключа, если индекс является разделенным. Опция можно использовать только в системах SMP и MPP с несколькими дисковыми устройствами. В сервере Oracle имеется много возможностей, но наличие этих возможностей само по себе не гарантирует повышенную эффективность. Необходимо соответствующим образом конфигурировать базу данных и специально оформлять операторы SQL. Например, следующий оператор SQL мог бы быть выполнен параллельно:

SELECT *  --+PARALLEL(ORDERS,6)
FROM ORDERS;

25. Сокращайте сетевой трафик и увеличивайте пропускную способность сети.

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

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail