Александр Просторов

Подробности Oracle

Источник: http://www.softwarer.ru/Oracle/oracle_facts.html , Версия 1.02 от 23.01.2005

Этот текст задуман как сборник неочевидных фактов о сервере 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 этот момент сформулирован весьма невнятно и допускает совершенно разные толкования. Изложенное - результат некоторых экспериментов, а также уверенности, что текст документации можно понять и таким образом.