Підвищення продуктивності запитів: Секціонування
секціонування
У багатьох базах даних в таблицях зберігається величезна кількість даних. Чим більше розмір таблиці, тим більше часу буде потрібно як для деяких операцій по вибірці рядків таблиці, так і для виконання деяких функцій адміністратора бази даних. Наприклад, для резервного копіювання та відновлення. Великі за розміром індексовані таблиці мають також великі індекси, які вимагають багато часу СУБД для роботи з ними.
Секціонування (partitioning) - це спосіб фізичного розподілу таблиць і індексів серед двох або більше табличних просторів в залежності від значень ключових колонок таблиць з метою підвищення продуктивності операцій введення / виводу. Фрагмент таблиці, розташований в окремому табличному просторі. будемо називати секцією таблиці. Секціонування також підвищує ефективність резервного копіювання та відновлення за рахунок виконання цих завдань за участю меншого обсягу даних. Обговоренню створення табличних просторів далі буде присвячений окремий розділ наступній лекції. Для розуміння даного матеріалу нам досить знати, що це зумовлений пойменований фрагмент пам'яті на одному або декількох дисках, до якого можна звертатися в пропозиціях SQL на ім'я.
У здійсненні секціонування одним з важливих понять є колонка таблиці, щодо значень якої СУБД буде робити фізичне рознесення таблиці з різних табличних просторів на жорстких дисках. Ця колонка називається ключем секціонування (partition key).
У СУБД Oracle підтримується кілька видів секціонування. секціонування за діапазоном, хеш-секціонування. складене секціонування. а також різні види секціонування індексів.
Секціонування за діапазоном
Секціонування за діапазоном (range partitioning) означає розподіл рядків таблиці на різні зумовлені табличні простору в залежності від значення ключа секціонування. Доступ до такої таблиці, як і до будь-якої іншої, здійснюється за її імені, причому доступ до секцій, розташованим в кожному табличному просторі. можна отримати окремо. Наприклад, таблицю, яка містить фінансові квартальні звіти організації, можна секціонувати за датою таким чином, що звіти по кожному кварталу будуть зберігатися в окремому табличному просторі. При такій організації секцій дані тільки по одному кварталу будуть вибиратися з одного табличного простору, що підвищить ефективність роботи бази даних в цілому.
Секціонування за діапазоном базується на впорядкування рядків таблиці в секціях (табличних просторах) на основі значення колонок ключа секціонування. Концептуально таблиця, секціонірованная за діапазоном, влаштована як на рис. 11.2 в прикладі нижче. Для створення секціонованих таблиць використовується команда SQL CREATE TABLE з пропозицією PARTITION. У СУБД Oracle ключ секціонування не може мати тип LONG.
Мал. 11.2. Приклад секціонування подіапазону
Приклад. Розглянемо систему обробки замовлень. Припустимо, що в ній є таблиця Sales. в якій зберігаються даних про кількість, часу і ціною продажу для кожного клієнта. Проектувальник бази даних може використовувати секціонування за діапазоном, а саме - по кварталу, для подання цієї таблиці в базі даних. Припустимо, що ми маємо чотири певні раніше табличних простору c іменами ts_01, ts_02, ts_03, ts_04. розподілені по чотирьох дисків, як показано на малюнку нижче.
Фрагмент скрипта нижче визначає таблицю Sales з фізичним розміщенням секцій, як на малюнку вище:
Щоб отримати доступ до рядків таблиці, розташованим в певній секції, дізнатися про продажі в третьому кварталі, можна використовувати команду SELECT. як показано нижче:
Як ми можемо побачити, для цього потрібно вказати опцію PARTITION (ім'я секції) після імені таблиці в реченні FROM.
Адміністратор бази даних може легко видаляти, додавати, переміщати, розщеплювати, усікати і змінювати секції за допомогою команди ALTER TABLE. Видалити окрему секцію можна також, видаливши відповідне їй табличний простір.
Хеш-секціонування
Хеш-секціонування (hash partitioning) означає рівномірний розподіл рядків таблиці по призначеним табличних просторів в залежності від значення ключа секціонування. який в даному випадку хешіруется. Цей вид секціонування зручно застосовувати для рядків, у яких розподіл значень ключа секціонування нерівномірно або погано групується. Якщо проектувальник бази даних приймає рішення про створення хеш-секціонірованной таблиці, то він повинен досить точно представляти розмір цієї таблиці, оскільки вбудовані в СУБД Oracle алгоритми хешування використовують цей розмір для обчислення позиції рядка на фізичної сторінці бази даних. Неправильне визначення розміру таблиці може привести до великої кількості колізій, тобто до потрапляння рядків з різними значеннями ключа на одну і ту ж сторінку, що призводить до підтримки ланцюжків переповнення і додаткового вводу / виводу.
Приклад. Розглянемо ту ж таблицю Sales. що і в попередньому прикладі, і ту ж схему (рис. 11.2) табличних просторів. Однак використовуємо в якості ключа секціонування ідентифікацію клієнта. Відзначимо, що розподіл значень цієї колонки може бути дуже нерівномірно. Фрагмент коду SQL для створення хеш-секціонірованной таблиці Sales можна написати так:
Пропозиція PARTITION BY HASH (s_customer_id) вказує СУБД Oracle виконати секціонування таблиці по ключу секціонування - s_customer_id. Пропозиції виду (PARTITION q01 TABLESPACE ts_01 визначають ім'я секції st_q01 і її розміщення в відповідному табличному просторі ts_01.
складений секціонування
Складений секціонування (composite partitioning) є комбінацією секціонування за діапазоном і хеш-секціонування. Це означає, що таблиця спочатку розподіляється серед табличних просторів на основі діапазону значень ключа секціонування. далі кожна з отриманих секцій діапазонів ділиться на підлеглі секції або підсекції, і потім рядки рівномірно розподіляються серед підлеглих секцій за значенням хеш-ключа.
Приклад. Розглянемо ту ж, що і в попередньому прикладі таблицю Sales і ту ж схему (рис. 11.2) табличних просторів. Як ключ секціонування за діапазоном використовуємо дату продажу. Як ключ хеш секціонування -ідентифікація клієнта. Однак тепер кожна секція за діапазоном буде розділена на визначене число підсекцій. Фрагмент коду SQL для створення таблиці Sales з складовим секціонуванням можна написати так: