Oracle, з яких полів складається індекс. Унікальний індекс, UNIQUE INDEX

Таблиці можуть мати велику кількість рядків. А оскільки рядки не впорядковані, то пошук за вказаним значенням може вимагати значного часу. Використання індексів дозволяє прискорити процес читання потрібних записів. INDEX- це впорядкований перелік певних стовпців чи груп стовпців у таблиці. Коли створюється індекс по одному або декільком полям, сервер БД формує відповідний впорядкований список. Таблиця, звичайно ж, повинна вже бути створена і повинна містити імена стовпців, що індексуються.

Синтаксис CREATE INDEX

CREATE INDEX ON table_name ( [,]...);

Додавання індексу знижує продуктивність запитів, пов'язаних з додаванням, зміною або видаленням даних, оскільки щоразу при виконанні транзакції дані індексу також оновлюються, що вимагає виконання сервера додаткової роботи. Однак виконання запитів SELECT по полям, що індексуються, істотно переважують ці недоліки. Не слід створювати індекси кожного стовпця таблиці, не визначивши, які запити виконуватимуться.

Унікальний індекс, UNIQUE INDEX

Індекс може бути унікальним unique indexщо не дозволяє мати в таблиці дубльованих записів з однаковими значеннями індексованих полів.

ПРИМІТКА: при створенні унікального індексу транзакція буде відхилена, якщо вже є ідентичні значення в записах таблиці по полям, що індексуються. Для унікального індексу таблиці з кількома полями комбінація значень має бути єдиною, але кожне із значень поля може і не бути унікальним.

Відмінність PRIMARY KEY та UNIQUE INDEX

Обмеження "primary key" та unique indexзабезпечують унікальність значень полів таблиці, де вони визначені. За замовчуванням primary key створює кластерний індекс на стовпці, а "unique index" – некластерний. Іншою відмінністю і те, що " primary key " неспроможна мати нульових записів, тобто. поле NOT NULL, тоді як "unique index" допускає лише один нульовий запис (NULL). Таблиця може мати лише один первинний ключ, але дещо "unique index".

Видалення DROP INDEX

Видалення індексу не впливає зміст полів. Синтаксис оператора видалення індексу drop index:

DROP INDEX ;

ALTER INDEX

У різних СУБД є суттєві відмінності щодо використання оператора alter index. Так, наприклад MySQL не підтримує даний оператор, в Interbase можна використовувати даний оператор для відключення та повторного включення індексу, в результаті чого буде виконана переіндексація даних.

У СУБД PostgresSQL індекс можна перейменувати за допомогою оператора alter index. Синтаксис зміни індексу:

Перейменування індексу в СУБД PostgresSQL ALTER INDEX index_name RENAME TO index_name_new;

ALTER INDEX в Oracle

Платформа Oracle також підтримує інструкцію alter index. Цей оператор використовується для зміни або перебудови існуючого індексу без його видалення та повторного створення.

Синтаксис оператора для перейменування індексу в Oracle має такий вигляд:

Перейменування індексу на СУБД Oracle ALTER INDEX index_name RENAME TO index_name_new;

Для переіндексації даних необхідно використовувати наступний синтаксис оператора alter index :

ALTER INDEX index_name [ coalesce | [ rebuild | rebuild online]];

COALESCE

При використанні coalesceтаблиця не блокується та переіндексація виконується online. При цьому індекс розміщується в межах існуючої індексної структури - з'єднує блоки листа в межах гілок дерева. Індексні листові блоки швидко звільняються для використання та не потрібно багато дискового простору.

Однак coalesceгенерує багато записів у журналах повторного виконання (redo). При цьому даний операнд може викликати помилку ORA-01555 (coalesce визначає "роботу" Oracle з листовими блоками, визначених кількістю малих транзакцій. А багато малих транзакцій, виконаних однією сесією, можуть викликати в іншій сесії, яка виконує тривалу транзакцію, цю помилку). Крім цього, coalesce не опускає HWM індекс, тобто. місце на диску не звільняє і не може перемістити індекс до іншого табличного простору.

REBUILD

Використання rebuildдозволяє швидко переміщати індекс до іншого табличного простору. Крім цього, "rebuild" створює нове дерево і зменшує його висоту при необхідності. А також дає можливість швидко змінювати storage і tablespace параметри, без необхідності видаляти індекс. Може бути використаний зменшення витрат ресурсів - пересувається позначка HWM.

Однак rebuildпов'язані з вищими витратами - потрібно більше дискового простору, щоб розмістити старий і новий індекс у відповідному табличному просторі. Крім цього, rebuild може викликати помилку ORA-01410: Invalid ROWID.

Rebuild "offline" може використовувати існуючий індекс для створення нової версії індексу, але блокує таблицю під час виконання.

Rebuild "online" не блокує таблицю під час безпосередньої перебудови індексу і індекс доступний практично весь час при перебудові, крім часу перемикання. Однак при цьому блокується таблиця на початку та наприкінці перебудови. При цьому старий індекс не використовується для зміни індексу, але з ним працюють користувачі. Всі зміни тим часом вносяться до журнальної таблиці, потім вже будуть перенесені до нового індексу. Може знадобитися велике сортування.

Таким чином, оператор coalesceособливо ефективний, коли відсоток проблематичного простору до загального індексного простору невеликий (20% листових блоків) та фрагментований індекс несуттєво. rebuildособливо ефективний, коли відсоток проблематичного простору до загального індексного простору великий і середній рівень фрагментації в межах індексного блоку листа порівняно високий.

Кері Міллсап, Hotsos Enterprises, Ltd

[Від гл.редактора OM/RE О.Бачина : Публікація цієї статті має якусь передісторію, яку я вкрай хочу розповісти нашим читачам
У журналі Oracle Magazine (весна 1995) Кері Міллсап (Cary Millsap), Грег Шаллхамер (Craig Shallahamer) та Міша Адлер (Micah Adler) опублікували в статтю "Predicting the Utility of the Nonunique Index." [ Millsap та Al 1993] ("Коли використовувати неунікальний індекс"). Цю статтю було перекладено російською мовою та опубліковано у нашому журналі "Світ Oracle", який виходив ще у паперовому виданні. Інтернету в нашій країні ще не було (віриться насилу, але чиста правда!), тому стаття збереглася лише в архівах та пам'яті багатьох наших читачів, як чудове джерело правильного підходу до використання індексів. Усі останні роки мені хотілося заново перекласти та переопублікувати цю статтю, щоб розробники та АБД нового покоління познайомилися з правильним підходом до цієї проблеми. Але коли справа дійшла до справи, виявилося, що ніхто з доступних адресатів не зберіг англійський варіант цієї статті. Навіть у самого автора, Кері Міллсап. Коли я до нього звернувся, він порадив перекласти та опублікувати новий її варіант, у якому сумно відзначив [ 4 ] можлива неувага до початкового тексту. Я постарався його в цьому зневірити, надіслав йому scan-копію статті та обкладинки журналу... Він був нам вдячний і дозволив перекладати та публікувати статті з сайту компанії Hotsos Enterprises, чим ми, звичайно, з вдячністю ще не раз скористаємося. Дякую, Кері!]

===***===***===***===

[Від редакції OM/RE:На сайті корпорації Oracle з'явилася Oracle ACE (http://www.oracle.com/technology/community/oracle_ace/index.html) - "Алея слави", тобто галерея найбільш уславлених Oracle-авторів, серед яких заслужене місце займає автор даної статті Cary Millsap. З цієї "Алеї слави" взято публіковану тут фотографію автора статті.]

Резюме

Коли слід використовувати індекс? Більше десяти років розробники програм Oracle використовували просте rule of thumb (правило великого пальця) - емпіричне правило для наближених розрахунків, щоб вирішити, чи використовувати неунікальний індекс (non-unique index). Проте, у повсякденній роботі ми рідко стикаємося з проблемами продуктивності, викликаними використанням цього емпіричного правила. У цій статті я викладаю такі результати наших досліджень:

Правило великого пальця є ненадійним, якщо можна отримати відсотковий баланс селективності рядків, щоб визначити, чи дійсно потрібно створювати індекс.
Індекс може суттєво покращувати ефективність запитів до таблиці навіть з одним рядком (a one-row table).
Домінуючим фактором вашого рішення, чи слід створити індекс, має бути селективність блоків , а не селективність рядків .
Ви можете визначити селективність блоків, задаючи фразу where , використовуючи SQL запит, наведений у цій статті.
Значення шпальт зазвичай кластеризовані (згруповані) або природним чином (naturally clustered), або уніфіковано (naturally uniform), тобто однаково. Ви можете використовувати ці відомості, щоб створити більш правильне рішення, чи дійсно потрібно створювати індекс.
Багато нових можливостей Oracle спрощують здатність зберігати дані у фізичному порядку, що забезпечує чудову продуктивність.

Коли використовувати індекс: Традиційна Рада

В одному або іншому вигляді, але стандартна рекомендація, чи потрібно використовувати індекс, принаймні, починаючи з версії Oracle 5, звучала наступним чином:

Використовуйте індекс, коли запит повертає менш ніж x% рядків таблиці.

Рисунок 1 ілюструє поняття, коли якийсь поріг у x% діє як точка балансу продуктивності Oracle у порівнянні діапазонного сканування індексу та повного сканування таблиці, що здійснюється шляхом доступу. Цей графік пов'язує час відповіді R (зазвичай виражається в секундах) у пропорції до Pr рядків таблиці, які повертаються за цю операцію запиту.

Малюнок 1.Час відповіді R у секундах як відсоткова функція Pr повертаються рядків таблиці. Пунктирна лінія за R = 6.75 (червона лінія, якщо ви бачите це в кольорі) є часом відповіді при повному перегляді таблиці. Безперервна (синя) лінія – час відповіді діапазонного сканування індексу, який повертає Pr відсотків рядків цієї таблиці.

Час відповіді при плані виконання, що повертає r рядків при повному перегляді таблиці є приблизно постійним, незалежно від того, r - це один рядок або загальна кількість рядків у таблиці. Однак час відповіді діапазонного сканування індексу збільшується в міру того, як наростає обсяг результуючих вихідних рядків . Відсоток pr = x- порогове значення pr, коли час відповіді повного перегляду таблиці та діапазонного сканування індексу порівнюються. При значенні pr< x діапазон сканування індексу має кращу продуктивність. При значенні pr > xнайкращу продуктивність надає повний перегляд таблиці.

Тим не менш, у цій лінії міркування є велика проблема. Будь-яке правило типу великого пальця щодо індексів ненадійне, якщо існує балансовий відсоток типу x .

Чому правило великого пальця ненадійне

Правило великого пальця звучить приблизно так: Використовуйте індекс, коли запит повертає менше ніж x відсотків від загальної кількості рядків таблиці ". Воно засноване на наступних позиціях:

Якщо операція запиту, що охоплює все джерело рядків, виводить лише один рядок, то діапазонне сканування індексу ефективніше, ніж повний перегляд таблиці.
Якщо операція запиту, що охоплює все джерело рядків, у результаті виводить усі рядки таблиці, повний перегляд таблиці ефективніший, ніж діапазонне сканування індексу.
Тому має існувати певний балансовий поріг від повного числа рядків у таблиці, при якому вартість отримання вихідних рядків за допомогою діапазонного сканування індексу еквівалентна отриманню вихідних рядків за допомогою повного перегляду таблиці. Для запиту, що повертає менше рядків, ніж граничне значення, діапазонне сканування індексу ефективніше. Для запитів, що повертають більше рядків, ніж граничне значення, ефективніший повний перегляд таблиці.

Наші випробування та практичний досвід показали, що позиція 1) є дійсною навіть для дуже маленьких таблиць. Запит, що повертає один рядок, більш ефективний, коли виконується з використанням індексу, ніж за допомогою повного перегляду таблиці, навіть якщо таблиця містить лише один рядок. Багато людей, з якими ми обговорили це, здивували таким результатом. Цей результат також суперечить цілком конкретній рекомендації Oracle: "малі таблиці не вимагають індексів" [ Oracle 2001a]. Малі таблиці можуть не вимагати наявності індексів, але індекси на малих таблицях можуть зробити вашу систему значно ефективнішою і, отже, значно масштабованішою [ 2 ].

Отже, ми приймаємо позицію 1), але позиції 2) починаються великі проблеми. Іноді набагато дешевше прочитати 100% рядків таблиці, використовуючи індекс, аніж за повного сканування таблиці.

Приклад: Представимо таблицю з ім'ям interface, яка займає (high-water mark – найвища позначка використання простору) 10,000 блоків. Хоча у своєму історичному минулому таблиця interface містила сотні тисяч рядків, сьогодні таблиця включає лише 100 рядків. Ці рядки довільно розпорошені по 30 блоків таблиці. Припустимо, що таблиця має первинний ключ на стовпці під назвою id, у якому, звісно, побудований індекс (з ім'ям id_u1). І далі нам треба виконати наступний запит:

Select id, date, status from interface i ;

Якщо цей запит виконувати за допомогою повного перегляду таблиці, потрібно 10,000 LIO-дзвінків Oracle. Ми можемо трохи переробити цей запит, щоб дозволити Oracle виконувати його, використовуючи індекс. Якщо id - числовий стовпець і всі значення id - невід'ємні цілі числа, наступний запит виводить бажаний набір рядків за допомогою індексу:

Select /*+ index(i id_u1) */ id, date, status from interface i where id> -1 ;

Цей запит потребує менше 40 LIO-дзвінків Oracle. Час відповіді становитиме приблизно 10,000/40, тобто у 250 разів краще за використання індексу, ніж за вибірці 100 % рядків з таблиці у вигляді її перегляду.

Існує багато різних гачків та загогулін (all sorts of hooks and crooks), які можна досліджувати на цьому прикладі. Наприклад, якби фраза select містила лише idабо count(id)(що може бути отримано з інформації індексу навіть без звернення до сегменту даних), перегляд за індексом був би ще швидше.

Отже, щоб бути застосовним у випадках подібних до цього, емпіричне правило (великого пальця) для будь-якого відсотка проіндексованих рядків повинно допускати можливість, що використання індексу може бути більш ефективним, ніж повний перегляд таблиці навіть для тих запитів, які повертають усі 100 % рядків таблиці. На малюнку 2 показано цей феномен.

Малюнок 2. Ця схема відбиває ситуацію, коли таблиця містить багато порожніх блоків. Діапазонне сканування індексу (синя суцільна лінія) швидше, ніж повний перегляд таблиці (червона пунктирна лінія) навіть для запиту, що повертає 100% рядків таблиці.

Існує багато випадків, коли засновані на відсотках емпіричні правила є ненадійними. Існує також велика проблема, пов'язана з висловленим раніше постулатом 3). Ця проблема ще покаже себе під час подальшого викладу.

Нерівномірно еволюціонуючий ознака x

Згадана велика проблема емпіричного правила індексації полягає в тому, що немає чіткої ясності, яке значення xмає використовувати. Якщо простежити історію рекомендацій для x у документації Oracle, то ви знайдете таке: 3 ]

Становище навіть гірше, ніж показано у таблиці. Якщо пам'ять мені не змінює, ранній випуск виробничої документації Oracle7 містив рекомендацію для xяк "1-15 відсотків". Я був вражений тим, наскільки широкий діапазон. Якщо ж заглибитися в це питання, деякі мої друзі з Oracle Applications development дуже переконливо говорили, що у своїх додатках вони часто спостерігали значення xпонад 40.

Багато людей вважають, що причина, через яку гойдається (wiggling) x,полягає в тому, що Oracle продовжує удосконалювати роботу оптимізатора (optimizer). Але це не загальна справжня причина. Причина ж того, що значення xстав таким об'єктом, що рухається (moving target), в тому, що автори рекомендацій не зуміли виявити справжні параметри, які дають збалансоване значення.

Критичний параметр - це число блоків Oracle нижче за вищу точку заповнення (high-water mark) таблиці, яку можна ігнорувати при використанні індексу . Шлях побудови правила створення індексу, яке перевершить емпіричне правило великого пальця і яке зробить життя легшим, має включати питання: "Який план виконання вимагатиме менше блоків Oracle, які мають бути переглянуті?"

Для будь-якого джерела рядків, з більш ніж одним рядком, індекс у багато разів дозволяє скоротити PIO-дзвінки. Число PIO-дзвінків для блоків даних, яке ігнорується при задіянні індексу, залежить від наступного:

Скільки блоків нижче high-water mark таблиці містять принаймні один рядок, який задовольняв би фразі where вашого запиту? Якщо рядки, що "цікавлять" вас, розподілені однорідно по всій таблиці, то можна з'ясувати, коли використання індексу неефективно навіть при неймовірно "хороших" значеннях селективності рядків.

Приклад: Ми бажаємо оптимізувати наступний запит:

select id, date from shipment where flag="x"

Завантажена таблиця shipmentмістить 1,000,000 рядків, які зберігаються в 10,000 блоках Oracle. Лише 10,000 рядків відповідають критерію flag = x. Тому селективність рядків на стовпці flag зі значенням x дуже "хороша" - 1%. Однак, фізичний розподіл рядків у shipmentтака, що кожен окремий блок таблиці містить рівно один рядок, для якого flag="x" . Отже, ми використовуємо індекс на стовпці flag чи ні, щоб задовольнити цей запит, ми повинні переглянути всі блоки таблиці. Тому повний перегляд таблиці буде ефективнішим, ніж діапазонне сканування індексу навіть при тому, що запит повертає лише 1% рядків з таблиці.
Чи може Oracle виконувати вимоги фрази select запиту, використовуючи лише дані, що зберігаються в індексі? Якщо так, то індекс може взагалі усунути потребу звернення до таблиці. Стовпці в індексі – це зазвичай невеликий піднабір стовпців індексованої таблиці. Отже, число листових блоків в індексі зазвичай набагато менше, ніж число блоків нижче за high-water mark у відповідній таблиці. Тому сканування навіть всього індексу може бути дешевшим, ніж перегляд діапазону блоків у таблиці.

Притча про індексаторів

Давайте розкриємо важливість концепції, яка називається селективністю блоківза допомогою історії. Йтиметься про …

Уявімо собі книгу з назвою Brief History of Humanity (Коротка Історія Людства), резюме на 1,000 сторінках фактично про все, що наша порода зробила, відколи ми здобули здатність все це висловлювати словами. Припустимо, що з цієї великої книги ви зацікавилися відомостями про Олександра Великого. Як ви шукатимете їх? Звісно, через індекс книги.
Індекс точно повідомить вам, на яких сторінках знаходиться інформація про Олександра Великого. Ви, напевно, позначите індекс, а потім підете пошуком прямого доступу за номером сторінки з "Олександр". Коли ви обробите одну секцію, то повернетеся назад до зазначеної сторінки індексу, щоб дізнатися, куди потрібно звернутися, щоб знайти подальші відомості. Нарешті, ви зробите ще один захід в індекс, щоб переконатися, що вичерпано список номерів сторінок, які містять інформацію, що вас цікавить.
Тепер уявіть собі, що на відміну від звичайних книг кожне окреме слово цієї книги знаходиться в індексі. В індексі такої книги ви зможете знайти розташування навіть таких слів, як "the" ("<определенный артикль>"). Тепер давайте скажемо, що в Brief History of Humanityнас цікавить повний список слів, які йдуть за словом "the". Запитуючи слова, які слідують засловом "the", за індексом ми зможемо знайти все, що шукаємо; для цього ми маємо звернутися до фактичного тексту.

Екстраординарна частота слова "the", ймовірно, зробить цю роботу абсолютно нездійсненною навіть за наявності індексу. "Давайте подивимося, де є "the"… Ах так, "the" є на першій сторінці." Добре, що ви відзначили першу "the" сторінку в індексі. Тоді натисніть на індекс для першої сторінки. Ви розташуєте слово після першого "the". Потім ви повертаєтеся до індексу, щоб знайти наступну сторінку, на якій з'являється "the" - це також сторінка одна. Ви будете ходити вперед і назад, поки ви не відвідаєте кожну сторінку всієї множини книжкових часів. Ви будете клацати по книзі назад і вперед стільки багато разів, що її палітурка, ймовірно, повністю зноситься.

Тепер уявімо, що існує Reader"s Digest Large Print for Easier Reading (Довідник Читача для Великого Видання для легшого читання) цієї Brief History of Humanity(Короткою Історії Людства). Далі уявімо, що основна книга надрукована літерами по 72 пункти. Тому Brief History of Humanityмістить лише по 20-30 слів на сторінці. І хоча слово "the" є досить загальним і фактично з'являється на кожній сторінці звичайної книги, воно вже не досить звичайно, щоб з'являтися на кожній сторінці довідника Large Print. У цих нових умовах індекс має дуже велику корисність для нашого невеликого проекту "find the word after the "the"" ("знайти слово після "the""), тому що тепер індекс дозволяє нам пропускати більшу кількість сторінок.

Це – 72-пунктовий шрифт. Довідник Large Print for Easier Readingдля книги Brief History of Humanityмістить набагато менше посилань, ніж до кожної сторінки стандартного розміру.

Розгадка міфу

Параметри, які впливають на корисність індексу при діапазонному скануванні, при якому потрібний доступ до таблиці, наступні:

Розуміння параметрів корисності індексу руйнує міф, чому люди не можуть зробити добрий вибір значення x.

Коли автори документації Oracle писали посібник з налаштування Oracle6 (Oracle version 6 tuning guide), вони, ймовірно, використовували таблиці типу dept у схемі scott/tigerу базі даних Oracle із блоками 2КБ. Коли створювалася документація по Oracle7, вони, ймовірно, протестували ті самі запити, що й раніше. Але, ймовірно, використовувався "new" ("новий") 4КБ розмір блоку Oracle, який увійшов у моду з Oracle7. Так як великі блоки зберігали більше рядків, ніж раніше, спостерігається значення xбуло нижче. Індекси, очевидно, виявилися менш корисними, ніж у Oracle6. Виявлений поріг знизився з 10-15 до 2-4%.
Документація по Oracle8 iта Oracle9 iнабагато краще розкриває тему корисності індексів. Тепер, як загальне становище, Oracle використовує x = 15, Але згадується, що значення " varia greatly " ( " істотно варіює " ). Кластеризація (clustering) і швидкість повного сканування (full-scan) згадуються як параметри, що змінюються, але не згадуються ні розмір блоку, ні розмір рядка як параметри кластеризації [ Oracle 2001a].
Ви не забули наших хороших друзів з Oracle Applications development, які оголошували хорошими результатами при x> 40? Чому вони були переконані у значенні, так драматично відмінному від усього, що говорила офіційна документація Oracle? Не важко зрозуміти їхню точку зору, якщо ви подумаєте про середовище, в якому вони знаходяться. По-перше, їх таблиці мають величезні (huge) рядки. Таблиці багатьох програм включають більше 200 стовпців у рядку. По-друге, з різних причин Oracle Applications є "little slow" ("трохи повільними") у плані сприйняття нових технологій, пропонованих ядром (kernel). Із середини 1990-х вони майже виключно використовували 2KB-блок бази даних. Зрозуміло, зміна розміру блоку у великих базах даних Oracle Applications - це величезна робота, не кажучи вже про, мабуть, непереборну роботу з перевірки правильності планів виконання SQL-пропозицій. Коли так траплялося, комбінація великих рядків і маленьких блоків призвела до спостерігається більш високого значення порога x,ніж по спостереженням багатьох інших груп.

Що ж тепер?

Моя вам порада:

Забудьте все про правила індексації типу великого пальця, що ґрунтуються на відсотках.

Насправді немає ніякого діапазону відсотків, який дасть вам надійний результат. Є запити, що повертають 1% або менше рядків таблиці, які більш ефективно виконуються повним переглядом таблиці, ніж з використанням індексу. І існують запити, що повертають усі 100% рядків таблиці, які ефективніше виконуються за допомогою індексу. Але якщо ви наполягаєте на виборі значення для xя рекомендую знайти таке значення, яке одночасно є менше 1% і більше або дорівнює 100%. Оскільки жодної такої кількості немає, я рекомендую, щоб ви повністю переключили свою увагу в інший бік від правил індексації типу великого пальця, заснованих на відсотках.

Технологія оптимізації Oracle пройшла довгий шлях, починаючи з впровадження заснованого на вартості (cost-based) оптимізатора Oracle (це було дуже добре в Oracle8 i). Все, що потрібно від Вас - це визначити, які створити індекси. Ядро Oracle лише тоді використовуватиме створені вами індекси, коли це ефективно. Але створення індексу, який ніколине буде добре використовуватися, - тільки витрата і місця, і часу. Тож ви маєте самі вирішувати, створювати індекс чи ні? Відповідь у селективності блоків.

Селективність блоків

Ви, мабуть, вже знайомі з концепцією селективності рядків. Ви можете визначити селективність рядків даного предикату з фрази where, як число рядків, повернутих предикатом (r), розділене на загальну кількість рядків у таблиці (R):

- Визначення селективності рядків (row selectivity)

Селективність блоків можна визначати, аналогічно задаючи у фразі where предикат відношення числа блоків даних, що містять принаймні один рядок, що відповідає умові предикату (b), загальною кількістю блоків даних нижче high-water mark (B):

Визначення селективності блоків (block selectivity)

Відмінність між селективністю рядків та селективністю блоків дуже суттєво, тому що селективність блоків майже завжди гірша - часто багато гірша - селективність рядків. Раніше на прикладі таблиці shipmentми бачили flag = "x". Для цього предикату виходить селективність 1% рядків, а селективність блоків 100%.

Ви можете обчислити селективність рядків та селективність блоків, використовуючи SQL-скрипт з наступного прикладу, який ми назвали hds.sql [ Holt 2002].

1 rem $Header: /usr/local/hotsos/RCS/hds.sql,v 1.8 2002/01/07 18:12:27 hotsos Exp $ 2 rem Copyright (c) 2000-2002 by Hotsos Enterprises, Ltd. Всі права захищені. 3 rem Author: [email protected] 4 rem Notes: Hotsos data selectivity за допомогою повного table scan for row count. 5 6 define v_substr7 = "substr(rowid,15,4)//substr(rowid,1,8)" 7 define v_substr8 = "substr(rowid,7,9)" 8 define v_over = "substr(""&_O_RELEASE" ",1,1)" 9 10 col dummy new_value v_substr 11 12 set termout off heading on pause off 13 14 select decode(&v_over, "7", "&v_substr7", "&v_substr8") dummy 15 from dual 16 17 set termout on verify off feedback off pages 10 18 19 accept p_town prompt "TableOwner: " 20 accept p_tname prompt "TableName: " 21 accept p_clst prompt "ColumnList: " 22 accept p_where prompt " ze : " 24 25 variable fblks number 26 27 declare 28 tblks number; 29 tbytes number; 30 ublks number; 31 ubytes number; 32 luefid number; 33 luebid number; 34 lublk number; 35 begin 36 sys.dbms_space.unused_space(37 upper("&p_town"), upper("&p_tname"), "TABLE", 38 tblks, tbytes, ublks, ubytes, luefid, luebid, lublk, null 3 40: fblks: = tblks - ublks; 41 end; 42 / 43 44 col blks form 9,999,999,999 heading "Table blocks below hwm/(B)" just c 45 col nrows form 999,999,999,999 heading "Table rows/(R)" just c new_value v_ nrows 48 від &p_town..&p_tname; 49 50 col bs form a17 heading "Block selectivity/(pb = b/B)" just c 51 col nblks form 9,999,999,999 heading "Block count/(b)" just c 52 col rs form a17 heading "Row selectivity/(pr = r/R)" just c 53 col nrows form 999,999,999,999 heading "Row count/(r)" just c 54 55 set pause on pause More: " pages &p_pgs 56 57 select &p_clst /:fblks*100,"990.00")//"%",17) as bs, 59 count(distinct &v_substr) nblks, 60 lpad(to_char(count(*)/&v_nrows*100,"990.00")//" %",17) rs, 61 count(*) nrows 62 від &p_town..&p_tname &p_where 63 group by &p_clst 64 order by bs desc;

Використання скрипта hds.sql є очевидним. Однак отримання повної інформації про розподіл даних у таблиці може бути дуже дорогим. Залежно від ваших даних цей запит може виконуватися і хвилини, і годинник. Це пояснює, чому вартісний оптимізатор Oracle покладається на статистику, що зберігається, замість самостійного аналізу даних, коли обчислює або затверджує план виконання. Наступний приклад ілюструє те, як ми використовуємо дані hds.sql.

Приклад: система має таблицю з ім'ям po.cs_ec_po_items . Наша мета полягає в тому, щоб оптимізувати кілька підоперацій запиту, які у фразі where використовують предикат ec_po_id =:vas . Що вийде, якщо ми створимо індекс на стовпці ec_po_id ? Ми можемо використовувати скрипт hds.sql, щоб отримати справжню інформацію про розподіл даних за різними значеннями ec_po_id :

Вихідні дані скрипта hds.sql відсортовані за зменшенням селективності блоків. Лістинг зазвичай містить тисячі рядків, але всі найгірші дані (worst-case data) - в даному випадку представляють найбільш цікаву частину - знаходяться нагорі. Тому ми зазвичай обриваємо листинг hds.sql після видачі однієї-двох сторінок.

Зауважимо, що для цієї таблиці має місце чудова селективність рядків для кожного значення ec_po_id . "Найгірше" значення селективності рядків - лише 0.54%. Це означає, що тільки піввідсотка рядків таблиці має значення ec_po_id = "8" . Однак стовпець селективності блоків представляє нам зовсім іншу історію. Селективність блоків ec_po_id = "8" складає 63.50%. Це означає, що майже дві третини блоків таблиці міститься принаймні по одному рядку, для якого ec_po_id = "8" .

Чи маємо ми створити індекс на ec_po_id ? Можна витратити півдня або більше, обчислюючи "back of the envelope" ("швидко і легко визначається") відповідь, намагаючись обчислити формули витрати плану виконання. Але оптимізатор Oracle може зробити це за вас. Найбільш точний і, зрештою, найменш забирає час метод визначення відповіді у тому, щоб виконати тестування на фактичної базі даних Oracle. Найкращий спосіб визначити відносні витрати двох планів виконання полягає в тому, щоб виконати їх на деяких тестових даних із встановленням опції sql_trace=true . Якщо потрібна більша детальність частини, скажімо, використання інших (не-CPU) механізмів, яких задіяє Oracle протягом виконання запиту, то протрасуйте виконання з використанням Oracle-події 10046 на рівні 8 [ Hotsos 2002]. Якщо потрібна більша кількість даних про те, чому оптимізатор вибрав такий план, який сам і зробив, то протрасуйте виконання з Oracle-подією з випадком 10053 [ Lewis 2001].

З листингу hds.sql ми дізнаємося про граничні умови, які потрібно перевірити. Наприклад, ми тепер знаємо, що при тестуванні слід відповісти на такі запити:

Чи виконається запит select foo from cs_ec_po_item where ec_po_id="8" швидше з індексом на ec_po_id ?
Чи виконається скільки-небудь швидше запит з індексом для ec_po_id = "45" ?
Чи виконається скільки-небудь швидше запит на ec_po_id які мають селективність блоків менше ніж 1%? (Оскільки звіт сортується у низхідному порядку селективності блоків, значення з кращою селективності блоків у ньому не показуються.)

Ваше остаточне рішення про побудову індексу звичайно залежить від того, чи перевищує вигода від наявності індексу вартість його наявності. Ці витрати можуть включати:

Випадкова деградація планів виконання інших запитів. У додатках, які досі використовують синтаксичний оптимізатор Oracle, це становить очевидний ризик. Створення індексу для оптимізації пропозиції Аможе випадково деградувати продуктивність деякої іншої пропозиції B. На щастя, у вартісній оптимізації, особливо для гістогамів (histograms) це явище стає дедалі рідкісним.
Збільшення часу DML-відповіді для конкретної таблиці. Однак я бачив, як люди драматично переоцінюють важливість цього чинника. Не гадайте про це; спрофілюйте трасові дані ваших DML-операцій, щоб з'ясувати їхню справжню вартість.
Збільшення обсягу простору розміщення індексу. Колись кількість місця, необхідного для індексу, була матеріально важливим чинником щодо, чи будувати індекс. З сьогоднішніми цінами на диски це майже не стосується.

Коли використовується інструмент подібно до скрипту hds.sql, спостерігається один з трьох варіантів:

Селективність блоків кожного значення настільки хороша, що ви напевно хочете створити індекс для стовпця.
Селективність блоків кожного значення настільки низька, що ви не хочете створити індекс для стовпця.
p align="justify"> Селективність блоків низька для деяких значень, але хороша для інших. У цьому випадку необхідно вирішити, чи достатня корисність індексу в хороших випадках, щоб компенсувати вартість його наявності.

Рішення у випадках 1 та 2 очевидні. А ситуація 3, ймовірно, саме та, в якій ви перебуваєте найчастіше. Користувачі вартісного оптимізатора Oracle до релізу 7.3 стояли перед жорстким вибором. Якщо індекс не створювався, то був великий ризик низької продуктивності за певних значень у фразі where; якщо індекс створювався, то був ризик низької продуктивності для інших значень. Нові версії вартісного оптимізатора Oracle роблять життя набагато простішим. Якщо в наші дні ви регулярно виконуєте свої обов'язки зі збору статистики, подібна ситуація набагато менш ймовірна, і помилкове створення мало придатного індексу завдасть екстремальних витрат (torture - тортур) вашим користувачам.

Приклад: Уявімо, що секційована таблиця містить стовпець id з наступним розподілом даних:

Показаний тут розподіл даних сильно перекошений (highly skewed). Тепер видамо наступний запит до цієї таблиці:

select name from division d where id=:a1

Без гістограм вартісний оптимізатор може припустити, що є десять різних значень id, кожен id відповідає приблизно за 1/10 рядків таблиці. Це припущення змусить його згадати хорошу ідею використовувати індекс на стовпчику id. І так було б доти, доки :a1 != "01" .

Сила гістограмної (histogram-based) оптимізації полягає в тому, що належним чином реалізований [ 9 ] гістограмний оптимізатор помітить, коли: a1 = "01" і не намагатиметься використовувати індекс на id. Без гістограмної оптимізації розробник додатків повинен або

оптимізувати запит так, щоб було ефективно, якщо:a1 = "01", але вкрай неефективно інакше [ 10 ]; або
ви повинні написати процедурну логіку, яка використовує одну SQL-пропозицію для звичайних значень та іншу SQL-пропозицію для рідкісних значень. Oracle General Ledger генерує динамічні SQL-пропозиції, використовуючи метод 2 для функцій Financial Statement Generator. Це розумно, але водночас і безладдя (a mess).

Значення не часто розподіляються довільним чином

Недавня документація по Oracle наводить припущення, що "рядки в таблиці впорядковані довільно (randomly ordered) щодо стовпця, на якому базується запит". Це припущення трохи спрощує написання Oracle-документації, але це робить цю пораду Oracle менш корисною, ніж вона могла б бути.

В результаті дій з hds.sql можна побачити, що іноді значення стовпця природно групуються, і залишитися згрупованими назавжди.

Приклад: таблиця shipment має стовпець стану, званий shipped, який приймає значення "y" тоді і тільки тоді, коли пункт замовлення був відвантажений (shipped). Оскільки замовлення мають тенденцію відправлятися, грубо кажучи, в тій самій послідовності, що були введені, таблиця shipment через якийсь час має хорошу природну кластеризацію за значеннями shipped="n" , як це показано на малюнку 3. Кластеризація рядків з shipped="n" покращує корисність індексу при пошуку рядків з shipped="n" .

Малюнок 3. Значення шпальти стану мають тенденцію до природної кластеризації.

Протилежністю згрупованого розподілу є однорідний розподіл. Якщо значення стовпця має справжнє однорідне розподіл у межах таблиці, то екземпляри цього значення фізично рівновіддалені друг від друга.

Приклад: таблиця address має стовпець з ім'ям state що містить два літерний код штату або провінції. У додатку, який використовує цю таблицю, немає жодних очевидних відносин між часом, коли було вставлено рядок клієнта, та значенням state клієнта. Отже, фізичний розподіл кожного значення стану практично однорідний. Хоча state = "TX" істинно, можливо, тільки для одного рядка з 30, лише деякі блоки таблиці не мають жодного рядка з state = "TX" . Малюнок 4 показує цю ситуацію.

[Блок містить принаймні один рядок з state = "TX"
Блок не містить жодного рядка, для якого state = "TX" ]

Рисунок 4. Індекс на state має низьку корисність для state = "TX".

Використання тут індексу з state , ймовірно, було б неефективно для пошуку будь-якого "відомого" ("popular") коду штату. Але якщо, наприклад, є один або більше штатів з набагато меншою кількістю рядків, ніж наявні блоки в таблиці address , і якщо Ви часто шукаєте коди таких штатів і використовуєте гістограми, тоді створення індексу state , Імовірно, допоможе вашому додатку.

Стовпці Status іноді можуть самостійно групуватися природним чином. Але за відсутності будь-якого штучного зовнішнього впливу стовпці type здебільшого мають тенденцію до однорідного фізичного розподілу. Існує кілька типів на фізичне зберігання даних у таблиці. Можна наказати певну фізичну впорядкованість даним, використовуючи:

Секціонування ( partitioning)таблиць та індексів Oracle
Індекс-організовані таблиці Oracle
Періодичні операційні процедури обслуговування для видалення рядків і потім повторної їх вставки у кращому фізичному порядку
Використання кластерних ( cluster) сегментів Oracle, замість табличних сегментів

Без потреби не припускайте, що розподіл ваших даних є випадковим (random). З'ясуйте це за допомогою hds.sql. Будь-які прийоми, що наказують фізичну впорядкованість, принесуть як вигоди, так і витрати вашого бізнесу. Якщо зміна фізичного розподілу даних одночасно допомагає максимізувати чистий прибуток вашої компанії, потік готівки (cash flow) та повернення інвестицій, тоді робіть це [ Goldratt 1992].

Висновок

Багато джерел вчать, що рішення щодо індексації треба приймати на базі аналізу предикату селективності рядків у фразі where . Ще гірше, коли деякі джерела обговорюють застосування індексації термінів селективності рядків для всього стовпця, що повністю ігнорує можливість його асиметричності. Однак селективність рядків – ненадійна підстава для рішення про створення індексу. Найкращий спосіб пом'якшувати ризик полягає в тому, щоб перевірити фактичну продуктивність SQL-пропозиції на перевірених тестових даних. Інструмент, подібний до скрипту hds.sql, який видає інформацію про селективності блоків , підвищує надійність та ефективність вашого випробування, розкриваючи критичні значення стовпця, на якому ви збираєтеся перевірити продуктивність.

Вартісний (cost-based) оптимізатор Oracle робить більш простою відповідь на питання, чи треба будувати індекс, оскільки він виробляє більш просунуті рішення щодо використання індексів, ніж це може зробити синтаксичний (rule-based) оптимізатор. Але для реалізацій, які все ще покладаються на синтаксичний оптимізатор Oracle, розуміння важливості селективності блоків може бути життєво важливим для продуктивності програм Oracle. Як тільки визначаться характеристики селективності блоків, необхідно унеможливити пасивний підхід до фізичної впорядкованості ваших даних. Багато можливостей, привнесених до СУБД Oracle, починаючи з випуску 7.3, спрощують ваші дії щодо зберігання даних у фізичній упорядкованості, що забезпечує чудову продуктивність.

Примітки:

У Oracle є кілька типів індексів:

· Деревоподібні індекси (В-дерева).

· хешовані індекси ( hash).

· Індекси на основі бітових карт або бітові індекси ( bitmap).

В-дерева були реалізовані в Oracle практично з самого початку її існування, потім з'явилися хешовані індекси, а потім - бітові карти.

Розуміння того, коли і де слід використовувати конкретні типи індексів, є дуже важливим для ефективного їх застосування. В-дерева використовуються найчастіше, у той час як хешовані та бітові індекси лише за наявності деяких умов можуть забезпечити суттєві переваги у виконанні певних запитів.

Оператор створення індексу використовує наступний синтаксис:

СREATE INDEX ім'я_індексу

ON ім'я_таблиці (ім'я_стовпця, [¼])

Для видалення індексу використовується команда

DROP INDEX<ИМЯ>(видалити)

Можна перебудувати існуючий індекс без його видалення та повторного створення за допомогою команди:

ALTER INDEX<ИМЯ>REBUILD(перебудувати індекс)

ALTER INDEX<ИМЯ>UNUSABLE(Вимкнути індекс на час,

щоб знову включити назад за допомогою REBUILD)

B-дерева

Мабуть, найпопулярнішим підходом до організації індексів у базах даних є використання техніки B-дерев. B-дерево містить один індексний елемент для кожного рядка таблиці, в якій є непусте (NOT NULL) індексне значення. З погляду зовнішнього логічного уявлення B-дерево – це збалансоване сильно гіллясте дерево у зовнішній пам'яті (рис.5.3).

Мал. 5.3 - Деревоподібний індекс по текстовому стовпцю

З погляду фізичної організації B-дерево представляється як мультисписочная структура сторінок зовнішньої пам'яті, тобто. кожному вузлу дерева відповідає блок зовнішньої пам'яті (сторінка). Внутрішні та листові сторінки зазвичай мають різну структуру.

У типовому випадку структура внутрішньої сторінки виглядає так:

При цьому витримуються такі властивості:

ключ(1)<= ключ(2) <= ... <= ключ(n);

у сторінці дерева Nm знаходяться ключі k зі значеннями ключ(m)<= k <= ключ(m+1).

Листова сторінка зазвичай містить значення індексу та ідентифікатори рядків (ROWID) і має таку структуру:

Листова сторінка має такі властивості:

· Ключ (1)< ключ(2) < ... < ключ(t);

· сп (r) - упорядкований список ідентифікаторів кортежів (tid), що включають значення ключ (r);

· Листові сторінки пов'язані одно-або двонаправленим списком.

Пошук у B-дереві - це проходження від кореня до листа відповідно до заданого значення ключа. Зауважимо, що оскільки дерева сильно гіллясті і збалансовані, то для пошуку за будь-яким значенням ключа потрібно одне і те ж (і зазвичай невелике) кількість обмінів із зовнішньою пам'яттю. Точніше, в збалансованому дереві, де довжини всіх шляхів від кореня до листа одні й самі, якщо у внутрішній сторінці міститься n ключів, то при зберіганні m записів потрібно дерево глибиною log n (m). Якщо n досить велике (звичайний випадок), то глибина дерева невелика, і робиться швидкий пошук.

Основною "родзинкою" B-дерев є автоматична підтримка якості збалансованості. Розглянемо, як це робиться при виконанні операцій занесення та видалення записів.

При занесенні нового запису виконується:

· Пошук листової сторінки. Фактично проводиться звичайний пошук по ключу. Якщо в B-дереві не міститься ключ із заданим значенням, то буде отримано номер сторінки, в якій йому слід утримуватися, та відповідні координати всередині сторінки.

· Приміщення запису на місце. Звичайно, вся робота проводиться в буферах оперативної пам'яті. Листова сторінка, в яку потрібно занести запис, зчитується до буфера, і в ньому виконується операція вставки. Розмір буфера повинен перевищувати розмір сторінки зовнішньої пам'яті.

· Якщо після виконання вставки нового запису розмір використовуваної частини буфера не перевищує розміру сторінки, то на цьому виконання операції запису закінчується. Буфер може бути негайно виштовхнутий у зовнішню пам'ять, або тимчасово збережений в оперативній пам'яті залежно від політики управління буферами.

· Якщо виникло переповнення буфера (тобто. розмір його використовуваної частини перевищує розмір сторінки), то виконується розщеплення сторінки. Для цього запитується нова сторінка зовнішньої пам'яті, частина буфера, що використовується, розбивається, грубо кажучи, навпіл (так, щоб друга половина також починалася з ключа), і друга половина записується в знову виділену сторінку, а в старій сторінці модифікується значення розміру вільної пам'яті. Звичайно, модифікуються посилання за списком листових сторінок.

· Щоб забезпечити доступ від кореня дерева до заново заведеної сторінки, необхідно відповідним чином модифікувати внутрішню сторінку, що є предком листової сторінки, що раніше існувала, тобто. вставити до неї відповідне значення ключа та посилання на нову сторінку. При виконанні цієї дії може знову відбутися переповнення тепер внутрішньої сторінки, і вона буде розщеплена на дві. В результаті потрібно вставити значення ключа і посилання на нову сторінку у внутрішню сторінку-предка вище ієрархії і т.д.

· Граничним випадком є переповнення кореневої сторінки B-дерева. І тут вона теж розщеплюється на дві, і заводиться нова коренева сторінка дерева, тобто. його глибина зростає на одиницю.

При видаленні запису виконуються такі дії:

· Пошук запису за ключем. Якщо запис не знайдено, видаляти нічого не потрібно.

· Реальне видалення запису в буфері, до якого прочитана відповідна листова сторінка.

· Якщо після виконання цієї підоперації розмір зайнятої в буфері області виявляється таким, що його сума з розміром зайнятої області в листових сторінках, що є лівим або правим братом даної сторінки, більша за розмір сторінки, операція завершується.

· Інакше виробляється злиття з правим чи лівим братом, тобто. у буфері виробляється новий образ сторінки, що містить загальну інформацію з цієї сторінки та її лівого чи правого брата. Листова сторінка, що стала непотрібною, заноситься до списку вільних сторінок. Відповідним чином коригується список листових сторінок.

· Щоб усунути можливість доступу від кореня до звільненої сторінки, потрібно видалити відповідне значення ключа та посилання на звільнену сторінку із внутрішньої сторінки – її предка. При цьому може виникнути потреба у злитті цієї сторінки з її лівим чи правими братами тощо.

· Граничним випадком є спустошення кореневої сторінки дерева, яке можливе після злиття останніх двох нащадків кореня. І тут коренева сторінка звільняється, а глибина дерева зменшується на одиницю.

Як видно, при виконанні операцій вставки та видалення властивість збалансованості B-дерева зберігається, а зовнішня пам'ять витрачається досить економно.

Проблемою є те, що при виконанні операцій модифікації дуже часто можуть виникати розщеплення та злиття. Щоб досягти ефективного використання зовнішньої пам'яті з мінімізацією числа розщеплень та злиття, застосовуються складніші прийоми, у тому числі:

· Випереджувальні розщеплення, тобто. розщеплення сторінки не при її переповненні, а дещо раніше, коли ступінь заповненості сторінки досягає певного рівня;

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

· Злиття 3-в-2, тобто. породження двох листових сторінок на основі вмісту трьох сусідніх.

Слід зазначити, що з організації мультидоступу до B-дерев, характерного під час їх використання в СУБД, доводиться вирішувати ряд нетривіальних проблем. Звичайно, грубі рішення очевидні, наприклад, монопольне захоплення B-дерева на все виконання операції модифікації. Але існують і тонші рішення.

Збалансоване дерево автоматично не врівноважує розподіл ключів у межах дерева так, щоб половина ключів знаходилася б на одній стороні дерева, а інша половина - на іншій. Очевидно, що немає необхідності перебудовувати дерево щоразу, коли додаються або видаляються ключі. Однак якщо ключі додаються або видаляються тільки на одній стороні дерева, то розподіл індексних ключів може стати нерівномірним, з невеликою кількістю розріджених і навіть спустошених блоків з одного боку дерева. У цьому випадку індекс рекомендується перебудувати.

На В-деревах для вилучення даних на запит може використовуватися механізм швидкого повного перегляду (fast full scan). Цей механізм дає суттєві переваги, якщо всі дані з конкретної таблиці дані можуть бути отримані тільки з індексу. При швидкому повному перегляді ефективне багатоблочне введення/виведення, яке зазвичай використовується для повних переглядів таблиць, використовується для прочитання всіх листових блоків В-дерева. Оскільки кількість листових блоків індексу, швидше за все, набагато менше, ніж блоків даних у таблиці, для виконання запиту потрібно переглянути менше блоків. Тому перегляд індексу відбудеться значно швидше, ніж повний перегляд таблиці, хоча іноді нерівномірний розподіл ключів знижує ефективність швидкого повного перегляду, оскільки потрібно переглянути більше листових блоків (містять мале або взагалі нульове число елементів). При цьому слід враховувати наявність або відсутність у таблиці порожніх значень, які, як було сказано вище, до індексу не заносяться.

В-дерева можна використовувати для пошуку даних, як за умовами рівності, так і за умовами нерівності. Це єдиний тип індексів, який можна використовувати для предикатів нерівності: LIKE, BETWEEN, “>”, “>=”, “<”, “<=”. Исключение представляет случай использования предиката LIKE при сравнении с шаблоном вида ‘%вираз’або ‘_ вираз'. В-дерева зберігають лише непусті значення ключів, так що можна побудувати розріджене В-дерево.

Структура B-дерева має такі переваги:

B-дерево автоматично підтримується у збалансованому вигляді.

Всі блоки-листя в дереві розташовані на одному рівні, отже, пошук будь-якого запису в індексі займає приблизно один і той же час.

B-дерева забезпечують хорошу продуктивність для широкого спектру запитів, включаючи пошук за конкретним значенням та пошук у відкритому та закритому інтервалах (завдяки посиланням між блоками-листами).

Модифікація даних таблиці виконується досить ефективно, т.к. в блоках індексу зазвичай є вільне місце розміщення нових значень, а повна перебудова дерева виконується досить рідко.

Продуктивність B-дерева однаково хороша для дрібних і високих таблиць, і змінюється значно у разі зростання таблиці.

Використання індексів

Синтаксис команди create index наступний:

create index<имя_индекса>

on<имя_таблицы>(<поле1> [, <поле2>,...]) [<параметры>];

Ім'я індексу має бути унікальним серед імен об'єктів БД. Якщо індекс складової, то поля, що входять до нього, перераховуються через

кому. Необов'язкові<параметры>залежить від використовуваної СУБД. Наприклад, в Oracle за допомогою наступної команди можна створити складовий індекс для таблиці СПІВРОБІТНИКИ (EMP) по полямПрізвище (fam) та Ім'я (name):

create index ind_emp_name на emp(fam, name) TABLESPACE MY_INDEXES;

Індекси та таблиці бажано створювати у різних табличних просторах.

Використання індексів

Вибір стовпців для індексу визначається такими міркуваннями:

Насамперед вибираються стовпці, які часто зустрічаються в умовах пошуку.

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

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

Чи не індексуються стовпці, які часто оновлюються, т.к. команди оновлення ведуть до втрати часу оновлення індексу.

Не індексуються стовпці, які часто використовуються як аргументи виразів або функцій: зазвичай це не дозволяє використовувати індекс.

Використання індексів

У деяких випадках використання складеного індексу краще, ніж одиночного, а саме:

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

Декілька стовпців з низькою селективністю в комбінації один з одним можуть дати набагато більшу селективність.

Звернення до складового індексу можливе лише в тому випадку, якщо в умовах вибору беруть участь стовпці, які є лідируючої частиною складового індексу. Якщо індекс, наприклад, включає поля (X, Y, Z), то звернення до індексу відбуватиметься у тих випадках, коли в умови запиту беруть участь поля XYZ, XY або X, причому саме в такому порядку.

Використання індексів

Запитання. Чи система користуватиметься індексом при виконанні наступних запитів:

1) SELECT * FROM emp;

2) SELECT * FROM emp

WHERE name = "Даль";

3) SELECT * FROM emp

WHERE sex = "ж";

4) SELECT depno, count(*) FROM emp GROUP BY depno;

5) SELECT * FROM emp e, child c WHERE e.tabno = c.tabno;

Необхідна умова використання індексу: у запиті є умова значення індексованого поля.

Достатня умова використання індексу: запит щодо індексу виконується швидше, ніж без індексу (підвищення ефективності).

Індекс має сенс, якщо необхідно забезпечити доступ одночасно лише до 4-5% даних таблиці. Пам'ятайте, що застосування індексів для вилучення рядків вимагає двох операцій читання: індексу та потім таблиці.

Уникайте створення індексів для порівняно невеликих таблиць. Для таких таблиць найбільше підходить повне сканування. У разі невеликих таблиць немає потреби у зберіганні даних і таблиць, і індексів.

Створюйте первинні ключі для всіх таблиць. При призначенні стовпця як первинного колюча Oracle автоматично створить індекс цього стовпця.

Індексуйте стовпці, що беруть участь у багатотабличних операціях з'єднання.

Індексуйте стовпці, які часто використовуються у конструкціях WHERE.

Використання індексів у Oracle

Індексуйте стовпці, які беруть участь у операціях ORDER BY та GROUP BY або інших операціях, таких як UNION та DISTINCT, що включають сортування. Оскільки індекси вже відсортовані, обсяг роботи щодо виконання необхідного сортування даних для згаданих операцій буде суттєво скорочено.

Стовпці, що стоять із довго-символьних рядків, зазвичай погані кандидати на індексацію.

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

Індексуйте таблиці, в яких мало рядків мають однакові значення.

Зберігайте кількість індексів невеликою.

Складові індекси можуть знадобитися там, де одностовцеві значення власними силами не унікальні. У складових індексах першим стовпцем ключа повинен бути стовпець, в якому кількість рядків з однаковим значенням мінімальна.

Побутові індекси Oracle

Бітові індекси (BITMAP) використовують бітові карти для вказівки значення індексованого стовпця. Це ідеальний індекс для стовпця з низькою кардинальністю (кількість унікальних записів у таблиці мало) при великому розмірі таблиці.

Ці індекси зазвичай не підходять для таблиць з інтенсивним оновленням, але добре підходять для додатків сховищ даних. Бітові індекси складаються з бітового потоку (одиниць та нулів) для кожного стовпця індексу. Бітові індекси дуже компактні порівняно з нормальними індексами на основі B-дерев.

Побутові індекси Oracle

Для створення бітового індексу використовується оператор ^ CREATE BITMAP INDEX day_ind ON tab(day) TABLESPACE MY_INDEXES;

Індекси Oracle з реверсованим ключем

Індекси з реверсованим ключем - це, по суті, те саме, що і індекси B-дерев, за винятком того, що байти даних ключового стовпця при індексації змінюють порядок на протилежний. Порядок стовпців залишається недоторканим, змінюється лише порядок байтів. Найбільша перевага застосування індексів з реверсивним ключем полягає в тому, що вони унеможливлюють неприємні наслідки впорядкованої вставки значень в індекс. Ось як створюється індекс із реверсованим ключем:

CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;

При використанні індексу з реверсованим ключем бази даних не зберігає ключі індексу один за одним у лексикографічному порядку. Таким чином, коли у запиті є предикат нерівності, відповідь виходить повільніше, оскільки база даних змушена виконувати повне сканування таблиці. При індексі з реверсованим ключем база даних не може запустити запит щодо діапазону ключа індексу.

Платформа Oracle дозволяє за допомогою інструкції CREATE INDEX створювати індекси за таблицями, секційованими таблицями, кластерами та індекс-таблицями (index-organized tables), а також скалярними атрибутами об'єктів об'єктних таблиць (typed table) і стовпцями вкладених таблиць. Платформа Oracle також дозволяє використовувати кілька типів індексів, у тому числі звичайні ієрархічні (B-tree) індекси, індекси на основі бітових карток (BITMAP) (використовуються для стовпців, у яких кожне значення повторюється 100 і більше разів), секційовані індекси, індекси, пов'язані з функцією (засновані на виразі, а не на значенні в стовпці), та предметні індекси (domain index).

Імена індексів Oracle повинні бути унікальними в межах схеми, а не тільки в межах таблиці, з якою вони пов'язані.

Платформа Oracle також підтримує інструкцію ALTER INDEX. Вона використовується для зміни або перебудови існуючого індексу без його видалення та повторного створення.

Синтаксис інструкції CREATE INDEX Oracle наступний.

CREATE INDEX ім'я_індексу (ON

(ім'я_таблиці ((стовпець | вираз) [, …]) [(INDEXTYPE IS

тип_індексу | NOPARALLEL] | CLUSTER ім'я_кластера |

FROM имя_таблицы WHERE умова ) [(LOCAL секціонування |

GLOBAL секціонування)] [параметри_фізичних_атрибутів] [(LOGGING | NOLOGGING)]

[(TABLESPACE имя_табличного_пространства DEFAULT)] [(COMPRESS int | NOCOMPRESS)] [(NOSORT |

SORT)] [(PARALLEL | NOPARALLEL)]

Синтаксис інструкції ALTER INDEX є наступним.

ALTER INDEX ім'я_індексу

PARAMETERS ("параметри_00С1") | параметри_зміни_секціонування_індексу | параметри_перебудови |

)] ] [(PARALLEL | NOPARALLEL)] [(LOGGING | NOLOGGING)]

[Параметри_фізичних_атрибутів])

Де пропозиції, що не входять до стандарту ANSI, такі:

Замість індексування кожного рядка кожного значення індексу створюється бітова карта. Бітові карти найкраще використовуватиме таблиць з невеликою кількістю конкурентних запитів, наприклад таблиць з високою інтенсивністю читання. Індекси на основі бітових карток несумісні з індексами з глобальним секціонуванням, пропозицією INDEXTYPE та індекс-таблицями (index-organized table) без зв'язку з таблицею відповідності (mapped table).

ASC | DESC

Визначає розташування значень індексу у висхідному (ASQ або низхідному (DESQ порядку. Якщо пропозиція опущена, за замовчуванням приймається ASC). DESC є деякі функціональні відмінності.Пропозиції ASC і DESC не можна використовувати разом із пропозицією INDEXTYPE.Пропозиція DESC ігнорується при використанні індексів на основі бітових карг (BITMAP).

INDEXTYPE IS munjuidenca

Створюється індекс певного користувачем типу тип_індексу. Предметні індекси (domain index) вимагають, щоб тип користувача вже існував (зверніться до розділу «Інструкція CREATE/ALTER TYPE»). Якщо для типу користувача потрібні аргументи, їх можна передати за допомогою пропозиції PARAMETERS. За бажання можна паралелізувати створення типізованого індексу за допомогою пропозиції PARALLEL, яка детально розглядається нижче.

CLUSTER ім'я_кластера

Оголошується кластерний індекс із зазначенням існуючого імені_клаетера. У Oracle кластерний індекс фізично поєднує дві таблиці, які часто опитуються за однаковими стовпцями, зазвичай стовпчиками первинного та зовнішнього ключів. (Кластери створюються специфічною для Oracle командою CREATE CLUSTER.) Таблиці та стовпці в кластерному індексі не потрібно оголошувати, оскільки таблиці та індексовані стовпці вже оголошувалися в раніше виконаній команді CREATE CLUSTER.