Oracle In Database Archiving Index

Question

В Oracle 12 есть фича позволяющая архивировать старые данные в той же таблице без создания лишних таблиц или БД, In Database Archiving. Его легко использовать но есть вопрос про перформанс и индексы, как работают индексы в особенности уникальные индексы, можно ли добавить такое значение в поле которое должно быть уникальным, к примеру уникальное поле NUMBER = 1 заархивировано, что будет если добавить такую же строку NUMBER = 1, будут ли учитываться заархивированные данные если да значит архивация и не сильно помогает в перформансе, а если нет то при выборке из всех данных(visibility = all) нарушается уникальность поля, как это все работает

Answer 1

О новой опции In Database Archiving в документации к версии 12.1:

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance.

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

To manage In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table.

Для достижения этого, в таблице будет создана скрытая символьная колонка (скрытые (hiden) колонки были также введены в этой версии) ORA_ARCHIVE_STATE со значением по умолчанию '0'=active, которой можно присвоить значение '1'=archived, чтобы сделать запись невидимой для запросов без явного указания этого в условии самого запроса.

Другими словами, фильтрация по состоянию архивирования записей, по сути является обычным предикатом, который будет добавлен неявно при выполнении запроса, и ожидать, что оптимизатор "магическим образом" обойдёт архивированные записи при чтении, не следует. На индексы архивирование записей никак не влияет.

Для подтверждения вышесказанного, посмотрим воспроизводимый пример:

create table t (x int unique, y int) row archival
/
insert /*+ append */ into t
    select rownum, rownum from dual connect by level<=1e5
/
100,000 rows inserted.
commit;
 
update t set ora_archive_state='1' where x<=99999
/
99,999 rows updated.
commit;

select t.*, ora_archive_state from t 
/
         X          Y ORA_ARCHIVE_STATE       
---------- ---------- ------------------------
    100000     100000 0                       

insert into t values (1, 1)
/
Error report -
ORA-00001: unique constraint (DB.SYS_C0011003) violated

Пока статистика не собрана, оптимизатор считает, что запрос вернёт 100K записей:

SQL> set autotrace trace
SQL> select t.* from t

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  1171K|    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   100K|  1171K|    69   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."ORA_ARCHIVE_STATE"='0')

После сбора статистики, она будет включать в себя архивированные записи:

SQL> exec dbms_stats.gather_table_stats (user, upper ('t'), cascade=>true)
SQL> select  num_rows from user_tables where table_name = 'T';

TABLE_NAME         NUM_ROWS
---------------- ----------
T                    100000

SQL> select table_name, index_name, num_rows from user_indexes where table_name = 'T';

TABLE_NAME       INDEX_NAME         NUM_ROWS
---------------- ---------------- ----------
T                SYS_C0011003         100000

SQL> select column_name, num_distinct, low_value, high_value from dba_tab_col_statistics
where table_name = 'T';

COLUMN_NAME       NUM_DISTINCT LOW_VALUE  HIGH_VALUE
----------------- ------------ ---------- ----------
ORA_ARCHIVE_STATE            2 30         31

Теперь оптимизатор знает, что запрос вернёт только одну запись, но это не делает выборку эффективней - по прежнему полное сканирование таблицы:

SQL> select t.* from t
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    12 |    69   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."ORA_ARCHIVE_STATE"='0')

БЛОГ НА HUSL

Oracle In Database Archiving Index

Ответы (1 шт):