Хотя подавляющее большинство разработчиков информационныхприложений и конечных пользователей баз данных более или менее регулярно использует язык SQL и, конечно же, имеет свои приемы достижения приемлемой эффективности, об этом очень редко пишут. В предлагаемом Вашему вниманию обзоре статьи делается попытка обобщить опыт эффективного написания операторов SQL в среде Oracle. Некоторые предлагаемые автором заповеди относятся к особенностям языка SQL в целом; другие заповеди основаны на особенностях реализации Oracle. Иногда заповеди кажутся наивными, иногда отражают слабости оптимизатора запросов Oracle. Однако в целом статья показалась мне интересной и практически полезной. Если это будет интересно читателям, я могу прокомментировать каждую из заповедей. Напишите мне.
С уважением, Сергей Кузнецов
Нельзя не учитывать важность эффективных SQL-операторов в основанных на использовании СУБД Oracle приложениях. Плохо написанный оператор может привести к хаосу в базе данных. Поскольку во многих организациях пользователи производят доступ к базам данных с использованием средств генерации отчетов и прямых запросов, эффективно написанный запрос на языке SQL позволяет не только улучшить производительность приложения, но и уменьшить сетевой трафик. Поэтому как пользователи, так и разработчики должны хорошо понимать работу оптимизатора запросов и возможности настройки, которая может сделать операторы более эффективными и менее рискованными.
В статье кратко обсуждаются 25 приемов, позволяющих добиться более быстрого выполнения операторов SQL. Некоторые из этих приемов ранее описывались в руководствах компании Oracle и журналах, а многие другие ранее не публиковались.
1. Хорошо знайте свои данные и бизнес-приложение.
Идентичная информация часто может быть получена из разных источников. Познакомьтесь с этими источниками; вы должны быть в курсе объема данных и их распределения в своей базе данных. Вы также должны иметь полное понимание используемой модели данных (равно как и связей между разными бизнес-объектами) до написания требуемых операторов SQL. Это понимание поможет намного лучше составлять запросы для извлечения информации из нескольких таблиц. CASE-средства, подобные Designer/2000, очень помогают документировать связи между различными объектами.
2. Тестируйте свои запросы на реалистических данных.
Во многих организациях имеются три среды баз данных: среда разработки, среда тестирования и среда использования. Программисты применяют среду разработки баз данных для создания и тестирования приложений, которые гораздо более тщательно проверяются программистами и пользователями в среде тестирования, прежде чем переходят в среду использования. При тестировании операторов SQL в среде тестирования убедитесь, что тестовая база данных содержит данные, отражающие реальные черты производственной базы данных. Для обеспечения строгости тестирования распределение данных в среде тестирования также должно быть близко к распределению данных в среде использования.
3. Пишите в своих приложениях идентичные операторы SQL.
Применяйте все возможности связывания переменных, хранимых процедур и пакетов. Преимущества идентичных операторов SQL включают уменьшение расходов памяти на сервере баз данных и более быстрое выполнение, поскольку исчезает потребность в новом грамматическом разборе. Например, следующие операторы не являются идентичными:
select * from employee where empid = 10; SELECT * FROM EMPLOYEE WHERE EMPID = 10; select * from employee where empid = 20;
но при использовании связываемой переменной с именем i_empid оператор
select * from employee where empid = :i_empid;будет идентичным.
4. Внимательно относитесь к использованию индексов на таблицах.
Постарайтесь создать все необходимые индексы. Однако слишком большое число индексов может привести к снижению эффективности. Как же решить, какие столбцы следует индексировать?
5. Делайте доступными к использованию индексные пути доступа к данным.
Для получения преимуществ от наличия индексов пишите SQL-операторы таким образом, чтобы для их выполнения были доступны индексные пути доступа. Оптимизатор не может использовать индексный путь доступа, основываясь только на существовании индекса; путь доступа должен быть сделан доступным в SQL. Механизм "указаний" (hints) - это один из способов гарантировать использование индекса.
6. При возможности используйте Explain Plan и TKPROF.
Если ваши SQL-операторы недостаточно хорошо настроены, они могут быть неэффективны, даже если сама база данных Oracle "хорошо смазана". Познакомьтесь с Explain Plan и средства TKPROF, чтобы уметь с пользой их применять. Explain Plan помогает узнать путь доступа, используемый для выполнения оператора SQL; TKPROF показывает реальные показатели эффективности. Эти средства привязаны к программному обеспечению сервера баз данных Oracle и могут помочь улучшить эффективность выполнения операторов SQL.
7. Разберитесь в том, как работает оптимизатор.
Операторы SQL могут быть выполнены с использованием оптимизатора, управляемого правилами, или оптимизатора, основанного на оценках. В старых приложениях было более распространено использование оптимизатора, управляемого правилами. Многие пользователи Oracle применяли этот подход в течение ряда лет и были вполне им довольны. Но при разработке новых приложений рассмотрите возможность применения оптимизатора, основанного на оценках. Компания Oracle совершенствует этот оптимизатор в каждом выпуске, делая его более стабильным и надежным. Если вы решите его использовать, будьте готовы регулярно выполнять анализ схемы. Соответствующее средство сохраняет статистические показатели базы данных в таблицах-каталогах, а затем эти показатели используются оптимизатором. Настройка операторов SQL возможна только при использовании оптимизатора, основанного на правилах. Если вы планируете переключиться на использование нового оптимизатора, вам следует оценить эффективность каждого оператора SQL во всех приложениях, использующих базу данных.
8. Глобально думайте при выполнении локальных действий.
Помните всегда: любые изменения базы данных с целью настройки одного оператора SQL могут повлиять на другие операторы, используемые приложениями и пользователями.
9. Раздел WHERE является критическим.
Для следующих примеров раздела WHERE индексный путь доступа не будет использоваться, даже если индекс существует (COL1 и COL2 - столбцы одной таблицы, и создан индекс на COL1):
SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) like 'SALES%');
10. Для фильтрации записей используйте WHERE, а не HAVING.
Избегайте использования раздела HAVING вместе с GROUP BY на индексированных столбцах. В этом случае индекс не используется. Фильтруйте строки с помощью раздела WHERE, а не раздела HAVING. Если для таблицы EMP существует индекс на столбце DEPTID, в при выполнении следующего запроса этот индекс использоваться не будет:
SELECT DEPTID, SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;
Однако этот запрос можно переписать так, чтобы индекс применялся:
SELECT DEPTID, SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;
11. Указывайте в разделе WHERE начальные столбцы ключа индекса.
При наличии индекса с составным ключом этот индекс может быть использован, если в разделе WHERE фигурируют условия на начальные столбцы его ключа. Для следующего запроса может быть применен составной индекс на столбцах PART_NUM и PRODUCT_ID, образованный в связи с ограничением первичного ключа:
SELECT *
FROM PARTS
WHERE PART_NUM = 100;
в то время как в приводимом ниже запросе составной индекс использоваться не может:
SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;
Последний запрос можно переписать так, чтобы индекс можно было применить. В этом запросе предполагается, что столбец PART_NUM будет всегда содержать положительные значения:
SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;
12. Сравните сканирование через индекс с полным просмотром таблицы.
При выборе из таблицы более 15 процентов строк полный просмотр таблицы обычно выполняется быстрее, чем сканирование через индекс. В таких случаях пишите свои запросы так, чтобы при их выполнении использовался полный просмотр таблицы. При выполнении следующих запросов индексное сканирование не будет применяться, даже если существует индекс на столбце SALARY. В первом запросе указание FULL заставит Oracle-сервер полностью просматривать таблицу. Когда использование индекса приносит больше вреда, чем пользы, можно применять эти методы, чтобы воспрепятствовать использованию индекса.
SELECT * --+FULL
FROM EMP
WHERE SALARY = 50000;
SELECT *
FROM EMP
WHERE SALARY+0 = 50000;
Для выполнения следующего запроса также не будет применяться индексное сканирование, даже если существует индекс на столбце SS#:
SELECT *
FROM EMP
WHERE SS# || '' = '111-22-333';
Индекс не используется и в том случае, когда Oracle-сервер должен выполнять неявное преобразование данных. В следующем примере SALARY является числовым столбцом таблицы EMP, и символьное значение преобразуется в числовое:
SELECT *
FROM EMP
WHERE SALARY = '50000';
Если процент выбираемых строк меньше или равен 15, индексное сканирование будет работать лучше, поскольку в этом случае для доступа в строке требуется несколько логических чтений блоков с диска, а при полном просмотре за одно логическое чтение обеспечивается доступ к всем строкам, находящимся в одном блоке. Чтобы проиллюстрировать эту мысль, предположим, что команда ANALYZE применяется к таблице EMP и всем ее индексам. Oracle генерирует следующую статистическую информацию в таблицах-каталогах USER_TABLES и USER_INDEXES:
Table Statistics: NUM_ROWS = 1000 BLOCKS = 100 Index Statistics: BLEVEL = 2 AVG_LEAF_BLOCKS_PER_KEY = 1 AVG_DATA_BLOCKS_PER_KEY = 1
На основе этой статистики для различных типов сканирования потребуется следующее число логических чтений блоков:
При использовании индекса для выбора одной строки - 3: (BLEVEL + (AVG_LEAF_BLOCKS_PER_KEY - 1) + AVG_DATA_PER_KEY).
При полном просмотре таблицы без индекса - 100.
При использовании индекса для выбора всех строк - 3000: (NUM_ROWS * число блоков, чтение которых нужно для выбора одной строки).
13. Используйте ORDER BY для индексного сканирования.
Оптимизатор Oracle будет использовать индексное сканирование, если запрос содержит раздел ORDER BY с указанием индексированного столбца. Для выполнения следующего запроса будет использован индекс на столбце EMPID, даже если этот столбец не используется в условиях раздела WHERE. Для каждой строки из индекса будет извлекаться ROWID, а потом с использованием ROWID будет производиться обращение к строке.
SELECT SALARY
FROM EMP
ORDER BY EMPID;
Если запрос будет плохо выполняться, можно попробовать переписать его с использованием указания FULL (см. 12-ую заповедь).
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 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-сервер, обработать их и получить результаты на стороне клиента.