Двадцать пять заповедей SQL - Сравните сканирование через индекс с полным просмотром таблицы

Written on . Posted in Язык запросов SQL

ОГЛАВЛЕНИЕ

Страница 13 из 26
 

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 * число блоков, чтение которых нужно для выбора одной строки).

базы данных форум