Выпуск 68

Уважаемые подписчики рассылки!

В этом выпуске мы рассмотрим некоторые аспекты использования таблиц, организованных по индексу (IOT). По мотивам интересного обсуждения на сайте Тома Кайта, которое началось еще в мае 2000 года.

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

Меня интересует, как повторно использовать пространство в таблице, организованной по индексу (Index Organized Table - IOT) после удаления существенного количества строк.

При работе с обычной таблицей после удаления множества строк я пересоздаю индексы, чтобы в индексных блоках не было пропусков, поскольку мы знаем, что это пустое место в индексах не будет использоваться повторно (в отличие от блоков таблиц, где это освободившееся место используется повторно, после того, как будет преодолен порог PCTUSED в блоке).

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

Ответ Тома Кайта

Ответ на этот вопрос, на самом деле, достаточно интересный - Oracle8i Release 8.1 позволяет выполнить два новых действия, которые делают ответ интересным:

Поскольку таблица, организованная по индексу, - это просто индекс... Мы, фактически, можем пересоздать индекс путем переноса таблицы "на ходу" (т.е. пока происходит пересоздание пользователи изменяют данные таблицы...)

Вот пример:

ops$tkyte@dev8i> create table demo_iot
  2  ( object_id int primary key,
  3    oname     varchar2(30),
  4    owner     varchar2(30),
  5    status    varchar2(30) )
  6  organization index;

Table created.

ops$tkyte@dev8i> insert into demo_iot
  2  select object_id, object_name, owner, status
  3    from all_objects
  4  /

22525 rows created.

ops$tkyte@dev8i> column index_name new_value iname
ops$tkyte@dev8i> select index_name
  2    from user_indexes
  3   where table_name = 'DEMO_IOT'
  4  /

INDEX_NAME
------------------------------
SYS_IOT_TOP_87241

ops$tkyte@dev8i> analyze index &iname validate structure;
old   1: analyze index &iname validate structure
new   1: analyze index SYS_IOT_TOP_87241 validate structure
Index analyzed.

ops$tkyte@dev8i> select height, blocks, del_lf_rows from index_stats;

    HEIGHT     BLOCKS DEL_LF_ROWS
---------- ---------- -----------
         2        192           0

Теперь мы просто удалим примерно половину строк таблицы. Будем удалять строки "через одну".

ops$tkyte@dev8i> delete from demo_iot where mod(object_id,2) = 1;
11270 rows deleted.

ops$tkyte@dev8i> analyze index &iname validate structure;
old   1: analyze index &iname validate structure
new   1: analyze index SYS_IOT_TOP_87241 validate structure

Index analyzed.

ops$tkyte@dev8i> select height, blocks, del_lf_rows from index_stats;

    HEIGHT     BLOCKS DEL_LF_ROWS
---------- ---------- -----------
         2        192       11270

Итак, у нас в индексе достаточно много удаленных строк (ни один из блоков не стал полностью пустым). Как это "почистить"?

ops$tkyte@dev8i> alter table demo_iot move online;
Index altered.

ops$tkyte@dev8i> analyze index &iname validate structure;
old   1: analyze index &iname validate structure
new   1: analyze index SYS_IOT_TOP_87241 validate structure

Index analyzed.

ops$tkyte@dev8i> select height, blocks, del_lf_rows from index_stats;

    HEIGHT     BLOCKS DEL_LF_ROWS
---------- ---------- -----------
         2        128           0

Вот что мы получили - все "вычищено". В качестве теста, можете оставить открытыми другие сеансы, пока выполняется alter table move - просто чтобы убедиться, что таблица доступна для запросов и всех операторов DML.

Комментарий читателя от 23 августа 2001 года

Вы говорили про два метода. Один из них - оперативное пересоздание индекса.

Я попробовал его применить, но ничего не получилось.

alter index SYS_IOT_TOP_87241 rebuild
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

Ответ Тома Кайта

См. http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76956/tables.htm#1913

Это делается с помощью оператора:

ALTER TABLE docindex MOVE ONLINE;

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

Если, скажем, у меня есть организованная по индексу таблица T со столбцами a, b, c, d, причем, столбцы a,b образуют первичный ключ.

Мне же нужно, чтобы первичным ключом были столбцы a,b,c. Нет ли оператора alter table, позволяющего изменить таблицу, организованную по индексу, и добавить еще один столбец в составной первичный ключ?

Ответ Тома Кайта

В Oracle9i можно использовать оперативное пересоздание.

В 8i, придется использовать Create table ... as select ..., удалить старую таблицу и переименовать новую.

Префикс...

Если, скажем, у меня есть организованная по индексу таблица T со столбцами a, b, c, d... Но теперь первичный ключ образуют столбцы a, b, c, именно в таком порядке.

Я обнаружил, что в большинстве запросов у меня используется условие по столбцам a,c. Будет ли при этом полезен составной ключ по столбцам a,b,c? По сравнению с составным ключом по столбцам a,c?

Ответ Тома Кайта

Первичный ключ - это первичный ключ, это его основное свойство.

Если в большинстве запросов обращение к таблице идет по столбцам a,c, то первичный ключ должен быть по столбцам a,c,b

Вопрос вдогонку...

Половина моих запросов - по a,c, а другая половина - по a,b.

Если создать составной первичный ключ по столбцам a,b,c, будет ли он использоваться для всех этих запросов? По a,b запросов немного больше, чем по a,c.

Я попытался на сервере для разработки использовать таблицу, организованную по индексу, вместо обычной таблицы, и полученная с помощью tkprof разница оказалась достаточной, чтобы оправдать такую реализацию и на производственном сервере.

Система относится к классу 24x7, и время простоя надо свести к минимуму.

Как мне заморозить упомянутую таблицу? Нет ли оператора "alter table <имя_таблицы> read only" - не могу его найти в 8i. Или исходная табллица будет заморожена оператором create table ... as select ...?

Я собираюсь в периоды минимальной нагрузки сделать следующее:

  1. переименовать таблицу в <temp>- чтобы дальнейшие изменения не происходили
  2. create table <исходная таблица> as select from <temp>

Я проверил зависимости других объектов от этой таблицы, и не нашел их. На первичный ключ таблицы не ссылаются внешние ключи других таблиц, а сама эта таблица не содержит внешних ключей.

Ответ Тома Кайта

В одном сеансе:

lock table t in exclusive mode;

а в другом скопируйте ее.

Потом в первом сеансе удалите, а новую таблицу переименуйте.

В 8i, если запросы выбирают данные по a,b и a,c, скорее всего, надо создать индексы по:

a,b,c
c

Отдельно по столбцу "c" потому, что все индексы таблицы, организованной по индексу, и так включают первичный ключ. Рассмотрим пример:

ops$tkyte@ORA817DEV> create table t (a int, b int, c int, d char(20), 
primary key (a,b,c) ) organization index;

Table created.

ops$tkyte@ORA817DEV> insert into t
  2  select rownum, -rownum, rownum*2, 'x'
  3  from all_objects where rownum <= 5000;

5000 rows created.

ops$tkyte@ORA817DEV> create index t_idx on t(c);

Index created.

ops$tkyte@ORA817DEV> analyze table t compute statistics for table for all 
indexes for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV> select a,c from t where c = 44 and a = 22;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=6)

ops$tkyte@ORA817DEV> set autotrace off

Видите, как удалось выполнить запрос исключительно по индексу? Для получения значения A вообще не пришлось обращаться к таблице, - оно есть в индексе.

Требуется ли пересоздание индекса?

Почему мы должны пересоздавать индексы?. Вы же против пересоздания индексов. Вот ваш ответ:

http://asktom.oracle.com/pls/ask/f?p=4950:8:444551828551181757::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601312252730,

Прокомментируйте, пожалуйста.

Ответ Тома Кайта

Я НЕ ПРОТИВ пересоздания вообще.

Я - против регулярного пересоздания индексов лишь потому, что "все знают, что так надо делать".

Я против выполнения действий, про которые не известно, что они:

Индексы на основе битовых карт (bitmap indexes) могут потребовать пересоздания после выполнения некоторого количества операторов DML.

Мой текстовый индекс на сайте asktom - я его время от времени пересоздаю, после существенного изменения данных (фактически, он очень похож на bitmap index).

Индексы на основе b*-деревьев - вряд ли их вообще когда-либо стоит пересоздавать (подсказка: почитайте про COALESCE, - дает большинство тех же преимуществ, а работы намного меньше).

Комментарий читателя от 16 июля 2003 года

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

create table category
  (name VARCHAR2(50)
     CONSTRAINT category$name$nn NOT NULL
  ,display_yn VARCHAR2(1)     
     CONSTRAINT category$display$nn NOT NULL
  ,CONSTRAINT category$pk 
     PRIMARY KEY (name)
  ) 
  organization index tablespace indx
  including display_yn overflow;

alter table category add constraint category$display$ck01
  check(display_yn in ('Y','N'));

create unique index category$name$x on category(UPPER(name))
  tablespace indx;

Меня интересует следующее:

1. По сути, есть два индекса по одному столбцу таблицы. Эффективно ли это, и как бы обойтись одним индексом?

NEREUS@mdl1> select index_name, index_type from dba_indexes
  2  where owner='NEREUS' and index_name like 'CATEGORY%';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
CATEGORY$NAME$X                FUNCTION-BASED NORMAL
CATEGORY$PK                    IOT - TOP

2. Как задать явное имя для таблицы переполнения (OVERFLOW)?

NEREUS@mdl1> select table_name, iot_name from dba_tables
  2  where owner='NEREUS'
  3  and table_name='CATEGORY'
  4  or IOT_NAME='CATEGORY';

TABLE_NAME                     IOT_NAME
------------------------------ -----------------------------
CATEGORY
SYS_IOT_OVER_27251             CATEGORY

Хотелось бы также узнать ваши рекомендации по повышению производительности.

Ответ Тома Кайта

1) На самом деле, я вижу только ОДИН индекс, - по функции, который вы создали. Другой "индекс" - это, фактически, сама таблица.

2) Сегмент переполнения в этот случае не нужен и нежелателен. Фактически, я бы сказал, что, если вам нужен сегмент переполнения, то вам вряд ли нужна организация таблицы по индексу (бывают, конечно, и исключения).

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

create table category
  (upper_name varchar2(50) constraint cat$pk primary key,
   name VARCHAR2(50)  CONSTRAINT category$name$nn NOT NULL,
   display_yn VARCHAR2(1) CONSTRAINT category$display$nn NOT NULL
  ) 
  organization index;

и просто вставлять в нее имя в верхнем регистре, имя и display_yn.

При этом вы получаете: