NULL это пусто, то есть ничего!

NULL это не значение, это его отсутствие, а отсутствие чего – не известно, по этому null=null или null!=null – нам НЕИЗВЕСТНО, да и вообще, сравнения чего-то с отсутствием чего-то дает в результате НЕИЗВЕСТНО, for ex. 5=null

Итак, у нас кроме ДА, НЕТ, появляется третье состояние НЕ ЗНАЮ – нечёткая троичная логика

В следующие условиях логика оракла такова, что в AND при одном false второй проверять нет смысла, а при true нужно и получаем null, при OR ситуация противоположная. Порядок значения не имеет.

Почему же не работает not in (1, null):

select * from dual where 1 not in (select 2 from dual union select null from dual)

1 not in (2, null) => (1!=2) AND (1!=null) => true AND null => null;

Для сравнения с null используются следующие операторы
IS NULL и IS NOT NULL

decode и null
decode(A,B,C,D) в случае с этой функцией оракл поступит иначе чем все описанное выше – если A и B будут пустыми функция вернет C

Функции для работы с отсутствующими значениями:
NULLIF(A,B) = if A=B then null else A
COALESCE(A,B,C..) возвращаете первое по порядку не пустое значение
NVL(A,B) возвращает не пустое A, если же А пустое то возвращает B
NVL2(A,B,C) если А не пустое возвращает B, если пустое то C
LNNVL(условие) используется во where, возвращает TRUE если условие FALSE или UNKNOWN и FALSE если условие TRUE
NANVL(A,B) возвращает B если A не числовое (NaN)
SYS_OP_MAP_NONNULL(A) используется для сравнения с пустыми значениями, по правилу null=null is true –

NULL и пустая строка
length(”) вернет вам null, все потому что null и пустая строка эквивалентны в оракл (выглядят одинаково при записи в память)
Но в pl/sql ассоциативная коллекция позволяет хранить элементы с пустой строкой, а вот с null нет

Арифметика с null недопустима, вернее допустима но вернет ничего, по этому нужно nvl, а вот конкатенация в оракл работает.
Зато агрегаты игнорируют пустые значения

Переменные функций OUT, IN OUT, хинт NOCOPY

IN parameter is passed by reference
OUT and IN OUT parameters are passed by value, копируются и при выходе из функции возвращаются обратно (call by copy-restore)

NOCOPY – указывает oracle делать вызов OUT и IN OUT параметров по ссылке

Unhandled exceptions and OUT variables – значения параметров возвращаются в исходдные

Рекурсивные запросы в Oracle – with recursive – Common Table Expressions

WITH – рекурсивные запросы в oracle начиная с 11.2

WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual — исходное множество — одна строка
UNION ALL — символическое «объединение» строк
SELECT n + 1 AS n — рекурсия: добавок к предыдущему результату
FROM numbers — предыдущий результат в качестве источника данных
WHERE n < 5 -- если не ограничить, будет бесконечная рекурсия ) SELECT n FROM numbers -- основной запрос ; CYCLE node SET cyclemark TO ‘X’ DEFAULT ‘-‘
SEARCH DEPTH FIRST BY node DESC SET orderval

WITH RECURSIVE temp (n, fact) AS
(SELECT 0, 1 FROM dual — Initial Subquery
UNION ALL
SELECT n+1, (n+1)*fact FROM temp — Recursive Subquery
WHERE n < 9) SELECT * FROM temp;

Иерархические запросы в oracle – Hierarchical Queries

START WITH – начало цикла
CONNECT BY – условие (как while к примеру) при котором цикл будет работать
PRIOR – оператор который отсылает нас к предыдущей записи (итерации) цикла
LEVEL – псевдоситолбец (как и rownum) указывающий уровень отдаленности от начала цикла (так сказать инкремент)
ORDER SIBLINGS BY – указывает нам сортировать внутри уровня что бы не рушить иерархию
SYS_CONNECT_BY_PATH(title, ‘/’) – функция которая агрегирует нам все значения по столбцу title к началу иерархии
CONNECT_BY_ISLEAF – проверка является ли элемент листом
CONNECT_BY_ROOT – унарный оператор, который ссылается на корень иерархии
CONNECT BY NOCYCLE – что б не зацикливаться в петлях
CONNECT_BY_ISCYCLE – псевдостолбец который сообщает нам о петле

select substr(sys_connect_by_path(col1, ','), 2)
from (select rownum n, rownum col1 from dual
connect by rownum < 5)
where connect_by_isleaf = 1
connect by prior n = n - 1
start with n = 1

Уровни изоляции транзакций oracle

READ COMMITTED – уровень изоляции транзакции в Oracle по умолчанию. Воспроизводимость чтения на уровне запроса. Запрос видит только зафиксированные данные.

SERIALIZABLE – запрос видит данные которые были зафиксированы до момента начала транзакции. Воспроизводимость чтения на уровне всей транзакции. Возможна ORA-08177: can’t serialize access for this transaction – при попытке изменить данные в транзакции, которые в это время уже изменила и зафиксировала другая транзакция, либо же изменила и зафиксировала другие данные но в том же блоке (см. INITRANS)

READ ONLY – транзакция только на чтения, данные доступны на момент начала транзакции. Используется для построения отчетов, которые состоят из нескольких продолжительных по времени выполнения запросов.

High Water Mark (HWM) Oracle

В Oracle существует такое понятие как HWM – High Water Mark. Именно оно определяет самый последний блок который выделялся для записи данных в таблицу, то есть, является маркером максимального уровня заполнения таблицы. При выполнении FULL SCAN, Oracle будет считывать все блоки которые находятся до High Water Mark, именно по этому, select с пустой таблицы может выполняться долго. Так же происходит уменьшение индексов.

Существует несколько способов что бы сдвинуть High Water Mark до реально заполненных блоков:

сжатие данных – alter table shrink, в табличных пространствах должен быть включен ASSM

также можно шринковать LOBы и IOT:

DBMS_REDEFINITION – онлайн переопределение таблиц

последовательность действий при онлайновом переопределении таблиц:

  1. DBMS_REDEFINITION.can_redef_table
  2. DBMS_REDEFINITION.start_redef_table
  3. DBMS_REDEFINITION.copy_table_dependents
  4. DBMS_REDEFINITION.sync_interim_table
  5. DBMS_REDEFINITION.finish_redef_table

MOVE – перемещение таблицы

deallocate unused space

table coalesce

Также хорошо работает метод TRUNCATE TABLE который обнуляет HWM, пользуйтесь им только в случае отсутствия необходимости данных в таблице.