Настройка ваших предложений SQL - важная часть процесса достижения наилучшей возможной производительности ORACLE. Вы должны настроить ваши предложения SQL до того, как ваш администратор базы данных настроит сам ORACLE:  Даже если вы не знакомы с внутренними тонкостями ORACLE, вы можете существенно улучшить производительность путем настройки вашего приложения на основе знания того, как ORACLE исполняет предложения SQL.  Если ваши предложения SQL не настроены, они могут выполняться неэффективно, даже если сам ORACLE хорошо настроен.

Эта глава рассказывает вам:

* как писать предложения SQL для лучшей производительности

ваших новых приложений

* как оптимизировать производительность предложений SQL в

ваших существующих приложениях

Эта глава предполагает, что вы знакомы с понятием плана

исполнения, и знаете, как он генерируется оптимизатором ORACLE.

Эта информация приведена в главе 13 "Оптимизатор" документа

ORACLE7 Server Concepts Manual.

После того, как вы настроите ваши предложения SQL, администратор

вашей базы данных может приступить к настройке ORACLE, как

описывается в главе 20 "Понимание процесса настройки" документа

ORACLE7 Server Administrator's Guide.

Как писать новые предложения SQL

Если вы пишете предложения SQL в новом приложении, выполните

следующие шаги, чтобы оптимизировать эти предложения:

* Создайте индексы, которые смогут использоваться вашими

предложениями.

* Создайте кластеры, чтобы оптимизировать ваши операции

соединения.

* Создайте хэш-кластеры, которые смогут использоваться

вашими предложениями.

* Выберите подход к оптимизации для ваших предложений.

* Используйте советы в ваших предложениях, где это имеет

смысл.

* Проведите сравнение альтернативных синтаксисов для ваших

предложений.

Все эти шаги обсуждаются в последующих секциях.

Как использовать индексы

------------------------

Эта секция дает рекомендации по созданию индексов и обсуждает

следующие вопросы:

* как решить, когда создавать индексы

* как выбрать, какие столбцы индексировать

* как использовать составные индексы

* как писать предложения, использующие индексы

Приняв решение о создании индекса, вы можете выдать команду

CREATE INDEX, чтобы создать этот индекс. Для дополнительной

информации о создании индексов обратитесь к главе 2 этого

руководства, "Управление объектами схемы".

Когда создавать индексы

Индексы улучшают производительность тех запросов, которые

выбирают небольшой процент строк из таблицы. Как общее правило,

вы должны создавать индексы по таблицам, из которых часто

выбирается 2-4% строк. Это правило основывается на следующих

предположениях:

* Строки с одинаковыми значениями того столбца, на котором

построен запрос, равномерно распределены между блоками

данных, распределенными таблице.

* Строки в таблице упорядочены случайным образом по

отношению к столбцу, на котором построен запрос.

* Каждый блок данных, распределенный таблице, содержит по

меньшей мере 10 строк.

* Таблица содержит относительно малое количество столбцов.

* Большинство запросов по таблице имеют относительно

простые фразы WHERE.

Если эти предположения не характеризуют данные в вашей таблице и

запросы, выбирающие их, то процент выбираемых строк, при котором

индекс полезен, может возрасти вплоть до 25%.

Как выбирать столбцы для индекса

При выборе столбцов для индекса руководствуйтесь следующими

правилами:

* Индексируйте столбцы, которые часто используются в фразах

WHERE.

* Индексируйте столбцы, которые часто используются для

соединения таблиц в предложениях SQL. Для дополнительной

информации об оптимизации соединений обратитесь к секции

"Как использовать кластеры" на странице 5-8.

* Индексируйте лишь столбцы, обладающие хорошей

селективностью. СЕЛЕКТИВНОСТЬ столбца - это процент

строк, имеющих одинаковое значение для индексированного

столбца. Селективность столбца хороша, если мало строк

имеют одинаковые значения для этого столбца. Заметьте,

что ORACLE неявно создает индексы по столбцам,

определенным (через ограничения целостности) как

первичные или уникальные ключи. Такие индексы наиболее

селективны и наиболее эффективны для оптимизации

производительности.

Вы можете определить селективность столбца, поделив число

строк в таблице на число различных индексированных

значений. Вы можете получить эти значения с помощью

команды ANALYZE. Селективность, вычисленная таким

способом, должна трактоваться как процент.

* Не индексируйте столбцы, имеющие мало различающихся

значений. Такие столбцы обычно имеют низкую

селективность, и потому не оптимизируют

производительность, если только часто выбираемые значения

столбца не встречаются намного реже, чем остальные

значения этого столбца.

Например, рассмотрим столбец, который содержит равные

количества значений 'YES' и 'NO'. Индексирование такого

столбца обычно не приводит к улучшению

производительности. Однако, если значение 'YES'

встречается относительно редко, а ваше приложение часто

опрашивает на 'YES', то индексирование такого столбца

может улучшить производительность.

* Не индексируйте столбцы, которые часто модифицируются.

Предложения UPDATE, модифицирующие индексированные

столбцы, и предложения INSERT и DELETE, модифицирующие

индексированные таблицы, выполняются дольше, чем если бы

индекса не было. Такие предложения SQL должны

модифицировать данные в индексах, когда они модифицируют

данные в таблице.

* Не индексируйте столбцы, которые появляются в фразах

WHERE только с функциями или операторами. Фраза WHERE,

использующая с индексированным столбцом функцию (отличную

от MIN или MAX) или оператор, не делает возможным путь

доступа, использующий индекс по этому столбцу.

* Индексируйте внешние ключи, входящие в ограничения

ссылочной целостности, в тех случаях, когда большое

количество одновременных предложений INSERT, UPDATE и

DELETE обращаются к родительской и порожденной таблицам.

Такой индекс позволяет ORACLE модифицировать данные в

порожденной таблице, не блокируя родительскую таблицу.

Решая, индексировать ли данный столбец, учитывайте, компенсирует

ли выигрыш в производительности, достигаемый для запросов, тех

потерь производительности, которые будут иметь место для

предложений INSERT, UPDATE и DELETE, а также той памяти, которая

будет затрачена на индекс. Вы можете провести эксперименты и

сравнить время обработки ваших предложений SQL с индексами и без

них. Время обработки можно измерять с помощью средства

трассировки SQL. Для информации о средстве трассировки SQL

обратитесь к приложению B "Инструменты диагностики

производительности" в этом руководстве.

Как выбирать составные индексы

СОСТАВНОЙ ИНДЕКС - это индекс, состоящий из более чем одного

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

преимущества по сравнению с одностолбцовыми индексами:

лучшая Иногда можно скомбинировать два или более

селективность столбцов, каждый из которых обладает низкой

селективностью, в составной индекс, имеющий

хорошую селективность.

дополнительный Если все столбцы, выбираемые запросом, входят в

источник составной индекс, то ORACLE может возвратить эти

данных значения прямо из индекса, не обращаясь к

таблице.

Предложение SQL может использовать путь доступа, включающий

составной индекс, если это предложение содержит конструкты,

которые используют ведущую порцию индекса. ВЕДУЩАЯ ПОРЦИЯ

индекса - это один или несколько столбцов, которые были

специфицированы первыми и подряд в списке столбцов предложения

CREATE INDEX, с помощью которого был создан индекс. Рассмотрим

следующее предложение CREATE INDEX:

CREATE INDEX comp_ind

ON tab1(x, y, z)

Следующие комбинации столбцов являются ведущими порциями этого

индекса: X, XY и XYZ. Другие комбинации столбцов, например, XZ,

YZ или Z, не являются ведущими порциями этого индекса.

При выборе столбцов для составных индексов руководствуйтесь

следующими правилами:

* Создавайте составной индекс по тем столбцам, которые

часто используются вместе в условиях фразы WHERE, будучи

соединены операторами AND, особенно если их

комбинированная селективность лучше, чем индивидуальная

селективность каждого столбца по отдельности. Если

некоторые из столбцов, составляющих индекс, используются

в фразах WHERE более часто, не забудьте обеспечить, чтобы

эти столбцы составляли ведущую порцию индекса, с тем,

чтобы предложения, использующие только эти столбцы, могли

использовать доступ через индекс.

* Если существует ряд запросов, базирующихся на одном и том

же наборе столбцов, рассмотрите возможность создания

составного индекса, в который вошли бы все эти столбцы,

образуя его ведущую порцию.

* Если составной индекс должен использоваться в запросах,

базирующихся на значениях нескольких столбцов, то

упорядочение этих столбцов от более селективного к менее

селективному в предложении CREATE INDEX лучше всего

повышает производительность запросов.

Разумеется, необходимо учитывать и вопросы, касающиеся общих

преимуществ и недостатков индексов, которые рассматривались в

предыдущих секциях.

Как писать предложения, использующие индексы

После того, как вы создали индекс, оптимизатор не сможет

использовать путь доступа через этот индекс просто потому, что

он существует. Оптимизатор может выбрать такой путь доступа для

предложения SQL лишь тогда, когда это предложение содержит

конструкт, делающий этот путь возможным. Для информации о путях

доступа и конструктах, делающих их возможными, обратитесь к

главе 13 "Оптимизатор" документа ORACLE7 Server Concepts Manual.

Чтобы гарантировать, что предложение SQL может использовать путь

доступа через индекс, обеспечьте, чтобы это предложение

содержало конструкт, делающий этот путь возможным. Если вы

используете стоимостной подход, вы должны также сгенерировать

статистики для индекса. После того, как вы сделаете путь

доступа через индекс возможным для предложения, оптимизатор

сможет выбрать или не выбрать его, в зависимости от других

возможных путей доступа.

Как писать предложения, избегающие использования индексов

В некоторых случаях вы можете захотеть предотвратить возможность

использования пути доступа через существующий индекс. Например,

вы можете знать, что индекс очень селективен для вашего случая,

и что полный просмотр таблицы был бы более эффективен. Если

предложение содержит конструкт, который делает возможным путь

доступа через индекс, вы можете заставить оптимизатор

использовать полный просмотр таблицы с помощью одного из

следующих методов:

* Вы можете сделать индексный путь доступа недоступным,

модифицировав предложение так, чтобы не изменить его

смысла. Этот метод проиллюстрирован на примере,

показанном ниже.

* Вы можете использовать совет FULL, чтобы заставить

оптимизатор выбрать полный просмотр таблицы вместо

просмотра индекса.

* Вы можете использовать совет INDEX или AND_EQUAL, чтобы

заставить оптимизатор использовать один индекс или набор

индексов вместо другого.

Так как поведение оптимизатора может измениться в будущих

версиях ORACLE, полагаться на первый метод для выбора пути

доступа в долгосрочном плане не следует. Вместо этого

используйте советы, чтобы предлагать оптимизатору конкретные

пути доступа. Для информации о советах обратитесь к секции "Как

использовать советы" на странице 5-14.

Пример

Рассмотрим следующие запросы, которые выбирают строки из

таблицы, базируясь на значении единственного столбца:

SELECT *

FROM tab1

WHERE col1 = 'A'

SELECT *

FROM tab1

WHERE col1 = 'B'

Предположим, что значениями столбца COL1 являются буквы от A до

Z. Предположим также, что таблица имеет 1000 строк, и что 75%

этих строк имеют значение столбца COL1, равное 'A'. Каждая из

остальных букв появляется в 1% строк.

Так как значение 'A' появляется в 75% строк таблицы, первый

запрос, вероятно, был бы выполнен быстрее путем полного

просмотра таблицы, чем через просмотр индекса по столбцу COL1.

Так как значение 'B' появляется в 1% строк, для второго запроса

эффективнее был бы просмотр индекса по столбцу COL1.

Следовательно, желательно создать индекс для второго запроса, но

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

Однако количество экземпляров каждого конкретного значения

столбца COL1 неизвестно оптимизатору. Оптимизатор выберет один

и тот же путь доступа для обоих запросов, несмотря на

существенную разницу в процентах строк, возвращаемых каждым

запросом.

Чтобы добиться лучшей производительности для обоих запросов,

создайте индекс по столбцу TAB1.COL1, чтобы он мог

использоваться вторым запросом:

CREATE INDEX col1_ind

ON tab1(col1)

Модифицируйте фразу WHERE первого запроса так, чтобы она сделала

невозможным путь доступа через индекс COL1_IND:

SELECT *

FROM tab1

WHERE col1 || '' = 'A'