Особливості використання таблиць, організованих за індексом - oracle - програмні продукти
Мене цікавить, як повторно використовувати простір в таблиці, організованої за індексом (Index Organized Table - IOT) після видалення істотної кількості рядків.
При роботі зі звичайною таблицею після видалення безлічі рядків я перебудував індекси, щоб в індексних блоках не було пропусків, оскільки ми знаємо, що це пусте місце в індексах не використовуватиметься повторно (на відміну від блоків таблиць, де це місце, що звільнилося використовується повторно, після того, як буде подолано поріг PCTUSED в блоці).
Отже, що ж можна зробити з таблицею, організованої за індексом, щоб запобігти її постійне зростання, навіть після видалення безлічі рядків?
Відповідь Тома Кайта
Відповідь на це питання, насправді, досить цікавий - Oracle8i Release 8.1 дозволяє виконати два нових дії, які роблять відповідь цікавим:
- оперативне пересозданіе індексів;
- перенос таблиці.
Оскільки таблиця, організована за індексом, - це просто індекс. Ми, фактично, можемо перебудувати індекс шляхом перенесення таблиці "на ходу" (тобто поки відбувається пересозданіе користувачі змінюють дані таблиці.)
Тепер ми просто видалимо приблизно половину рядків таблиці. Будемо видаляти рядки "через одну".
Отже, у нас в індексі досить багато віддалених рядків (жоден з блоків не став повністю порожнім). Як це "почистити"?
Ось що ми отримали - все "вичищено". Як тест, можете залишити відкритими інші сеанси, поки виконується alter table move - просто щоб переконатися, що таблиця доступна для запитів і всіх операторів DML.
Ви говорили про два методи. Один з них - оперативне пересозданіе індексу.
Я спробував його застосувати, але нічого не вийшло.
Відповідь Тома Кайта
Це робиться за допомогою оператора:
Зміна первинного ключа таблиці, організованої за індексом
Якщо, скажімо, у мене є організована за індексом таблиця 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.?
Я збираюся в періоди мінімального навантаження зробити наступне:
- перейменувати таблицю в
- щоб подальші зміни не відбувалися - create table <исходная таблица> as select from
Я перевірив залежності інших об'єктів від цієї таблиці, і не знайшов їх. На первинний ключ таблиці не посилаються зовнішні ключі інших таблиць, а сама ця таблиця не містить зовнішніх ключів.
Відповідь Тома Кайта
а в іншому скопіюйте її.
Потім в першому сеансі видаліть, а нову таблицю перейменуйте.
У 8i, якщо запити вибирають дані по a, b і a, c. швидше за все, треба створити індекси по:
Окремо по стовпчику "c" тому, що всі індекси таблиці, організованої за індексом, і так включають первинний ключ. Розглянемо приклад:
Бачите, як вдалося виконати запит виключно за індексом? Для отримання значення A взагалі не довелося звертатися до таблиці, - воно є в індексі.
Чи потрібна пересозданіе індексу?
Чому ми повинні пересоздавать індекси. Ви ж проти пересозданія індексів. Ось ваш відповідь:
Відповідь Тома Кайта
Я НЕ ПРОТИ пересозданія взагалі.
Я - проти регулярного пересозданія індексів лише тому, що "все знають, що так треба робити".
Я проти виконання дій, про які невідомо, що вони:
- роблять систему краще;
- не викликають негативних наслідків.
Індекси на основі бітових карт (bitmap indexes) можуть зажадати пересозданія після виконання деякої кількості операторів DML.
Мій текстовий індекс на сайті asktom - я його час від часу перестворює, після істотної зміни даних (фактично, він дуже схожий на bitmap index).
Індекси на основі b * -дерев - навряд чи їх взагалі коли-небудь стоїть пересоздавать (підказка: почитайте про COALESCE. - дає більшість тих же переваг, а роботи набагато менше).
Я створив таблицю, організовану за індексом, для використання в якості списку:
Мене цікавить наступне:
1. По суті, є два індексу по однім стовпці таблиці. Чи ефективно це, і як би обійтися одним індексом?
2. Як задати явне ім'я для таблиці переповнення (OVERFLOW)?
Хотілося б також дізнатися ваші рекомендації щодо підвищення продуктивності.
Відповідь Тома Кайта
1) Насправді, я бачу тільки ОДИН індекс, - по функції, який ви створили. Інший "індекс" - це, фактично, сама таблиця.
2) Сегмент переповнення в цей випадку не потрібен і небажаний. Фактично, я б сказав, що, якщо вам потрібен сегмент переповнення, то вам навряд чи потрібна організація таблиці за індексом (бувають, звичайно, і винятки).
Якщо ви завжди шукаєте рядок у верхньому регістрі, можна створити таблицю:
і просто вставляти в неї ім'я у верхньому регістрі, ім'я та display_yn.
При цьому ви отримуєте:
- одну лише таблицю, організовану за індексом;
- ніяких двозначностей, тому що зараз у вас в якості значення "первинного ключа" таблиці може бути як 'hello', так і 'Hello'.
Як сервер знаходить рядок в таблиці, організованої за індексом
Чи використовує сервер значення первинного ключа для швидкого пошуку рядка в таблиці, організованої за індексом? Він же не зберігає rowid. як в звичайному індексі на основі b-дерева? Чи не могли б ви пояснити, який механізм використовується.
Відповідь Тома Кайта
Зберігається "rowid", але універсальний, а не фізичний. І він, фактично, включає значення первинного ключа. Розглянемо приклад:
бачите, яким великим може бути rowid.
Ключ індексу містить.
Чи означає це, що в запису індексу по стовпцю (вторинного індексу) буде значення ключа, значення первинного ключа і rowid (логічний)?
Відповідь Тома Кайта
Так. Зверніть увагу, як в наступному прикладі звернення йде тільки до індексу T_IDX. а до таблиці взагалі не звертаються. Хоча індекс - тільки за стовпцем Y.
Розмір таблиці, організованої за індексом
Я займаюся перетворенням деяких "великих, але худих" таблиць з більш ніж 10000 рядків в організовані за індексом і виявив, що це істотно підвищує продуктивність.
Як ви думаєте, чи має сенс організовувати за індексом таблиці з менш ніж 10000 рядків? Чи дасть це суттєва перевага в порівнянні зі звичайною таблицею з індексами?
Відповідь Тома Кайта
Так, безсумнівно. При пошуку по ключу може знадобитися всього 1/3 операцій логічного введення-виведення. Якщо такий пошук виконується часто, 100 рядків в таблиці або 10000 - не важливо.
COALESCE або MOVE ONLINE
1) Коли використовувати ALTER iot_table COALESCE. а коли - ALTER iot_table MOVE ONLINE. Чи є випадки, коли використання COALESCE виправдано?
2) Якщо по таблиці iot_table є індекс і ми виконуємо MOVE ONLINE. чи треба пересоздавать цей індекс?
Відповідь Тома Кайта
1) Використовуйте coalesce для "стиснення" таблиці, організованої за індексом.
Використовуйте move для її перенесення. При цьому відбувається повне пересозданіе і потрібно вільний простір розміром з таблицю.
2) Індекси таблиці, організованої за індексом, засновані на первинних ключах. При перенесенні значення первинного ключа не змінюється, тому пересоздавать індекси не потрібно.
Індекси звичайної таблиці треба пересоздавать (змінилися фізичні ідентифікатори рядків), а індекси таблиці, організованої за індексом - немає.
Індекс з великим розміром блоку
Як один запис індексу може мати розмір більше 4000 байтів при розмірі блоку 2 Кбайта?
Відповідь Тома Кайта
Схоже на помилку, вони забули перевірити. Якщо в таблиці є рядки, які перевищують максимальний розмір, ви повинні були отримати:
якщо alter move успішно спрацює, ви отримаєте:
при спробі вставити дуже велику рядок.
При цьому жодної шкоди даних і т.п. не відбувається - просто пропущена перевірка перед переносом. Я вже виставив помилку за мотивами Вашого повідомлення з наступним тестовим прикладом:
Як можна звичайну таблицю зробити організованою за індексом?
Організація таблиці за індексом здається підходящої в моєму випадку. У нас в таблиці - 6 мільйонів рядків, звернення до яких завжди йде по одному індексу; в таблиці всього п'ять стовпців, і по 3 створюється індекс, так що, організація таблиці за індексом здається прекрасним рішенням. Але у мене є два питання.
Як реально "реорганізувати" цю таблицю за індексом? Якщо створити нову таблицю і виконати: insert into. select *. при шести мільйонах рядків, ця операція рано чи пізно закінчиться невдало (після того, як будуть зайняті всі сегменти відкату). Чи немає більш цікавого способу змінити організацію таблиці? А якщо немає, що, якщо я експортує таблицю, видалю її, створю таблицю з таким же ім'ям, але іншою організацією. Чи спрацює імпорт? Чи немає причини (інтенсивні вставки / зміни.), По якій не варто використовувати організацію таблиці за індексом?
І ще питання: чи можна секціонувати таблицю, організовану за індексом? У цьому, взагалі, є сенс?
Відповідь Тома Кайта
Все закінчиться вдало, якщо задати розмір сегментів відкоту відповідно до тих діями, які треба виконати (заради бога, я часто з 10 мільйонами і більше "широких" рядків працюю на своєму ноутбуці. На ноутбуці!)
Ви можете секціонувати таблицю, організовану за індексом, так.
Так, в цьому є сенс (але 6 мільйонів рядків, все одно, це трохи)
то виявиться, що використовується, приблизно, аж один блок в сегменті відкату :) Ви турбуєтеся про те, що не повинно статися (а в режимі archive log ви можете виконувати цю дію в режимі nologging. погодивши з АБД виконання резервного копіювання цих нових даних як якомога швидше, і усунути тим самим будь-які проблеми з журналом повторного виконання, які ви могли собі надумати):
і використаний був один блок UNDO!
Секціонірованная за діапазоном, організована за індексом стисла таблиця
У 9iR2 у мене є таблиця з 3 стовпців, з, приблизно, мільярдом рядків, та ще й швидко зростаюча. Я хотів би секціонувати її по діапазону, стиснути і організувати за індексом.
Я не дуже багато хочу. -)
Я вже створив Секціонірованние таблицю, організовану за індексом, і, схоже, продуктивність (і не тільки) істотно підвищилася, за багатьма критеріями, на яких я не хочу зупинятися.
Я займуся тестуванням надійності / швидкості та інших аспектів стиснення даних в таблиці (вже організованою так, як описано вище), але основне питання в тому, чи є таке рішення (якщо тестування дасть позитивні результати) стабільним і відповідним для "промислового" використання? Тобто чи немає відомих помилок, "нюансів" або інших проблем, які можуть стати осущественним перешкодою або суттєвою загрозою в майбутньому.
Відповідь Тома Кайта
3 "вузьких" шпальти = замечтаельно для таблиці, організованої за індексом
1,000,000,000 rows = підходяща кандидатура на секціонування
Якщо початкові стовпці мають багато значень, що повторюються, то стиснення = цілком непогана ідея.
Я не знаю ні про які "нюанси" і потенційні проблеми, які не дозволяють це робити.
Чтои упевниться.
Дякую за відповідь, але я хочу упевниться, що правильно описав ситуацію, тому що це критично важливо в нашому випадку:
Я хочу використовувати організувати за індексом, Секціонірованние за діапазоном і стиснення таблицю, пріметрно таку, як створюється нижче (я десь знайшов синтаксис для стиснення) [до речі, спасибі за підказку про "початкових шпальтах з повторюваними значеннями"]
Мені на мить здалося, якщо я правильно зрозумів, що ви виправдовуєте використання секціонірованной АБО організованою за індексом таблиці, але я хочу і те, і інше, І стиск, але стурбований ймовірністю втрати даних і т.п. у довгостроковій перспективі. У нас пам'яті - вагон, так що час на стиск мене не турбує, хоча, протестувати треба. Мене більше турбує пошкодження даних і т.п. ПЛЮС якщо буде виконано TRUNCATE (як я випадково зробив, поки розбирався, як виконати усічення для секції) - доведеться мені пересоздавать ВРЮ таблицю або тільки цю організовану за індексом секцію :-)
Чи був представлений вище сценарій випробуваний серйозними компаніями успішно і без особливих проблем для адміністрування?
(Питання про 10g викинуть, оскільки Том на нього не відповів, поки. - Прим. В.К.)
Відповідь Тома Кайта
Ні, я написав, що організована за індексом таблиця, яка секціонірована і, можливо, стиснута, може дуже навіть підійти.
Я знаю про використання великих секціонованих таблиць, організованих за індексом (про стиснення я не впевнений, про це зазвичай забувають), і вони успішно працюють.