Два кейса для ora_rowscn в Oracle 11g

В СУБД Oracle системный номер изменения (SCN, system change number) есть непрерывно возрастающее число. Каждая транзакция, и даже каждое чтение этого номера из БД увеличивает его:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 7211772580

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 7211772583

Начиная с версии 10 в таблицах БД Oracle появился псевдостолбец ora_rowscn. Этот псевдостолбец содержит SCN, связанный с последним изменением соответствующей строки. Посмотрим на него поближе.

Создадим таблицу locations, заполним ее данными и выполним запрос со столбцом ora_rowscn:

SQL> create table locations (
  2      id number primary key,
  3      parent number references locations (id) on delete cascade,
  4      name varchar2(50) not null
  5  );
Table created

SQL> 
SQL> insert into locations values (1, null, 'Земля');
1 row inserted
SQL> insert into locations values (2, 1, 'Азия');
1 row inserted
SQL> insert into locations values (3, 2, 'Владивосток');
1 row inserted
SQL> insert into locations values (4, 2, 'Саппоро');
1 row inserted
SQL> insert into locations values (5, 2, 'Стамбул');
1 row inserted
SQL> insert into locations values (6, 1, 'Европа');
1 row inserted
SQL> insert into locations values (7, 6, 'Реймс');
1 row inserted
SQL> insert into locations values (8, 6, 'Санкт-Петербург');
1 row inserted
SQL> commit;
Commit complete

SQL> select id, ora_rowscn from locations;
        ID ORA_ROWSCN
---------- ----------
         1 7211772620
         2 7211772620
         3 7211772620
         4 7211772620
         5 7211772620
         6 7211772620
         7 7211772620
         8 7211772620
8 rows selected

Как видим, SCN одинаков для всех строк таблицы, что неудивительно: все строки были добавлены в рамках одной транзакции.

Изменим одну строку в отдельной транзакции и повторим запрос:

SQL> update locations set name = upper(name) where id = 3;
1 row updated

SQL> commit;
Commit complete

SQL> select id, ora_rowscn from locations;
        ID ORA_ROWSCN
---------- ----------
         1 7211772627
         2 7211772627
         3 7211772627
         4 7211772627
         5 7211772627
         6 7211772627
         7 7211772627
         8 7211772627
8 rows selected

Изменилось значение ora_rowscn для всех строк. Почему это произошло?

По умолчанию значение SCN, отображаемое в псевдостолбце ora_rowscn, хранится на уровне блока данных (СУБД Oracle пишет и читает данные блоками). А один блок данных в нашем случае вмещает все строки таблицы. Получается, что обновив одну строку, мы "пометили" новым SCN все строки блока, в котором находится измененная строка.

Для того, чтобы SCN хранился индивидуально для каждой строки, таблица должна быть создана с опцией rowdependencies. У нашей таблицы свойство rowdependencies (по умолчанию) отключено:

SQL> select table_name, dependencies
  2  from user_tables
  3  where table_name = 'LOCATIONS';

TABLE_NAME                     DEPENDENCIES
------------------------------ ------------
LOCATIONS                      DISABLED

К сожалению, с помощью alter table данное свойство включить нельзя. Нужно пересоздать таблицу:

SQL> drop table locations;
Table dropped

SQL> create table locations (
  2      id number primary key,
  3      parent number references locations (id) on delete cascade,
  4      name varchar2(50) not null
  5  ) rowdependencies;
Table created

SQL> insert into locations values (1, null, 'Земля');
1 row inserted
SQL> insert into locations values (2, 1, 'Азия');
1 row inserted
SQL> insert into locations values (3, 2, 'Владивосток');
1 row inserted
SQL> insert into locations values (4, 2, 'Саппоро');
1 row inserted
SQL> insert into locations values (5, 2, 'Стамбул');
1 row inserted
SQL> insert into locations values (6, 1, 'Европа');
1 row inserted
SQL> insert into locations values (7, 6, 'Реймс');
1 row inserted
SQL> insert into locations values (8, 6, 'Санкт-Петербург');
1 row inserted
SQL> commit;
Commit complete

SQL> select id, ora_rowscn from locations;
        ID ORA_ROWSCN
---------- ----------
         1 7211772643
         2 7211772643
         3 7211772643
         4 7211772643
         5 7211772643
         6 7211772643
         7 7211772643
         8 7211772643
8 rows selected

SQL> select table_name, dependencies
  2  from user_tables
  3  where table_name = 'LOCATIONS';

TABLE_NAME                     DEPENDENCIES
------------------------------ ------------
LOCATIONS                      ENABLED

Теперь в таблице должны храниться SCN для каждой строки индивидуально. Проверим это:

SQL> update locations set name = upper(name) where id = 3;
1 row updated

SQL> commit;
Commit complete

SQL> select id, ora_rowscn from locations;
        ID ORA_ROWSCN
---------- ----------
         1 7211772643
         2 7211772643
         3 7211772645
         4 7211772643
         5 7211772643
         6 7211772643
         7 7211772643
         8 7211772643
8 rows selected

Хранение SCN для каждой строки требует дополнительно 6 байт на строку.

Для перевода существующей таблицы в режим rowdependencies с сохранением ее данных и зависимостей и без приостановки работы с ней, можно воспользоваться пакетом dbms_redefinition (см., например, статью Online Table Redefinition (DBMS_REDEFINITION) Enhancements in Oracle Database 11g Release 1).

А теперь рассмотрим два случая, когда псевдостолбец ora_rowscn оказывается полезен.

Кейс 1. Оптимистическая блокировка

Оптимистическая блокировка - это когда мы оптимистично полагаем, что между моментом, когда мы прочитали данные из БД, и более поздним моментом, когда мы станем сохранять измененные данные в БД, эти данные не были изменены в БД никем другим. А как проверить, что данные в БД не были изменены? Сравнив текущие данные в БД с ранее считанными - еще не измененными - данными.

Допустим, мы прочитали из таблицы locations значение столбца name строки с id = 8:

select name into :loc_name from locations where id = 8;

и отобразили его в пользовательском интерфейсе для редактирования. После редактирования названия ("Санкт-Петербург" исправили на "Санкт-Петербургъ"), сохраняем изменения:

update locations set
    name = 'Санкт-Петербургъ'
where id = 8
    and name = :loc_name
;

Последнее условие name = :loc_name гарантирует нам, что строка обновится только в случае, если считанное ранее значение name не изменилось в БД за то время, что нам понадобилось на его редактирование. Если же обновлено 0 строк, то изменение не сохранено и пользователь должен получить сообщение об ошибке.

Что, если для редактирования было считано не одно, а 50 полей? В таком случае нужно сравнивать все 50 полей! Альтернатива этому - иметь в таблице столбец с версией/номером изменения, который должен обновляться каждым предложением update для этой таблицы. Тогда этот столбец нужно считывать вместе с другими перед началом редактирования и сравнивать считанное значение с текущим при сохранении изменений:

-- читаем
select name, ..., version
into :loc_name, ..., :loc_version
from locations 
where id = 8
;

-- ...

-- сохраняем изменения
update locations set
    name = 'Санкт-Петербургъ',
    ...
    version = version + 1
where id = 8
    and version = :loc_version
;

Чем может быть полезен ora_rowscn в этой ситуации? Значение ora_rowscn как раз и есть непрерывно возрастающий номер изменения, для которого не нужно предусматривать отдельный столбец в таблице.

При условии, что для таблицы включено свойство rowdependencies, перепишем предыдущие запросы так:

-- читаем
select name, ..., ora_rowscn
into :loc_name, ..., :loc_rowscn 
from locations 
where id = 8
;

-- ...

-- сохраняем изменения
update locations set
    name = 'Санкт-Петербургъ',
    ...
where id = 8
    and ora_rowscn = :loc_rowscn
;

Если обновлено 0 строк, это значит, что строка была изменена в другом сеансе, и пользователь должен получить сообщение об ошибке.

Кейс 2. Получение изменений за период

Нередко возникает задача получить все строки таблицы, добавленные или измененные за последний час (6 часов, неделю, ...) или после некоторого момента в прошлом. Это нужно, когда сделанные изменения должны время от времени обрабатываться, например, передаваться в другую систему.

Такую задачу иногда решают добавлением столбца для сохранения в нем даты-времени последнего изменения строки. При этом все изменения таблицы с помощью insert и update должны устанавливать в этом поле текущие дату-время. Нужно либо строго придерживаться этого правила при написании insert и update, либо повесить на таблицу триггер before insert or update, который гарантирует обновление этого поля.

Если нельзя добавить столбец в таблицу (приложение сломается?), то можно повесить на таблицу триггер, который будет регистрировать все изменения строк в другой таблице. Например, так:

create table locations_log (
    id number primary key,
    last_when timestamp with time zone
);

create or replace trigger locations_aiur_trg
after insert or update on locations
for each row
begin
    merge into locations_log tgt
    using (select :new.id id from dual) src
    on (tgt.id = src.id)
    when matched then
        update set last_when = systimestamp
    when not matched then
        insert (id, last_when) values (:new.id, systimestamp)
    ;
end;
/

Благодаря merge, многочисленные изменения одной и той же строки locations будут отражены только в одной строке locations_log.

Теперь, чтобы периодически (или просто время от времени) обрабатывать только измененные строки, нужно запоминать дату-время последней обработки и при следующей обработке выбирать строки, измененные позднее. Для случая, когда таблица locations содержит столбец last_when, это выглядит примерно так:

declare
    l_curr_when timestamp with time zone := systimestamp;
    -- для 1-го раза предыдущее время обработки равно текущему
    l_prev_when timestamp with time zone := 
        get_prev_when(p_default => l_curr_when);
begin
    for r in (
        select * 
        from locations 
        where last_when >= l_prev_when
            and last_when < l_curr_when
    ) loop
        -- здесь обрабатываем изменения
        null;
    end loop;
    -- запоминаем время, относительно которого
    -- более ранние изменения уже обработаны
    save_prev_when(l_curr_when);
end;

Функция get_prev_when возвращает метку времени, сохраненную процедурой save_prev_when или, если сохраненного значения нет, то метку времени, полученную через параметр p_default. (Код этих функции и процедуры не привожу, чтобы не загромождать пример. Все просто: save_prev_when сохраняет значение в некоторой служебной табличке, а get_prev_when выбирает значение оттуда.)

При первом выполнении приведенный код обработает 0 строк, а при втором и последующих будет обрабатывать строки, измененные в интервале между временем предыдущего выполнения (l_prev_when) и текущим временем (l_curr_when).

Для случая, когда изменения в таблице locations регистрируются в locations_log, обработка выполняется так же. Изменится только запрос, выбирающий строки для обработки:

select l.* 
from locations l, locations_log ll 
where l.id = ll.id 
    and ll.last_when >= l_prev_when 
    and ll.last_when < l_curr_when

Что нам может дать ora_rowscn в такой ситуации? Значения SCN обладают тем же свойством, что и значения, возвращаемые systimestamp (или sysdate): они возрастают со временем. Каждая последующая версия строки таблицы имеет большее значение ora_rowscn, чем предыдущая.

Отсюда, код обработки изменений можно переписать так:

declare
    l_curr_scn number;
    l_prev_scn number;
begin
    -- текущий SCN, который больше не повторится
    select current_scn into l_curr_scn from v$database;
    -- для 1-го раза предыдущий SCN равен текущему
    l_prev_scn := get_prev_scn(p_default => l_curr_scn);
    for r in (
        select * 
        from locations 
        where ora_rowscn between l_prev_scn and l_curr_scn
    ) loop
        -- здесь обрабатываем изменения
        null;
    end loop;
    -- запоминаем SCN, по который изменения уже обработаны
    save_prev_scn(l_curr_scn);
end;

Если воспользоваться ora_rowscn, то не нужны ни дополнительная таблица для регистрации изменений, ни даже столбец с меткой времени последнего изменения.

Псевдостолбец ora_rowscn предоставляет нам готовый механизм для выбора строк, измененных после того, как мы запомнили некоторый более ранний SCN. При условии, что для таблицы включено свойство rowdependencies.

В заключение, удалю таблицы, с которыми экспериментировал:

SQL> drop table locations;
Table dropped
SQL> drop table locations_log;
Table dropped