Лекция 5: Выборка данных. Общее построение предложения SELECT и фразы FROM и WHERE

Вычисление составного логического выражения

При использовании цепочек из AND и OR логические выражения вычисляются не обязательно полностью, а до установления ясности, каким будет результат независимо от дальнейшего вычисления подвыражений. Иногда это возможно. Oracle называет это short-circuit evaluation, или "короткое вычисление" выражений. Цепочки вычисляются обыкновенно справа налево (AND) или слева направо (OR). Однако на деле не все так прямолинейно, и на эту технику может наложиться выявление тривиальных условных подвыражений, выполняемое на подготовительной фазе разбора. Все это вместе способствует затаенным ошибкам, углядеть появление которых при программировании запросов не всегда просто. Сравните две пары запросов:

SQL> SELECT 'ok' FROM dual WHERE 1 = 1 / 0 OR 1 = 1;
'O
--
ok
SQL> SELECT 'ok' FROM dual WHERE 1 = 1 OR 1 = 1 / 0;
'O
--
ok
SQL> SELECT 'ok' FROM dual WHERE 1 = ( SELECT 1 FROM dual ) OR 1 = 1 / 0;
'O
--
ok
SQL> SELECT 'ok' FROM dual WHERE 1 = 1 / 0 OR 1 = ( SELECT 1 FROM dual );
SELECT 'ok' FROM dual WHERE 1 = 1 / 0 OR 1 = ( SELECT 1 FROM dual )
                                 *
ERROR at line 1:
ORA-01476: divisor is equal to zero 

Очевидно, оптимизатор, разбирая условное выражение, сначала определяет тривиальные подвыражения чисто аналитически (еще не приступая к фактической оценке). Хотя и не часто, но таковые бывают. В нашем случае это 1 = 1. Обнаружив в структуре логического выражения цепочку из AND или OR, оптимизатор чисто формально проверит, достаточно ли выявленных значений тривиальных подвыражений для вынесения окончательного решения по поводу результата. И только если недостаточно, начинается фактическое оценивание подвыражений. При прочих равных порядок оценивания — слева направо или справа налево, но на деле в него могут дополнительно вмешаться: более раннее вычисление подзапросов; обращения к встроеным функциям, для которых СУБД известна стоимость вычислений; имеющаяся для таблиц — объектов доступа статистика хранения; до версии 10 — подсказка ORDERED_PREDICATES оптимизатору. Вот несколько поясняющих примеров:

SELECT 'ok' FROM dual WHERE 1 = 1 / 0 OR 1 = TRUNC ( 1 );
-- ok
SELECT 'ok' FROM dual WHERE 1 = TRUNC ( 1 ) OR 1 = 1 / 0;
-- ok
SELECT 'ok' FROM dept WHERE 1 = 1 / 0 OR LENGTH ( loc ) = LENGTH ( loc );
-- ok
SELECT 'ok' FROM dept WHERE LENGTH ( loc ) = LENGTH ( loc ) OR 1 = 1 / 0;
-- ok 
SELECT 'ok' FROM dept WHERE 1 = 1 / 0 OR 1 = LENGTH ( loc ) / LENGTH ( loc );
-- ORA-01476: divisor is equal to zero
SELECT 'ok' FROM dept WHERE 1 = LENGTH ( loc ) / LENGTH ( loc ) OR 1 = 1 / 0;
-- ok

Такая техника оценки логического выражения способна экономить вычисления. Этим обстоятельством может пользоваться программист, размещая наиболее достоверные или легковычисляемые подвыражения в соответствующем краю цепочки (особенно когда в подвыражениях встречаются обращения к функциям пользователя и СУБД вынуждена применять стандартный порядок). Об оборотной стороне уже упоминалось: такая техника вычислений снижает предсказуемость содержательной корректности общего выражения при построении последнего программистом.

Условный оператор IS

Типы данных в Oracle и в стандарте SQL достаточно разнообразны, так что традиционный оператор сравнения на равенство = не всегда в состоянии их адекватно обслуживать. Для сравнения на равенство в ряде нестандартных случаев используется особый оператор IS.

Наиболее распространенное его употребление — при проверке отсутствия значения-результата в выражении.

Сравнение IS NULL

Рассмотрим запрос: "Выдать сотрудников, не имеющих комиссионного вознаграждения, и их оклады". Следующие две попытки очевидно показывают ошибочность формулировки условного выражения со сравнением в конкретном случае:

SQL> SELECT ename, sal FROM emp WHERE comm = NULL;
no rows selected
SQL> SELECT ename, sal FROM emp WHERE comm <> NULL;
no rows selected

Но этого и следовало ожидать: общее правило SQL гласит, что обычное сравнение значений (в том числе операциями = и <>) даст NULL, если один из операндов NULL. Логика в том, что при сравнении "неизвестно чего" и результат "неизвестно какой". Приводившийся выше алгоритм работы фразы WHERE сообщает, что если условное выражение оценивается как NULL, строка в дальнейшую обработку не отбирается. По этой причине сравнения = NULL и <> NULL во фразе WHERE (равно как во всех других допустимых местах) бессмысленны, так как всегда приведут к пустому результату.

Для выявления наличия или отсутствия значений используются особые операторы IS NULL и IS NOT NULL:

SQL> SELECT ename, sal FROM emp WHERE comm IS NULL;
ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

Выражение

  E1 IS NOT NULL

равносильно

  NOT (E1 IS NULL)

Особенности проверки на отсутствие значения для типа VARCHAR2

В противоречие стандарту ANSI/ISO Oracle считает строку без символов отсутствующей строкой типа VARCHAR2:

SQL> VARIABLE nochars VARCHAR2 ( 10 )
SQL> SELECT 'ok' FROM dual WHERE :nochars IS NULL;
'O
--
ok
SQL> SELECT 'ok' FROM dual WHERE :nochars = '';
no rows selected

Для типа CLOB различие между пустой строкой и строкой без символов, как и в стандарте, проводится, однако сам он устроен сложнее. По стандарту для типов LOB существует локатор (который хранится вместе с другими полями строки в БД) и отдельно хранимый массив байтов. В условных выражениях SQL типы LOB допускают сравнения как раз только операторами IS NULL/IS NOT NULL. Прочие сравнения устраиваются посредством функций из пакета DBMS_LOB. В PL/SQL таких ограничений на условные выражения с типами LOB нет.

Сравнения для числовых данных BINARY_FLOAT и BINARY_DOUBLE

Стандарт IEEE 754 предписывает для данных этих типов возможность специальных значений Not a Number и +/- Infinity. Примеры сравнений в Oracle:

SELECT 'ok' FROM dual WHERE 123f IS NOT NAN;
SELECT 'ok' FROM dual WHERE TO_BINARY_DOUBLE ( '-INF' ) IS INFINITE;
SELECT 'ok' FROM dual WHERE BINARY_FLOAT_INFINITY - 1 IS INFINITE;

Сравнения для объектных данных

Оператор IS используется также для составления условных выражений с участием объектных данных Oracle. Вот примеры, как это могло бы выглядеть в некоторой гипотетической базе данных:

SELECT * FROM checkpoint WHERE person IS OF ( employee_type );
-- Отбор объектов типа EMPLOYEE_TYPE
SELECT dname FROM dept WHERE addr IS DANGLING;
-- Отбор строк с ссылками на более не существующие в БД объекты
Для "вложенных таблиц" (элемент объектных возможностей Oracle) сравнения могли бы выглядеть так:
SELECT model_type FROM colour_models WHERE colours IS EMPTY;
-- Названия цветовых моделей с пустым множеством их элементов
SELECT model_type FROM colour_models WHERE colours IS A SET;
-- Названия цветовых моделей с отсутствием повторений среди их элементов