Подробиці Oracle, Інші СУБД, Бази даних, статті

Цей текст задуманий як збірник неочевидних фактів про сервер Oracle, корисних і цікавих розробнику. Своєю появою він зобов’язаний в першу чергу компанії “МВ” – протягом деякого часу я читав там курс основ програмування для Oracle, і в ході підготовки до нього з’ясував, що кілька тверджень, які я вважав очевидними фактами, насправді не тільки не очевидні, але навіть і не факти. В результаті я вирішив зібрати в одному місці те, що випадає з поля зору при побіжному знайомстві, але може привести до несподіваних і неприємних наслідків.
На жаль, “на замовлення” згадати все такі дрібниці – досить важко; зазвичай вони спливають у пам’яті в міру необхідності. Тому ця стаття ще дуже довго буде поповнюватися, і тому я прошу надсилати мені опису того, що я не знаю або забув згадати. І – працюючи, намагайтеся страхуватися від подібних тонкощів.
Твердження цієї статті перевірені в основному на сервері Oracle 9i. У той же час я не проводив перевірки, починаючи з якої версії справедливо те чи інше твердження.

Зміст.



Подвійна угруповання.


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


SQL> select owner, count(*)
from dba_objects
where owner like “%SYS%”
group by owner ;
OWNER COUNT(*)
—————————— ———-
SYS 13681
SYSTEM 332
WMSYS 131
SQL> select max (owner), max (count(*))
from dba_objects
where owner like “%SYS%”
group by owner ;
MAX(OWNER) MAX(COUNT(*))
—————————— ————-
WMSYS 13681

Результат вкладеного виклику обчислюється як якби зовнішня агрегатна функція застосовувалася до результату підзапиту, що містить внутрішню агрегатну функцію і вираз GROUP BY. Тобто, в прикладі сервер спочатку обчислює COUNT (*) у вибірці, згрупованої за схемами, а потім застосовує функцію MAX до результатів цієї вибірки, вже без додаткової угруповання.


Послідовності.


Несподіваною особливістю послідовностей є те, що атрибут NEXTVAL, який повертає чергове значення послідовності, обчислюється не при кожному використанні. Згаданий в SQL-операторі, скажімо, в SELECT, він обчислюється тільки один раз для кожної чергової рядки результату.


SQL> create sequence s1 ;
Sequence created
SQL> create sequence s2 increment by 2 ;
Sequence created
SQL> select s1.nextval, s1.currval, s1.nextval, s2.currval, s2.nextval, s2.nextval
from all_objects
where rownum <= 10;
NEXTVAL CURRVAL NEXTVAL CURRVAL NEXTVAL NEXTVAL
——- ——- ——- ——- ——- ——-
1 1 1 1 1 1
2 2 2 3 3 3
3 3 3 5 5 5
4 4 4 7 7 7
5 5 5 9 9 9
6 6 6 11 11 11
7 7 7 13 13 13
8 8 8 15 15 15
9 9 9 17 17 17
10 10 10 19 19 19

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


SQL> create table seq_values ( s11 number(3), s12 number(3),
s21 number(3), s22 number(3));
Table created
SQL> insert into seq_values
values ( s1.nextval, s1.nextval, s2.nextval, s2.nextval ) ;
1 row inserted
SQL> insert into seq_values
values ( s1.nextval, s1.nextval, s2.nextval, s2.nextval ) ;
1 row inserted
SQL> insert into seq_values
select s1.nextval, s1.nextval, s2.nextval, s2.nextval from dual ;
1 row inserted
SQL> select * from seq_values ;
S11 S12 S21 S22
—- —- —- —-
11 11 21 21
12 12 23 23
13 13 25 25
SQL> update seq_values set
s11 = s1.nextval, s12 = s1.nextval ;
3 rows updated
SQL> select * from seq_values ;
S11 S12 S21 S22
—- —- —- —-
14 14 21 21
15 15 23 23
16 16 25 25

Російські літери.


Oracle випередив більшість інших інструментальних засобів в сумнівній практиці використання національних символів в ідентифікаторах. Наслідки такого рішення видно на наступному прикладі:


SQL> create table dic$currencies ( currency_id integer, currency varchar2(3)) ;
Table created
SQL> select * from diс $ currencies;
ORA-00942: table or view does not exist

Причина помилки в тому, що одна з букв “с” у назві таблиці – російська. Сервер дозволяє використання символів національних алфавітів в ідентифікаторах без їх укладення в лапки; допускається створення названих по-русски таблиць, колонок, уявлень і інших елементів БД. На жаль, виправлення помилок (допущених при створенні об’єкта, особливо при проектуванні в CASE-засобах) призводить до подібних “Дивним” проблем; після декількох подібних випадків особисто я створив системний тригер, який забороняє використання російських букв в створюваних об’єктах.


Винятки в declare.


Блок операторів в Oracle складається з трьох секцій, обрамлених ключовими словами declare, begin, exception і end. Кілька несподіваний, хоча логічний факт – оператори обробки виключень в секції exception діють для коду, що виконується в секції begin але не діють для коду, що виконується в секції declare.


SQL> declare
s varchar2(1) := “12345” ;

begin dbms_output.put_line (“Все в порядку”);
exception
when others then dbms_output.put_line (“Помилка:” / / sqlerrm);
end ;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2


Цю особливість слід мати на увазі, застосовуючи в declare складну ініціалізацію – наприклад, виклик функцій, здатних привести до виключення. Таке виключення може бути оброблено тільки зовнішнім по відношенню до declare блоком – або в зухвалому підпрограмі, або в додатковому, осяжний блоці begin / exception / end.


Оптимізатор.


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


SQL> select count(*)
from ( select 1/0 from dual ) ;
COUNT(*)
———-
1
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF “DUAL”

У той же час не варто надмірно покладатися на цю здатність; такий результат виходить далеко не завжди:


SQL> declare
i integer ;
begin
i := 1/0 ; dbms_output.put_line (“Змінна i ніде більше не використовується”);
end ;
/
ORA-01476: divisor is equal to zero
ORA-06512: at line 4

Операції з NULL.


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


declare
i integer ;
procedure write ( expr varchar2 ) is
begin
dbms_output.put_line ( expr ) ;
end ;
procedure test ( expr varchar2, value varchar2 ) is begin
write ( rpad ( expr, 15 ) // ” = ” // coalesce ( value, “null” )) ;
end ;
procedure test ( expr varchar2, value boolean ) is
begin
test ( expr, case when value then “true”
when not value then “false”
when value is null then “null”
else “something strange” end ) ;
end ;

begin
write ( “” ) ;
test ( “1 + null”, 1 + null ) ;
test ( “2 * null”, 2 * null ) ;
test ( “3 // null”, 3 // null ) ;
write ( “” ) ;
test ( “true and null”, true and null ) ;
test ( “false and null”, false and null ) ;
test ( “true or null”, true or null ) ;
test ( “false or null”, false or null ) ;
write ( “” ) ;
test ( “(null = null)”, null = null ) ;
test ( “(null <> null)”, null <> null ) ;
test ( “not (null)”, not ( null )) ;
write ( “” ) ;
test ( “(true > false)”, true > false ) ;
test ( “(true > null)”, true > null ) ;
test ( “(true >= null)”, true >= null ) ;
write ( “” ) ;
test ( “length (null)”, length ( null )) ;
test ( “length (“”””)”, length ( “” )) ;
test ( “lpad (“”””, 0)”, lpad ( “”, 0 )) ;
write ( “” ) ;
select ( select 1 from dual where 1 = 0 ) into i from dual ;
test ( “i (select)”, i ) ; write ( “” ) ;
if null
then write ( “”if null then” works” ) ;
else write ( “”if null else” works” ) ;
end if ;
end ;


Цікавий момент в результатах цього скрипта – видно використання тризначної логіки при обчисленні логічних виразів. У той же час, мабуть, навряд чи варто будувати програми, розраховані на ті чи інші тонкощі роботи з null (особливо на горезвісний результат length (“”)); швидше варто максимально використовувати спеціальні операції – is null, is not null, coalesce.


Розмноження рядків в connect by.


Зазвичай ієрархічний запит можна вважати поєднанням фільтра (на потрапляння рядки в дерево) і впорядкування (у порядку вершин дерева). Однак, ця модель стає невірною, як тільки умова в connect by дозволяє прив’язати вузол відразу до декількох батьківським вузлів.


SQL> create table tree as
select 1 id, cast ( null as integer ) parent_id from dual
union all select 2 id, 1 parent_id from dual
union all select 3 id, 1 parent_id from dual ;
Table created
SQL> insert into tree select * from tree ;
3 rows inserted
SQL> select *
from tree
start with id = 1
connect by prior id = parent_id ;
ID PARENT_ID
— ———
1
2 1
3 1
2 1
3 1
1
2 1
3 1
2 1
3 1

10 rows selected


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


Поєднання where з connect by.


В ієрархічному запиті (start with .. connect by) побудова дерева виконується як частина пропозиції where. Порядок дій при цьому наступний (див. *):



Наслідки цього можна побачити на наступному прикладі:


SQL> create table tree as
select rownum id,
case when rownum = 1 then null
else round ( dbms_random.value ( 1, rownum – 1 )) end parent_id,
mod ( rownum, 3 ) modulo
from dba_objects
where rownum <= 10 ;
Table created
SQL> create table modulos as
select id, modulo from tree where modulo = 0 ;
Table created
SQL> select *
from tree
where modulo = 0
start with id = 1
connect by prior id = parent_id ;
ID PARENT_ID MODULO
— ——— ——
3 1 0
9 3 0
6 1 0
SQL> select t.*
from tree t, modulos m
where t.id = m.id
start with t.id = 1
connect by prior t.id = t.parent_id ;
ID PARENT_ID MODULO
— ——— ——
SQL>

Тут двічі виконується майже одне і те ж дія – але наявність з’єднання таблиць призводить до іншого результату, оскільки фільтрація виконується до виконання connect by, а не після.
В цілому, поєднання where з connect by – досить дивна думка, якщо подумати, виявиться, що якогось очевидно правильного, єдино розумного шляху дій в такій ситуації просто немає. Oracle вибрав шлях, який можна назвати “вгадування бажань”; шлях, який буде правильний для більшості припущених запитів.
Нарешті, варто звернути увагу на те, що поділ на “виконується з’єднання таблиць” і “виконуються обмеження, не пов’язані з виконанням таблиць” зрозуміло до тих пір, поки окремі вирази з’єднуються тільки зв’язками and. Розглянемо наступний приклад:


SQL> select t.*, m.*
from tree t, modulos m
where t.id = m.id or t.id = 1
start with t.id = 1
connect by prior t.id = t.parent_id ;

ID PARENT_ID MODULO ID MODULO
———- ———- ———- ——————–
1 1 3 0
1 1 6 0
1 1 9 0

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 CONNECT BY (WITHOUT FILTERING)
3 2 COUNT
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF “MODULOS”
6 4 TABLE ACCESS (FULL) OF “TREE”


Звідси видно, що Oracle діє таким же чином: виконує з’єднання таблиць до побудови дерева і фільтрує результат після побудови. Сам по собі, проте, результат досить несподіваний. Умова “t.id = 1 “, начебто прив’язане логічним” або “, насправді стало обов’язковим для всіх рядків результату – оскільки це єдина умова, що виконується після побудови дерева. Цей запит виявляється еквівалентним приблизно наступного:


select *
from ( select *
from ( select t.*, m.id m_id, m.modulo m_modulo
from tree t join modulos m on ( t.id = m.id )
union
select t.*, m.id m_id, m.modulo m_modulo
from tree t cross join modulos m
) t
start with t.id = 1
connect by prior t.id = t.parent_id ) t
where t.id = 1

З моєї точки зору, цей приклад показує, що при поєднанні where з connect by слід досконально розуміти логіку роботи сервера, але краще – подбати про явному, однозначному вираженні своїх думок за допомогою підзапитів. Другий з цих варіантів може бути і не більше читаємо, ніж перший – але набагато менш підступний.
Заодно, мабуть, це показує, чому в Oracle 8 заборонялося поєднання connect by і з’єднання таблиць в одному запиті.


Natural Join.


Мабуть, із загальних міркувань natural join слід назвати сумнівною операцією. Вона може бути зручна для адміністрування, для разових запитів у випадку, якщо адміністратор знає, що дві таблиці з’єднуються але не пам’ятає, як саме називається сполучна поле. Розробник ж навряд чи має право застосовувати цю операцію в програмному коді: будь-яка зміна структури бази може призвести до непомітного зміни фактично виконуваного запиту. Додавання до таблиці поля з назвою “name” або “date_start” виявиться цілком достатньо, щоб зруйнувати логіку працюючого програми і цілком ймовірно, нанести важкі ушкодження даними.
Але навіть адміністратор, який виконує natural join для разового запиту, повинен пам’ятати про неприємну особливості цієї операції: у випадку, якщо у таблиць не виявляється однойменних колонок, natural join просто виконується як cross join (картезіан), не реєструючи помилки.


SQL> select d.*, s.sid, s.username, s.program
from dual d natural join v$session s ;
DUMMY SID USERNAME PROGRAM
—– — —————————— ———–
X 1 ORACLE.EXE
X 2 ORACLE.EXE
X 3 ORACLE.EXE
X 4 ORACLE.EXE
X 5 ORACLE.EXE
X 6 ORACLE.EXE
X 7 ORACLE.EXE
X 8 TEST
X 9 TEST
X 11 TEST
X 12 TEST sqlplus.exe
X 13 TEST sqlplus.exe
X 14 TEST
X 15 TEST
X 16 TEST
X 17 TEST
16 rows selected

Таким чином, особливо якщо запит містить подальшу угруповання, легко непомітно отримати невірні дані (помилково зв’язавши не мають зв’язку таблиці). Через це навряд чи варто використовувати natural join взагалі; для економії роботи пальцями, мабуть, варто дочекатися операції на зразок foreign key join – її можна визначити досить надійно.


(*) У документації Oracle цей момент сформульований досить невиразно і допускає зовсім різні тлумачення. Викладене – результат деяких експериментів, а також впевненості, що текст документації можна зрозуміти і таким чином.

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


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

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

Ваш отзыв

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

*

*