Том Кайт: про видалення дублікатів і порядку спрацьовування тригерів

1. Порядок спрацьовування тригерів
2. Об'єктна привілей SELECT дозволяє блокування
3. Складні обмеження цілісності
4. З чого починати
5. Деякі "солодощі" в сервері Oracle Database 10 g Release 2

2007 р

Джерело: журнал Oracle Magazine, March-April 2006
( http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html ).

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

Питання. Будь ласка, допоможіть мені написати SQL-запит для позбавлення від дублікатів, які нижче позначені як XXX. Дублюючі записи для мене - записи, які мають однакові значення в стовпцях SA і SB, незалежно від їх позиції. (Первинний ключ - об'єднані стовпці SA і SB).

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

А поки для видалення з таблиці дублікатів я можу використовувати деякі вбудовані функції і аналітику.

Використовуючи аналітику, я буду розділяти, розбивати, дані за допомогою функцій LEAST (SA, SB) і GREATEST (SA, SB). Тобто, спочатку я знайду менше значення стовпців SA і SB, а потім більше значення, щоб рядки зі значеннями A, B перебували в одному і тому ж розділі, що і рядки зі значеннями B, A. Після розбиття даних за допомогою цих функцій я сортую рядки за значеннями стовпців SA і SB і застосовую до кожного рядка функцію row_number (). Все, що я повинен зробити для того, - знайти будь-які рядки, у яких номер рядка row_number () <> 1 (не перша рядок в розділі - в кожному розділі мені потрібна тільки одна рядок), і видалити їх.

Цей SQL-код схожий на показаний в лістингу 1.

Лістинг 1. Видалення дублікатів з таблиці t.

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

Потім створіть складовою первинний ключ за стовпцями SA і SB. Таким чином ви ніколи не будете мати A, B і B, A; оскільки B, A спочатку не буде вставлено. Якщо ви все ж хочете мати в первинному ключі значення B, A; ви можете використовувати індекс по ключу-функції:

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

Ця тема вилилася в досить цікаве інтерактивне обговорення, коли хтось запитав: "А що щодо трьох стовпців або N стовпців"? Ви можете прочитати вихідне обговорення на сайті asktom.oracle.com , Там же розглядалися альтернативні оператори DELETE, які також дозволяють видаляти дублікати.

Порядок спрацьовування тригерів

Питання. У мене є таблиця і два або більше тригерів AFTER INSERT / AFTER DELETE. У мене є два-три тригера AFTER INSERT для однієї і тієї ж таблиці. Чи можете ви розповісти про порядок спрацювання тригерів AFTER INSERT? (Я намагався визначити це, використовуючи позначки часу (time stamps), але всі вони вказують однаковий час).

Відповідь. Про порядок спрацьовування тригерів я можу сказати тільки наступне:

• будуть спрацьовувати все тригери BEFORE в якомусь порядку;
• будуть спрацьовувати все тригери BEFORE FOR EACH ROW в якомусь порядку;
• будуть спрацьовувати все тригери AFTER FOR EACH ROW в якомусь порядку;
• а потім будуть спрацьовувати все тригери AFTER в якомусь порядку.

В межах одного типу тригерів ви не повинні розраховувати на порядок спрацьовування цих тригерів. Навіть якщо ви виявили, що три тригера BEFORE запускаються в такому порядку: TRIGGER_A, TRIGGER_B, TRIGGER_C; ви не можете покладатися на це. У документації Oracle (Сервер Oracle Database. Керівництво розробника додатків. Основи) ясно написано: "Якщо для таблиці існує кілька тригерів одного типу, СУБД Oracle виконує їх в довільному порядку".

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

Побічна примітка: під час інтерактивного обговорення виникло питання: "Чому в СУБД Oracle взагалі дозволяється використання множинних тригерів одного і того ж типу"? Причина сходить до первісної реалізації в сервері Oracle Database механізму тиражування. У журналах матеріалізованих уявлень (materialized view logs) - раніше в версії 7.0 вони називалися просто журналами моментальних копій (snapshot logs) - для реєстрації операцій мови маніпулювання даними (DML), які виконувалися на цій таблиці, використовувалися тригери бази даних. До підтримки в сервері бази даних множинних тригерів одного і того ж типу це накладало на розробників обмеження: або не використовувати сам цей тип тригерів (який вже використовувався сервером Oracle), або не використовувати журнали матеріалізованих уявлень. Жоден з тих варіантів не був привабливим, тому в сервер бази даних була додана можливість використання множинних тригерів одного і того ж типу.

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

Об'єктна привілей SELECT дозволяє блокування

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

Кілька тижнів тому один з наших нових розробників заблокував головну таблицю промислової бази даних і призупинив всю пакетну обробку. Всі адміністратори бази даних клялися, що не можна встановити блокування, маючи тільки об'єктну привілей SELECT, і ми втратили багато часу з'ясовуючи, як розробник заблокував таблицю. Ми відкрили в Oracle запит на технічну підтримку (TAR) і отримали відповідь: "Так, ви можете заблокувати рядки, маючи тільки об'єктну привілей SELECT. Фактично, ви можете заблокувати всю таблицю оператором LOCK TABLE". Я хочу знати, скільки адміністраторів баз даних не знають про це, і зрозуміти причину дозволу блокування, коли не можна модифікувати дані.

Відповідь. Таке блокування "відомо" і допускається, я згоден, що мало хто знає про цю особливість (в цьому полягає основна причина мого рішення про публікацію цього питання - підвищити рівень знань). В описі оператора LOCK TABLE (Сервер Oracle Database. Довідник з мови SQL), наприклад, сказано: "Необхідні умови: таблиця або подання має належати вашої власної схемою або ви повинні мати системну привілей LOCK ANY TABLE або будь-яку об'єктну привілей на таблицю або подання . (виділено мною)

Зауважимо, ці необхідні умови не обов'язково означають можливість модифікації даних, але і можливість читання даних вказує на можливість блокування цих даних (забезпечувати їх стійке стан, "заморожувати", забороняти модифікацію). Так працює оператор SELECT ... FOR UPDATE. Отже, чому це правильно?

Наступний сценарій показує, чому це правильно. Припустимо, я маю доступ по читанню до таблиці конференц-залів і доступ по читанню-запису до таблиці розкладу засідань. Мені потрібно зарезервувати конференц-зал X. Для того щоб зробити це надійно (щоб уникнути накладень) і ніхто інший не міг зарезервувати конференц-зал X, я повинен заблокувати цей конференц-зал (в таблиці конференц-залів). Транзакція, в якій резервуються зали, - процедура, що зберігається, як це повинно і бути - всі спроби модифікації таблиці розкладу засідань виконуються за допомогою цієї процедури. Процедура блокує цей конференц-зал (в таблиці конференц-залів) оператором SELECT ... FOR UPDATE, щоб ніхто інший не міг зарезервувати його.

Інший сценарій, який показує, чому читання може заблокувати таблицю: я хочу видалити рядок з таблиці P, яка є батьківської таблицею таблиці C. У мене є доступ по читанню-запису до таблиці P і доступ тільки з читання до таблиці С. Я намагаюся видалити рядок з таблиці P, але перед цим я повинен заблокувати частину таблиці C (якщо існують індексовані зовнішні ключі) або всю цю таблицю (якщо немає індексу по зовнішньому ключу).

Складні обмеження цілісності

Питання. Я написав на мові PL / SQL збережену процедуру, але думаю, вона не буде працювати правильно з одночасним доступом до неї декількох користувачів. Ця процедура перевіряє, що рядок з певними значеннями не існує і ініціює помилку, якщо вона існує. Якщо рядок не існує, процедура виконує деякі обчислення, а потім вставляє рядок. Я думаю, якщо інший користувач вставив рядок в цю ж таблицю і зафіксував цю вставку після того, як моя збережена процедура виконала перевірку, але до того, як вона зафіксувала вставку, то в таблицю можуть бути вставлені невірні дані. Ця проблема не може бути усунена за допомогою унікального ключа, оскільки поля не завжди повинні бути різними - тільки іноді. Єдине рішення, яке я зміг придумати, полягає в тому, щоб перевіряти умова на початку процедури, що, а потім знову перевіряти в кінці, і в разі будь-якої невдалої перевірки виконувати відкат. Чи є кращий спосіб зробити це?

Таблиця має три стовпці: A, B і С; комбінація значень стовпців A і B завжди повинна бути унікальною, якщо тільки значення стовпця C не дорівнює 'W'. Якщо в стовпці C міститься значення 'W', то значення стовпців A і B можуть збігатися зі значеннями в інших рядках. Крім того, стовпець B може мати невизначене значення (null). Чи можна створити обмеження для цього?

Відповідь. Ви маєте рацію, при роботі в середовищі з багатьма користувачами можуть бути проблеми. Фактично неможливо визначати крос-стороковие (це ваш випадок) або крос-табличні (типу зовнішніх ключів) обмеження цілісності. У більшості випадків я бачу спроби реалізувати це в кодах програм або за допомогою тригерів, що неправильно для багатокористувацької середовища. Як ви помітили, якщо два користувача одночасно роблять одне і теж, вони не бачать роботу один одного. Щоб досягти мети, потрібно фактично серіалізовать модифікації цієї таблиці, використовуючи оператор LOCK TABLE.

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

• ключі, які містять тільки значення NULL, не включаються до індекси типу "B-дерево". Якщо ви створюєте індекс на T (C1, C2) і існує рядок, в якій обидва стовпці C1 і C2 мають значення NULL, то такий рядок не поміщається в індекс. Отже, ви можете в сервері Oracle селективно індексувати рядки, якщо ви будете повертати NULL-ключ (повністю NULL-ключ);
• ви може індексувати складні функції, включаючи користувацькі і вбудовані.

І унікальний індекс по стовпцях A і B, коли стовпчик C <> 'W':

Я отримав саме те, що вам потрібно:

Значення NULL, NULL виходять тоді, коли стовпчик C = 'W', а значення стовпців A, B, коли стовпчик C <> 'W', тобто я унікально Індексую стовпці A, B тільки тоді, коли стовпчик C <> 'W '- в точності те, що вам потрібно. Це як би схоже на індексування пропозиції WHERE.

З чого починати

Питання. Протягом минулих чотирьох років я був адміністратором бази даних SQL Server 2000, тепер я дуже хочу вивчити СУБД Oracle. З чого мені починати?

Відповідь. Я часто отримую це питання, тому я придумав план початкового вивчення документації Oracle.

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

Мал. 1. План вивчення документації сервера Oracle Database 10g

Я також пропоную завантажити і інсталювати безкоштовну СУБД Oracle Database 10 g Express Edition . Обсяг інформації, що завантажується відносно невеликий (приблизно 150 МБ), а для подальшої інсталяції і запуску в ОС Windows або Linux потрібно близько п'яти хвилин - чудовий спосіб почати вивчати СУБД Oracle.

І нарешті, беріть участь в форумах, починайте задавати питання. Деякі пропоновані форуми:

Usenet newsgroups (Система телеконференцій Інтернету). З цього я починав в жовтні 1994 р, коли відправив своє перше повідомлення. Що дивно, маса людей, які брали участь в цих телеконференціях тоді, все ще там і сьогодні. Зараз я не відправляю стільки ж повідомлень, скільки раніше, але час від часу як і раніше переглядаю телеконференції, в яких активну участь багато людей.

Oracle-l . (Модератор - Стів Адамс. - Прим. Пер.) Тут беруть участь такі відомі фахівці, як Вольфганг Брайтлінг (Wolfgang Breitling), Лекс де Хаан (Lex de Haan), Пол Дрейк (Paul Drake), Младен Гога (Mladen Gogala), Тім Горман (Tim Gorman), Джонатан Льюїс (Jonathan Lewis,), Найалла Личфилд (Niall Litchfield), Коннор Макдоналд (Connor McDonald), Кері Міллсап (Cary Millsap), Танель Подер (Tanel Poder), Марк Пауелл (Mark Powell), Піт Шарман (Pete Sharman) і Джаред Стілл (Jared Still). (І цей список далеко не вичерпний.) Крім того, тут дуже високий дух співдружності. У кожній конференції або подію будь-якого масштабу зазвичай беруть участь практично всі. У багатьох учасників склалися хороші дружні відносини.

The Oracle Users 'Co-Operative FAQ (Спільні "Часті запитання" користувачів продуктів Oracle). Спонсорується і підтримується Джонатаном Льюїсом. Не забувайте, тут треба ділитися. Якщо ви виявили часто задається питання там, де ви працюєте, то, ймовірно, він буде таким же у всьому FAQ-світі, так що ви теж можете внести і свій внесок.

OTN discussion forums (Дискусійні форуми на сайті OTN). Тут ви можете навіть отримати відповідь від людини, який написав програмне забезпечення, про який ви запитуєте.

OracleMetaLink . Я повинен згадати цей сайт. На ньому також проводяться інтерактивні дискусійні форуми.

(З російськомовних дискусійних форумів варто відзначити форум на сайті sql.ru , Як має найбільший трафік. Однак культура спілкування багатьох його учасників помітно поступається закордонній. Разом з тим, бере участь також і багато висококласних фахівців. - Прим. пер. )

Independent Oracle Users Group . (IOUG, незалежна група користувачів Oracle) Я рекомендую цей сайт не так через дискусійних форумів (у них невеликий трафік), а як організацію в цілому. Я - великий прихильник IOUG і намагаюся зробити все, що можу, для її підтримки. Я настійно закликаю всіх вас, хто пов'язав свою кар'єру з Oracle, вступити в неї. Само по собі мережеве спілкування - це одне. Зустрічайтеся з людьми, які працюють в різних компаніях у вашій предметної області, окремо від фахівців в інших областях. Послухайте про їхній досвід роботи; поділіться своїм. Проводяться великі конференції - я брав участь в американській IOUG-A, британської UKOUG (і планую в цьому році бути там знову), ірландської Irish OUG, не рахуючи інших подій OUG, яких надто багато, щоб навіть почати їх перераховувати. Це - велика організація з великим технічним змістом і сильним лідерством.

Деякі "солодощі" в сервері Oracle Database 10 g Release 2

До теперішнього часу дуже багато написано про нові функціональні можливості сервера Oracle Database 10g Release 2 (наприклад см. oracle.com/ru/oramag/feb2006/dev_asktom55.html ), Таких, як прозоре шифрування даних, нове DML-пропозиція LOG ERRORS, умовна компіляція в мові PL / SQL і так далі. Тепер я хотів би розглянути деякі інші нові функціональні можливості, обговорення яких було здійснено на сайті asktom.oracle.com .

Почнемо з поліпшеною підтримки великих об'єктів (LOB) в розподілених системах. У середовищі PL / SQL максимальний розмір великих об'єктів, до яких можливий доступ через зв'язку баз даних, виріс до 32 КБ (максимальний розмір рядка в мові PL / SQL) - в інтерфейсі рівня викликів СУБД Oracle (OCI) і середовищі Java / JDBC відсутні такі обмеження, тому в них можна мати доступ через зв'язку баз даних до великих об'єктів будь-якого розміру.

У диспетчері відновлення Recovery Manager (RMAN) з'явилася нова функціональна можливість - шифрування резервних копій. Воно може виконуватися прозоро з використанням електронних гаманців (Oracle Wallet), що дуже схоже на прозоре шифрування даних, або ж під час резервування ви можете вказати пароль, який вам хочеться. Ви також можете використовувати комбінацію обох цих способів, наприклад, це буде корисно, коли електронний гаманець не доступний під час відновлення. Звичайно, ви повинні піклуватися про гарантії доступності гаманця або пароля, інакше зашифровані дані можна буде відновити.

Інша нова Функціональна можлівість утіліті RMAN - можлівість пропускаті во время Резервування бази даних будь-які Невикористані блоки. У минулому утиліта RMAN "стискала" набори резервних копій, пропускаючи (НЕ резервуючи) будь-які блоки бази даних, які ніколи не використовувалася, але після того, як в блок хоча б один раз вставлялися дані, він завжди буде резервуватися. Тобто, якби ви створили пусте табличний простір розміром 100 ГБ і не поміщали в нього нічого, то під час резервування утиліта RMAN пропустила б в цьому табличному просторі все блоки (оскільки вони ніколи не містили ніяких даних). Якби ви згодом заповнили все це табличний простір даними (використовували б все блоки), а потім видалили б таблицю з цими даними, то в деякій момент часу утиліта RMAN створила б резервну копію всіх цих блоків, оскільки вони містили дані (були сформатовані), навіть при тому, що вони в даний час порожні. Починаючи з сервера Oracle Database 10g Release 2, утиліта RMAN має можливість не резервувати такі блоки, що не містять ніякої інформації, яку дійсно потрібно резервувати.

З міркувань безпеки, серйозно зменшений набір привілеїв ролі CONNECT. У минулому роль CONNECT дозволяла не тільки створювати сеанси, але також і кластерні сегменти бази даних, зв'язку бази даних, послідовності, синоніми, таблиці, уявлення і змінювати параметри сеансів. (Вона була більше схожа на роль розробника, а не на роль для простого підключення до бази даних.) Їй були надані дуже серйозні права. Починаючи з сервера Oracle Database 10 g Release 2, роль CONNECT має тільки привілей створення сеансів CREATE SESSION.

І нарешті, довгоочікувана функціональна можливість сервера Oracle Database 10 g Release 2 - можливість видалення з табличного простору файлу даних, який не містить ніяких даних (в ньому не виділено ніяких екстентів). Це дозволяє адміністратору бази даних видалити файл, який був випадково доданий в неправильні місце або табличний простір. У минулому такий файл назавжди закріплювався за табличним простором.

Це - тільки короткий список з багатьох нових функціональних можливостей сервера Oracle Database 10 g Release 2. Якщо вам цікаво дізнатися більше про це новому випуску, на сайті portal_db?selected=1/> oracle.com/pls/db102/portal.portal_db?selected=1 ознайомтеся з керівництвом New Features Guide (нові можливості). Крім того, не забувайте переглядати початкові розділи майже кожної книги комплекту документації Oracle, присвячені опису нових можливостей розглянутих в конкретному документі.

Ведучий цієї колонки Том Кайт ( Tom Kyte ) Працює в корпорації Oracle з 1993 року. Кайт - віце-президент Oracle, який очолює групу Oracle Public Sector, він автор книг "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а також ряду інших.