Индексы 385

При отсутствии актуальной статистической информации оптимизатор, основанный на стоимости, не может принимать правильные решения.

По моему опыту, эти шесть случаев демонстрируют основн1е причины, по которым не используются индексы. Все в конечном итоге сводится к тому, что их нельзя использовать, потому что это даст неверные результаты или их нет смысла использовать, потому что это снизит производительность .

Использовались ли индексы?

На этот вопрос ответить сложно. Сервер Oracle не отслеживает обращения к индексам, так что нельзя просто посчитать записи в проверочной таблице (audit trail table) или предложить аналогичное по простоте решение. Для решения этой проблемы простого способа нет. В версии Oracle 8i, однако, можно использовать два подхода.

В главе 11 я буду описывать, как хранимые шаблоны запросов (stored query outlines) - средство сохранения подсказок для выбора плана выполнения запроса сервером Oracle в таблице базы данных - могут использоваться для определения того, какие индексы используются. Можно включить хранение (но не использование) планов. При этом можно записать все планы для всех выполненных запросов, не изменяя приложения. Затем можно выполнить запрос к таблицам шаблонов и определить, какие методы доступа по индексу использовались, и даже попытаться выяснить, для каких именно запросов использовались индексы.

Еще один метод - поместить каждый индекс в отдельное табличное пространство или в отдельный файл в табличном пространстве. Сервер Oracle отслеживает ввод/вывод в каждый файл (доступ к этой информации можно получить через представление динамической производительности V$FILESTAT). Если для табличного пространства с индексом количество чтений примерно соответствует количеству записей, понятно, что этот индекс не используется для доступа к данным. Он читался сервером, только когда изменялся (сервер Oracle изменяет индекс при выполнении операторов INSERT, UPDATE и DELETE). Если табличное пространство практически не читается, понятно, что индекс не используется и таблица изменяется не часто. Если же чтений выполняется больше, чем записей, - это свидетельствует о том, что индекс используется.

Недостаток этих подходов в том, что, даже если удастся разобраться, какие индексы использовались, а какие - нет, все равно непонятно, правильн1й ли это набор индексов для имеющихся данных. Эти подходы не подскажут, что при простом добавлении столбца X к индексу Y можно избежать обращения к таблице по идентификатору строки и тем самым существенно повысить эффективность запроса. Один из общих способов оптимизации состоит в добавлении столбцов в конец индекса так, чтобы для ответов на запросы использовался только индекс и к таблице обращаться вообще не пришлось. Так нельзя понять, что есть избыточные индексы. Например, при наличии индексов по Т(Х), T(X,Y) и T(X,Y,Z), первые два, вероятно, можно удалить, не снизив при этом производительности запросов и ускорив выполнение изменений, хотя бывают случаи, когда это неверно. Идея в том, что хорошо спроектированная и документированная система должна периодически проверять, используются ли имеющиеся индек-

Глава 7

сы, поскольку последствия наличия индексов надо продумывать заранее для всей системы в целом, а не только для отдельных запросов. В быстро изменяющейся системе с большим количеством разработчиков, постоянно подключающихся и уходящих из проекта, это обычно не делается.

Миф: пространство в индексе никогда повторно не используется

Этот миф я хочу развеять раз и навсегда: пространство в индексе используется повторно.

Миф этот возникает следующим образом: имеется таблица Т со столбцом X. В некоторый момент времени в таблицу добавляется строка со значением X = 5. Затем вы ее удаляете. Миф состоит в том, что пространство, выделенное для записи X = 5, не будет использоваться повторно до тех пор, пока в индекс опять не будет вставлена запись со значением X = 5. Миф утверждает, что, как только слот индекса использован, он остается занятым навсегда, и может использоваться только под такое же значение. Этот миф дополняется мифом о том, что освободившееся в индексе пространство никогда индексной структуре не возвращается, и блок индекса никогда не используется повторно. Это тоже неправда.

Опровергнуть первую часть мифа просто. Достаточно создать следующую таблицу:

tkyte@ORA8I.WORLD> create table t (x int, constraint t pk primary key(x)); Table created.

tkyte@ORA8I.WORLD insert into t values (1) ; 1 row created.

tkyte@ORA8I.WORLD> insert into t values (2);

1 row created.

tkyte@ORA8I.WORLD> insert into t values (9999999999); 1 row created.

tkyte@ORA8I.WORLD> exec show space(T PK, user, INDEX);

Free Blocks.......................0

Total Blocks......................64

Unused Blocks.....................62

PL/SQL procedure successfully completed.

Итак, в соответствии с мифом, если выполнить оператор delete irom T where x = 2,

это пространство не будет использоваться повторно до тех пор, пока не будет повторно вставлено значение 2. Сейчас индекс использует два блока: один - для карты экстентов, второй - для данных индекса. Если при удалении записи индекса никогда повторно не используются, а я буду вставлять и удалять строки, не используя прежние значения, то индекс должен чрезмерно разрастись. Давайте посмотрим:

ops$tkyte@ORA8I.WORLD>begin

2 for i in 2 . . 999999

3 loop

4 delete from t where x = i;

Индексы 387

5 commit;

6 insert into t values (i+1);

7 commit;

8 end loop;

9 end; 10 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> exec show space(T PK, user, INDEX);

Free Blocks.......................0

Total Blocks......................64

Unused Blocks.....................62

PL/SQL procedure successfully completed.

Итак, это показывает, что пространство в индексе повторно используется. Как и в большинстве мифов, однако, доля правды в мифе все-таки есть. Правда в том, что пространство, выделенное первоначальному значению 2 (в диапазоне от 1 до 9999999999), останется в этом блоке индекса навсегда. Индекс сам себя не уплотняет . Это означает, что если загрузить в таблицу значения от 1 до 500000, а затем удалить, скажем, все строки с нечетными значениями, в индексе по этому столбцу будет 250000 дырок . Только при повторной вставке данных, которые попадают в блок с дыркой, соответствующее пространство будет повторно использовано. Сервер Oracle не будет пытаться сжать или уменьшить индекс. Это можно сделать с помощью операторов ALTER INDEX REBUILD или COALESCE. С другой стороны, если загрузить в таблицу значения от 1 до 500000, а затем удалить все строки со значениями, меньшими 250000, окажется, что освободившиеся блоки индекса снова возвращены в список свободных мест индекса. Это пространство полностью может быть использовано повторно. Если помните, второй миф утверждал как раз обратное. Утверждалось, что выделенное индексу пространство никогда не возвращается . В соответствии с этим мифом, после того как блок индекса использован, он оказывается в соответствующем месте структуры индекса навсегда и будет использован повторно только при вставке данных, которые должны были бы попасть в это место структуры индекса. Можно показать, что и это неправда. Сначала построим таблицу с примерно 500000 строк:

ops$tkyte@ORA8I.WORLD> create table t

2 ( x int ) ;

Table created.

ops$tkyte@ORA8I.WORLD> insert /*+ APPEND */ into t select rownum from

all objects;

30402 rows created.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.

ops$tkyte@ORA8I.WORLD> insert /*+ APPEND */ into t

2 select rownum+cnt from t, (select count(*) cnt from t) ; 30402 rows created.

ops$tkyte@ORA8I.WORLD> commit; Commit complete.