Этот текст задуман как сборник неочевидных фактов о сервере Oracle, полезных и интересных разработчику. Своим появлением он обязан в первую очередь компании "МВ" - в течение некоторого времени я читал там курс основ программирования для Oracle, и в ходе подготовки к нему выяснил, что несколько утверждений, которые я полагал очевидными фактами, на самом деле не только не очевидные, но даже и не факты. В результате я решил собрать в одном месте то, что выпадает из поля зрения при беглом знакомстве, но может привести к неожиданным и неприятным последствиям.
К сожалению, "по заказу" вспомнить все такие мелочи - довольно тяжело; обычно они всплывают в памяти по мере необходимости. Поэтому эта статья еще очень долго будет пополняться, и поэтому я прошу присылать мне описания того, что я не знаю или забыл упомянуть. И - работая, старайтесь страховаться от подобных тонкостей.
Утверждения этой статьи проверены в основном на сервере Oracle 9i. В то же время я не проводил проверки, начиная с какой версии справедливо то или иное утверждение.
Оглавление.
- Двойная группировка
- Последовательности
- Русские буквы
- Исключения в declare
- Оптимизатор
- Операции с NULL
- Размножение строк в connect by
- Сочетание where с connect by
- Natural Join
Двойная группировка.
Как и следует ожидать, 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. Порядок действий при этом следующий (см. *):
- Выполняются указанные в where соединения таблиц
- Полученная выборка обрабатывается согласно условиям 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 этот момент сформулирован весьма невнятно и допускает совершенно разные толкования. Изложенное - результат некоторых экспериментов, а также уверенности, что текст документации можно понять и таким образом.