Oracle 12c: in database archiving

Julian Dontcheff hat dieser Tage in seinem Blog eine ganze Reihe von Links zum Thema in-database archiving zusammengetragen und ein paar Bemerkungen zu den Performance-Aspekten dieses neuen Features gemacht - und mich damit auf die Idee gebracht, das Verhalten dieser Option etwas genauer zu betrachten. Ausgangspunkt war dabei die folgende Definition aus der Dokumentation:

In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

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

Was genau bedeutet dabei "without compromising application performance"? Dazu ein kleiner Test mit 12.1.0.1 auf meinem Windows-PC. Ich lege eine Tabelle mit zwei Spalten und der Option row archival an, die dafür sorgt, dass man Datensätze vor dem Optimizer verbergen kann:

drop table t1;

create table t1 (
    id number
  , padding varchar2(20)
) row archival;

insert /*+ append */ into t1(id, padding)
select rownum id, lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

commit;

select file_id, block_id, blocks
  from dba_extents
where segment_name = 'T1';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       1096          8
      9       1104          8
      9       1112          8
      9       1120          8
      9       1128          8
      9       1136          8
      9       1144          8

alter system dump datafile 9 block 1104;

Ein Blick in den block dump zeigt, dass die row archival Option intern über die Anlage einer zusätzlichen Spalte realisiert ist:

data_block_dump,data header at 0x777007c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x0777007c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Vor die Spalten Id und Padding wird somit eine interne Spalte gestellt. Besonders überraschend ist diese Beobachtung eher nicht, wenn man bedenkt, dass die Archivierungs-Markierung eines Datensatzes als Update durchgeführt wird:

update t1 set ORA_ARCHIVE_STATE = 2 where id <= 5000;
commit;

Das Update setzt die Hälfte der Datensätze in der Tabelle auf einen ORA_ARCHIVE_STATE <> 0 und macht sie damit für den Optimizer unsichtbar. Eine einfache Query ohne Einschränkung bringt anschließend eine sinnvolle cardinality-Schätzung:

explain plan for
select *
  from t1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   131K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 |   131K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

Ganz offensichtlich ist hier also keine schwarze Magie im Spiel, sondern nur eine virtuelle Spalte, die als default-Filterkriterium eingesetzt wird. Die Frage, die sich mir im Anschluss stellt, ist: gibt es jenseits der simplen Handhabung (und wahrscheinlich des Verhaltens bei komplexeren Operationen) einen prinzipiellen Unterschied zwischen dieser neuen Option und der Verwendung einer expliziten sichtbaren Spalte zur Kennzeichnung historischer Daten? Dazu das komplementäre Beispiel:

drop table t2;

create table t2 (
    archive_flag number
  , id number
  , padding varchar2(20)
) ;

insert /*+ append */ into t2(archive_flag, id, padding)
select 0 archive_flag
     , rownum id
     , lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

select file_id, block_id, blocks
  from dba_extents
 where segment_name = 'T2';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       3904          8
      9       3912          8
      9       3920          8
      9       3928          8
      9       3936          8
      9       3944          8
      9       3952          8

alter system dump datafile 9 block 3912;

Der zugehörige block dump scheint mir von der Version des impliziten Archivierungsverfahren kaum unterscheidbar zu sein: die Satzanzahl und die Angaben zum verwendeten Speicherplatz sind identisch und auch bei der Beschreibung der Datensätze gibt es nur marginale Differenzen (die sich daraus ergeben, dass ich mit einem NUMBER-Wert gearbeitet habe, statt mit VARCHAR2(4000) als was ORA_ARCHIVE_STATE tatsächlich definiert ist).

data_block_dump,data header at 0x1545807c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x1545807c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Natürlich muss man in diesem Fall das Flag bei Zugriffen explizit angeben, aber davon abgesehen, scheint der Unterschied bei den internen Strategien zunächst eher marginal zu sein:

update t2 set archive_flag = 2 where id <= 5000;
commit;

exec dbms_stats.gather_table_stats(user, 'T2')

explain plan for
select *
  from t2
 where archive_flag = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   136K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |  5000 |   136K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ARCHIVE_FLAG"=0)

Insofern würde ich auf Anhieb erst einmal behaupten, dass das Feature sicherlich administrative Vereinfachungen mit sich bringt - aber große Geheimnisse scheinen mir hinsichtlich der Performance-Eigenschaften der Operation eher nicht zu bestehen. Ich muss allerdings zugeben, dass ich über die elaborierteren Effekte des Features ("updates to archived data can be deferred during application upgrades to improve the performance of upgrades") und die vorgeschlagenen Komprimierungsverfahren noch nicht nachgedacht habe.