Головна → Налаштування Wi-Fi Oracle: Створення індексів, пов'язаних з обмеженням цілісності. Індекси Індекси оракл

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

Платформа 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.

Кері Міллсап, 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 " : " 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, спрощують ваші дії щодо зберігання даних у фізичній упорядкованості, що забезпечує чудову продуктивність.

Примітки:

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

B-Tree

Сімейство B-Tree індексів - це найбільш часто використовується тип індексів, організованих як збалансоване дерево, упорядкованих ключів. Вони підтримуються майже всіма СУБД як реляційними, і нереляційними, і майже всім типів даних.

Так як більшість, напевно, їх добре знає (або можуть прочитати про них, наприклад), то єдине, що, мабуть, слід тут відзначити, це те, що даний тип індексу оптимальний для множини з хорошим розподілом значень і високою потужністю (cardinality- кількість унікальних значень).

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

В даний момент всі дані СУБД мають просторові типи даних і функції для роботи з ними, для Oracle - це безліч типів і функцій у схемі MDSYS, для PostgreSQL - point, line, lseg, polygon, box, path, polygon, circle, MySQL - geometry, point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection, MS SQL - Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, GeometryCollection.
У схемі роботи просторових запитів зазвичай виділяють дві стадії або два ступені фільтрації. СУБД, які мають слабку просторову підтримку, відпрацьовують лише перший щабель (груба фільтрація, MySQL). Як правило, на цій стадії використовується наближене, апроксимоване уявлення об'єктів. Найпоширеніший тип апроксимації – мінімальний обмежуючий прямокутник (MBR – Minimum Bounding Rectangle).
Для просторових типів даних існують спеціальні методи індексування на основі R-дерев (R-Tree index) і сіток (Grid-based Spatial index).

Spatial grid

Spatial grid (просторова сітка) index – це деревоподібна структура, подібна до B-дерева, але використовується для організації доступу до просторових (Spatial) даних, тобто для індексації багатовимірної інформації, такої, наприклад, як географічні дані з двовимірними координатами (широтою та довготою) ). У цій структурі вузлами дерева виступають осередки простору. Наприклад, для двовимірного простору: спочатку вся батьківська площа буде розбита на сітку строго певного дозволу, потім кожен осередок сітки, в якій кількість об'єктів перевищує встановлений максимум об'єктів в осередку, буде розбита на підсітку наступного рівня. Цей процес буде продовжуватися доти, доки не буде досягнуто максимум вкладеності (якщо встановлено), або доки все не буде розділено до осередків, що не перевищують максимум об'єктів.

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

Quadtree

Quadtree - це підвид Grid-based Spatial index, в якому в батьківському осередку завжди 4 нащадки і роздільна здатність сітки варіюється в залежності від характеру або складності даних.

R-Tree

R-Tree (Regions Tree) – це також деревоподібна структура даних подібна до Spatial Grid, запропонована в 1984 році Антоніном Гуттманом. Ця структура даних теж розбиває простір безліч ієрархічно вкладених осередків, але які, на відміну Spatial Grid, нічого не винні повністю покривати батьківську комірку і можуть перетинатися.
Для розщеплення переповнених вершин можуть застосовуватися різні алгоритми, що породжує поділ R-дерев на підтипи: з квадратичною та лінійною складністю (Гуттман, звичайно, описав і з експоненційною складністю – Exhaustive Search, але він, природно, ніде не використовується).
Квадратичний підтип полягає в розбитті на два прямокутники з мінімальною площею, що покривають усі об'єкти. Лінійний – у розбиття за максимальною віддаленістю.

HASH

Hash-індекси були запропоновані Артуром Фуллером, і припускають зберігання не самих значень, а їх хешей, завдяки чому зменшується розмір (а, відповідно, і збільшується швидкість їх обробки) індексів великих полів. Таким чином, при запитах з використанням HASH-індексів, порівнюватися будуть не шукане значення поля, а хеш від шуканого значення з хешами полів.
Через нелінійність хеш-функцій цей індекс не можна сортувати за значенням, що призводить до неможливості використання в порівняннях більше/менше і «is null». Крім того, так як хеші не унікальні, то для хешей, що збігаються, застосовуються методи дозволу колізій.

Bitmap

Bitmap index – метод бітових індексів полягає у створенні окремих бітових карт (послідовність 0 і 1) для кожного можливого значення стовпця, де кожному біту відповідає рядок з значенням, що індексується, а його значення дорівнює 1 означає, що запис, відповідна позиції біта містить індексоване значення для даного стовпця чи властивості.

Reverse index

Reverse index – це також B-tree індекс але з реверсованим ключем, використовуваний переважно монотонно зростаючих значень (наприклад, автоінкрементний ідентифікатор) в OLTP системах із єдиною метою зняття конкуренції за останній листовий блок індексу, т.к. завдяки перевертанню значення два сусідні записи індексу потрапляють у різні блоки індексу. Він не може використовуватися для пошуку.
Приклад:
Як бачите, значення в індексі змінюється набагато більше, ніж саме значення в таблиці, і тому в структурі b-tree вони потраплять у різні блоки.

Inverted index

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

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

Partial index

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

Function-based index

Самим гнучким типом індексів є функціональні індекси, тобто індекси, ключі яких зберігають результат функцій користувача. Функціональні індекси часто будуються для полів, значення яких проходять попередню обробку перед порівнянням у команді SQL. Наприклад, при порівнянні рядкових даних без урахування регістру символів часто використовується функція UPPER. Створення функціонального індексу з функцією UPPER покращує ефективність таких порівнянь.
Крім того, функціональний індекс може допомогти реалізувати будь-який інший відсутній тип індексів даної СУБД (крім, мабуть, бітового індексу, наприклад, Hash для Oracle)

Зведена таблиця типів індексів

	MySQL	PostgreSQL	MS SQL	Oracle
B-Tree index	Є	Є	Є	Є
Просторові індекси, що підтримуються (Spatial indexes)	R-Tree з квадратичним розбиттям	Rtree_GiST(використовується лінійне розбиття)	4-х рівневий Grid-based spatial index (окремі для географічних та геодезичних даних)	R-Tree c квадратичним розбиттям; Quadtree
Hash index	Тільки таблиці типу Memory	Є	Ні	Ні
Bitmap index	Ні	Є	Ні	Є
Reverse index	Ні	Ні	Ні	Є
Inverted index	Є	Є	Є	Є
Partial index	Ні	Є	Є	Ні
Function based index	Ні	Є	Є	Є

Варто згадати, що PostgreSQL GiST дозволяє створити для будь-якого власного типу даних індекс заснований на R-Tree. Для цього необхідно продати всі 7 функцій механізму R-Tree.
Додатково можна прочитати тут:

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

Створення Індексу

Синтаксис створення індексу:

ON table_name (column1, column2, . column_n)
[COMPUTE STATISTICS];

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

Параметр COMPUTE STATISTICS командує Oracle збирати статистику в процесі створення індексу. Ця статистика використовується оптимізатором при виборі «plan of execution» у процесі виконання SQL-запиту.

Наприклад:

CREATE INDEX supplier_idx
ON supplier (supplier_name);

У цьому прикладі ми створили індексна таблиці supplier з ім'ям supplier_idx. Він містить лише одне поле – supplier_name.

Також ми можемо створити індексиз більшою, ніж одне, кількістю полів, як у наступному прикладі:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city);

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

CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;

Створення індексів на основі функцій

У Oracle ви не обмежені створенням індексів тільки на стовпцях таблиць. Ви можете створювати індекси на основі функцій.

Синтаксис створення індексу на основі функції:

CREATE INDEX index_name
ON table_name (function1, function2, . function_n)
[COMPUTE STATISTICS];

Наприклад:

CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));

У цьому прикладі ми створили індексзаснований на функції uppercase застосованої до поля supplier_name.

Однак, щоб бути впевненим, що Oracle оптимізатор використовує цей індекс, коли виконує ваші SQL запити, переконайтеся в
те, що значення UPPER(supplier_name) не повертає NULL. Щоб це перевірити, додайте вираз UPPER(supplier_name) IS NOT NULLоператор WHERE наступним чином: