Майстерня Oracle: Секції в реальному світі

Ви звертали увагу, що в більшості статей про можливості Oracle для демонстрації переваг, які хочуть підкреслити, використовуються абсолютно тривіальні приклади? А чи звернули ви увагу, що тільки при спробі реального використання можливості починають виявлятися проблеми?

У цій статті представлені деякі з проблем, що виникають при реальному використанні можливості обміну секції (exchange partition). У статті була використана версія Oracle 9.2.0.3 – інші версії можуть вести себе інакше.

Що таке обмін секцій?


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

alter table pt_tab1 exchange partition p_9999
with table new_data
– Including indexes – необов'язкова конструкція
– Without validation – необов'язкова конструкція
;


Ця команда "зламує" словник даних і змінює місцями визначення зазначеної секції і таблиці new_data, Так що дані несподівано опиняються в потрібному місці в секціонованими таблиці. Більш того, з додаванням двох необов'язкових додаткових конструкцій, будуть замінені також визначення індексів і Oracle не буде перевіряти, чи належать реально дані зазначеної секції – тому обмін виконується дуже швидко.

Або так зазвичай пишуть.

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

Зазвичай, якщо ви заплатили за підтримку секціонування (partitioning option), таблиці у вас – дуже великі, за ним є кілька індексів і пов'язані зовнішніми ключами таблиці. Так що ж відбувається в реальному житті при виконанні обміну секцій?

У що обходиться обмін секцій?


Давайте почнемо з дійсно простого випадку – обміну таблиці з 1000000 рядків з порожньою секцією. Давайте почнемо з SQL-операторів для створення секціонованими таблиці та не секціонованими таблиці – ми будемо повторно використовувати цей код, з деякими змінами, в частині статті. Нам також знадобиться табличний простір, у якому буде близько 200 Мбайт вільного місця.

create table parent (
id number(12,6),
v1 varchar2(10),
padding varchar2(100)
)
partition by range(id) (
partition p1000 values less than (1000),
partition p3000 values less than (3000),
partition p5000 values less than (5000)
);

create table parent_ex (
id number(12,6),
v1 varchar2(10),
padding varchar2(100))
nologging – щоб заощадити трохи часу
;

insert /*+ append ordered full(s1) use_nl(s2) */
into parent_ex
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad(“x”,100)
from
sys.source $ s1, – має бути відповідна привілей
sys.source$ s2
where
rownum <= 1000000;


А тепер давайте обміняємо таблицю з секцією P5000, Якій і належать ці дані. Але давайте включимо timing, Щоб побачити, скільки часу на це буде потрібно.

alter table parent
exchange partition p5000 with table parent_ex;

Elapsed: 00:00:17.06


Що сталося з "дуже швидким" перенесенням? Повторіть тест з включеним sql_trace і ви знайдете в файлі трасування наступний SQL-оператор. Сервер Oracle перевіряє, чи є в таблиці parent_ex рядки, які не належать зазначеної секції таблиці parent.

select 1
from “PARENT_EX”
where TBL$OR$IDX$PART$NUM(“PARENT”,0,0,65535,”ID”) != :1

Для цього необхідно виконати повний перегляд таблиці і викликати функцію для кожного рядка в завантажується таблиці – уявіть собі результат у реальній системі з великими обсягами даних і завантаженій підсистемою вводу-виводу.

Але не бійтеся, – саме для таких випадків призначена конструкція without validation. Повторіть експеримент, але помістіть в кінці команди exchange цю конструкцію.

alter table parent
exchange partition p5000 with table parent_ex
without validation;

Elapsed: 00:00:00.00


Ур-ра – так працює набагато швидше! Але, не поспішайте з висновками; в реальних базах даних зазвичай є індекси й обмеження первинного або унікального ключа. Тому давайте повторимо вправу, але додамо обмеження первинного ключа до основної і обмінюваної таблиці перед обміном (Зверніть увагу на синтаксис версії 9 для повного вказівки індексу при додаванні обмеження – я вирішив забезпечити виконання обмеження унікального / первинного ключа за допомогою унікального індексу). У цьому випадку, ми, ймовірно, захочемо включити в процес обміну та індекси, щоб дані виявилися в основній таблиці з придатною до використання секцією індексу.

alter table parent
add constraint p_pk primary key(id)
using index (create index p_pk on parent(id) nologging local);

alter table parent_ex
add constraint px_pk primary key(id)
using index (create index px_pk on parent_ex(id) nologging);

alter table parent
exchange partition p5000 with table parent_ex
including indexes without validation;

Elapsed: 00:00:28.05


А тепер що сталося? Повтор (не такий вже швидкий) експерименту з включеним sql_trace видає представлений нижче SQL-оператор. Сервер Oracle перевіряє, що обмін не призведе до проблеми з унікальністю. Запит переглядає всю таблицю parent (Виключаючи секцію, яку ми обмінюємо), щоб дізнатися, чи немає дублікатів завантажуються нами рядків. Це собенно нерозумно, оскільки обмеження унікальності підтримується за допомогою локального індексу, тому він обов'язково включає ключ секціонування, а це означає, що рядок може бути тільки в одній секції, і ми вже пообіцяли (за допомогою конструкції without validation), Що всі рядки належать тій секції, в яку ми їх розміщуємо. Звичайно, вірно, що в інших секціях можуть бути рядки, які повинні б перебувати в вантажної секції, але і в цьому випадку я не думаю, що хочу витрачати час на їх пошук прямо зараз.

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

Зверніть увагу, зокрема, на оператори minus і intersect – Вони вимагають сортування всього результуючого безлічі, і я ще легко відбувся зі своїми 28 секундами, оскільки (a) у мене не було даних у вихідній таблиці parent, І (b) дані в таблиці parent_ex вже були відсортовані.

select “ID”
from “TEST_USER”.”PARENT_EX”
where not( “ID” is null)
intersect
select “ID”
from (
select “ID”
from “TEST_USER”.”PARENT”
minus
select “ID”
from “TEST_USER”.”PARENT” partition (P5000)
) B
where not( “ID” is null)

Чи можна обійти ці величезні витрати? Так, і все, що потрібно зробити – перевести обмеження по обидва таблиць в состяніе novalidate перед виконанням обміну.

alter table parent_ex modify primary key novalidate;
alter table parent modify primary key novalidate;

Загальне призначення опції novalidate для ограничени – повідомити сервер Oracle, що він не повинен перевіряти вже існуючі дані, щоб переконатися, що вони відповідають обмеженню. Нові дані або зміни, які ви будете робити в старих даних, будуть, однак, перевірятися, оскільки обмеження все одно діє.

Але тут є дуже тонка прихована пастка. Існує спеціальний алгоритм оптимізатора, який оцінює вартість обмежень первинного та унікального ключа нижче звичайного, якщо тільки обмеження не допускає відкладену перевірку або (як ми тільки що його встановили) не знаходиться в стані novalidate. Якщо ми змінимо стан обмежень з validate на novalidate, Щоб знизити витрати на обмін секцій, ми можемо виявити, що при цьому випадковий запит почав виконуватися інакше!

Так що, мабуть, краще залишити обмеження в стані validate, І не гнатися за "дешевизною", просто встановивши опцію without validation при обміні. Один прохід по додається даними може бути куди більш прийнятним, ніж альтернативні варіанти.

У вас реляційна база даних?


Але завжди слід чекати надалі новин і гірше. У даному випадку, у вас може бути дві секціонованими таблиці з деякою зв'язком між ними (таке я спостерігав у багатьох системах). Як це впливає на роботу з секціями?

create table child (
id_p number(12,6),
seq_c number(10),
v1 varchar2(10),
padding varchar2(100),
constraint c_fk_p foreign key (id_p) references parent,
constraint c_pk primary key (id_p, seq_c) using index local
)
partition by range(id_p) (
partition p1000 values less than (1000),
partition p3000 values less than (3000),
partition p5000 values less than (5000)
);

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

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

А потім все стає ще гірше! Якщо ви коли-то вирішите видалити старі секції, це можна зробити простим оператором:

alter table child drop partition p1000;
alter table parent drop partition p1000;

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

Перша проблема – якщо ви спробуєте видалити пару секцій головною / підпорядкованої таблиці, і в секції головної таблиці коли-небудь були якісь дані, то спроба видалити (або очистити, truncate) секцію головною таблиці призведе до видачі повідомлення про помилку:

ORA-02266: unique/primary keys in table
referenced by enabled foreign keys

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

Друга проблема – коли ви видаляєте секцію, кожну Секу після неї треба перенумерувати (внутрішньо) в словнику даних. Уявіть собі секціонованими таблицю з 3000 секцій і двома локально секціонованими індексами. При видаленні першої секції сервер Oracle перенумеруєте 9000 рядків у словнику даних – і робить це за рядком. Один оператор drop призводить до виконання 9000 окремих змін. Це нешвидко.

Нарешті, як тільки ви виконали перший оператор drop (Імовірно, для підлеглої таблиці), таблиці перестають бути однаково секціонованими – всі відповідні SQL-курсори робляться недійсними (таке відбувається при будь-якої операції супроводу з секціями) і оптимізуються заново, і оптимізатор не буде використовувати посекційне з'єднання (partition-wise joins), поки не буде вилучена відповідна секція. Треба добре подумати, обираючи час для видалення секцій.

Обхідні шляхи


У ваших спеціальних випадках ви цілком можете знаходити прийнятні рішення або навіть шляхи обходу цих проблем. Наприклад, один (безсумнівно, життєздатний) спосіб обійти проблему відключення обмежень зовнішнього ключа спирається на той факт, що можна видаляти секцію в головній таблиці, якщо в ній ніколи не було ніяких даних. Якщо "погратися" з цією ідеєю, можна знайти наступну стратегію видалення пари секцій за наявності обмежень всього лише в режимі novalidate:


Я не впевнений, проте, що це абсолютно безпечно. Що, якщо Відсутність повідомлення про помилку Oracle ORA-02266 у цьому особливому випадку – помилка? Що ви будете робити, якщо корпорація Oracle включить виправлення цієї помилки в наступний набір виправлень?

Висновок


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

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

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

Зупинити друк!


Незадовго до того, як я зібрався посилати цю статтю в DBAZine для публікації, я встановив оновлення до версії 9.2.0.4 і виявив зауваження в списку виправлених помилок, що затверджує, що помилка, яка "може викликати повільне виконання обміну для таблиць з обмеженнями унікальності ", була виправлена. Рішення складалося у видаленні перевірки обмеження, описаної в цій статті.

Джонатан Льюїс (Jonathan Lewis) – незалежний консультант з більш ніж 18-річним досвідом використання Oracle. Він спеціалізується на фізичному проектуванні баз даних і стратегії використання сервера Oracle. Джонатан – автор книги "Practical Oracle 8i – Building Efficient Databases", Опублікованій видавництвом Addison-Wesley, і один з найбільш відомих лекторів серед фахівців з Oracle у Великобританії. Докладніше про його публікаціях, презентаціях і семінарах можна дізнатися на сайті www.jlcomp.demon.co.uk, де також знаходиться список ЧаВО The Co-operative Oracle Users” FAQ з дискусійних групах Usenet, пов'язаним з СУБД Oracle.

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


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

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

Ваш отзыв

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

*

*