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

Нельзя не учитывать важность эффективных 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. Внимательно относитесь к использованию индексов на таблицах.

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

• Создавайте индексы на столбцах, которые часто используются в разделе WHERE встроенных операторов SQL или запросов, используемых конечными пользователями.
• Индексируйте столбцы, часто используемые в операторах SQL для соединения таблиц.
• Используйте для индексирования только те столбцы, в которые входит небольшой процент строк с одним и тем же значением.
• Не индексируйте столбцы, которые используются только в функциях и операторах раздела WHERE запросов.
• Не индексируйте часто изменяемые столбцы, а также не применяйте индексацию в тех случаях, когда повышение эффективности за счет создания индекса приводит к снижению эффективности при выполнении операций INSERT, UPDATE и DELETE. Выполнение этих операций замедлится из-за необходимости поддерживать индексы.
• Уникальные индексы лучше неуникальных по причине их более высокой селективности. Используйте уникальные индексы на столбцах первичного ключа и неуникальные индексы на столбцах внешнего ключа и столбцах, часто используемых в разделе WHERE.
• Создавайте индексы таким образом, чтобы столбцы, используемые в разделе WHERE составляли начальную группу столбцов в ключе индекса.

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):

• COL1 > COL2
• COL1 < COL2
• COL1 >= COL2
• COL1 <= COL2
• COL1 IS NULL
• COL1 IS NOT NULL (В индексе не сохраняются идентификаторы строк - ROWID - для столбцов, содержащих неопределенные значения. Поэтому для выполнения запросов строк с неопределенными значениями индекс не может быть использован.)
• COL1 NOT IN (value1, value2)
• COL1 != expression
• COL1 LIKE '%patern' (В этом случае начальная составляющая ключа индекса не указывается и поэтому индекс не может быть использован. С другой стороны, для COL1 LIKE 'patern%' и COL1 LIKE 'patern%patern%' индекс может использоваться в режиме сканирования в диапазоне значений ключа.)
• NOT EXISTS subquery
• expression1 = expression2 (Любые выражения, функции и вычисления, включающие индексированные столбцы, препятствуют использованию индекса. Например, в следующем примере наличие функции UPPER не дает возможность использовать сканирование по индексу, и будет применен полный просмотр таблицы:

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 BE для индексного сканирования.

Оптимизатор Oracle будет использовать индексное сканирование, если запрос содержит раздел ORDER BY с указанием индексированного столбца. Для выполнения следующего запроса будет использован индекс на столбце EMPID, даже если этот столбец не используется в условиях раздела WHERE. Для каждой строки из индекса будет извлекаться ROWID, а потом с использованием ROWID будет производиться обращение к строке.

SELECT SALARY
FROM EMP
ORDER BY EMPID;

Если запрос будет плохо выполняться, можно попробовать переписать его с использованием указания FULL (см. 12-ую заповедь).

Часть 2 >>

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

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