Особливості використання таблиць, організованих за індексом, Інші СУБД, Бази даних, статті

У цьому випуску ми розглянемо деякі аспекти використання таблиць, організованих за індексом (IOT).

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


Мене цікавить, як повторно використовувати простір в таблиці, організованої за індексом (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 різниця виявилася достатньою, щоб виправдати таку реалізацію та на виробничому сервері.


Система відноситься до класу 24×7, і час простою треба звести до мінімуму.


Як мені заморозити згадану таблицю? Чи немає оператора “alter table <ім'я_таблиці> read only“- Не можу його знайти в 8i. Або вихідна таблліца буде заморожена оператором create table … as select …?


Я збираюся в періоди мінімального навантаження зробити наступне:



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

Я перевірив залежності інших об’єктів від цієї таблиці, і не знайшов їх. На первинний ключ таблиці не посилаються зовнішні ключі інших таблиць, а сама ця таблиця не містить зовнішніх ключів.


Відповідь Тома Кайта


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

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.


При цьому ви отримуєте:


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*