In-Database Archiving: Konzepte, Setup und Einsatz

Datenbank Community - März 2016

von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Häufig gibt es die Anforderung bestimmte Zeilen einer Tabelle für die Anwendungen "unsichtbar" zu machen. Die Realisierung kann über verschiedenste Methoden wie zum Beispiel das Anlegen von Views oder auch den Einsatz von Virtual Private Database erfolgen. In dem Zusammenhang sollte man auch über das Feature In-Database Archiving nachdenken, das ab Oracle Database 12c in allen Editionen sofort nach der Installation zur Verfügung steht. Die Funktionsweise ist mit einem Satz erklärt: Mit In-Database Archiving lassen sich Zeilen einer Tabelle archivieren, in dem gewisse Zeilen ausgeblendet werden.

Möchte man In-Database Archiving verwenden, stellen sich zuerst einige grundsätzliche Fragen wie z.B.: Wie funktioniert das Setup? Wird weiterhin nach der "Archivierung" Speicherplatz verbraucht? Erfolgt eine automatische Archivierung? Kann man die "archivierten" Zeilen sichtbar machen? Muss eine Applikation für die Verwendung angepaßt werden?

Das Setup

Das Konfiguration ist einfach. Die neuen Schlüsselworte ROW ARCHIVAL am Ende eines CREATE/ALTER TABLE Kommandos sorgen dafür, dass das Feature In-Database Archiving eingeschaltet wird. Dazu wird unter anderem die "invisible" Spalte ORA_ARCHIVE_STATE zu der Tabelle hinzugefügt und mit dem Wert 0 belegt. Der Wert 0 steht dabei für aktive Daten. Ändert man diesen Wert, wird die entsprechende Zeile inaktiv.
Zur Erinnerung: Ab Oracle Database 12c können nicht nur Indizes sondern auch Spalten die Eigenschaft "invisible" erhalten. Sie sorgt dafür, dass die Spalten bei der Verwendung von gewissen Zugriffen über SELECT, DESCRIBE oder %ROWTYPE (in PL/SQL) unsichtbar (engl: invisible) sind.

Im ersten Beispiel nehmen wir die nicht partitionierte Tabelle SALESARCH, die eine Kopie der Tabelle SALES darstellt. Sie besitzt 7 Spalten und hat 918843 Zeilen.

SQL> drop table salesarch purge;

Table dropped.

SQL> create table salesarch as select * from sales;

Table created.

SQL> col data_type format a20
SQL> col column_name format a20 
SQL> select column_name, data_type, hidden_column, char_length
     from user_tab_cols where table_name='SALESARCH';

COLUMN_NAME          DATA_TYPE            HID CHAR_LENGTH
-------------------- -------------------- --- -----------
AMOUNT_SOLD          NUMBER               NO            0
QUANTITY_SOLD        NUMBER               NO            0
PROMO_ID             NUMBER               NO            0
CHANNEL_ID           NUMBER               NO            0
TIME_ID              DATE                 NO            0
CUST_ID              NUMBER               NO            0
PROD_ID              NUMBER               NO            0

SQL> select count(*) from salesarch;

  COUNT(*)
----------
    918843

Später werden wir die Zeilen archivieren, in denen die Werte von AMOUNT_SOLD größer als 100 sind. Offensichtlich handelt es sich dabei um 135669 Zeilen.

SQL> select count(*) from salesarch where amount_sold>100;

  COUNT(*)
----------
    135669

Jetzt aktivieren wir In-Database Archiving für die Tabelle SALEASARCH mit dem Kommando ALTER TABLE und überprüfen erneut die Spalten.

SQL> alter table salesarch row archival;

SQL> select column_name, data_type, hidden_column, char_length, user_generated
     from user_tab_cols where table_name ='SALESARCH';

COLUMN_NAME          DATA_TYPE            HID CHAR_LENGTH USE
-------------------- -------------------- --- ----------- ---
ORA_ARCHIVE_STATE    VARCHAR2             YES        4000 NO
SYS_NC00008$         RAW                  YES           0 NO
AMOUNT_SOLD          NUMBER               NO            0 YES
QUANTITY_SOLD        NUMBER               NO            0 YES
PROMO_ID             NUMBER               NO            0 YES
CHANNEL_ID           NUMBER               NO            0 YES
TIME_ID              DATE                 NO            0 YES
CUST_ID              NUMBER               NO            0 YES
PROD_ID              NUMBER               NO            0 YES


9 rows selected.

SQL> col ORA_ARCHIVE_STATE format a20
SQl> select distinct ora_archive_state from salesarch;

ORA_ARCHIVE_STATE    
------------------------
0

Wie man leicht erkennen kann, sind direkt nach dem Einschalten von In-Database Archiving zwei Spalten hinzugefügt worden - ORA_ARCHIVE_STATE und SYS_NC00008$. Die VARCHAR2 Spalte ORA_ARCHIVE_STATE hat den Wert 0; die RAW Spalte hingegen ist nicht belegt und wird nur für interne Zwecke verwendet. Die Spalte ORA_ARCHIVE_STATE hat die Funktion den Status der Zeilen anzugeben. 0 bedeutet aktiv, alle anderen Werte der Spalte repräsentieren den Zustand "archiviert". Zeilen mit der Eigenschaft "archiviert" sind standardmässig für die die Applikationen unsichtbar. In unserem Fall sind natürlich alle Zeilen (noch) aktiv, da wir bis jetzt keine Veränderung an der Spalte ORA_ARCHIVE_STATE vorgenommen haben,

Möchte man das ganze Verfahren wieder rückgängig machen, kann man die Syntax "alter table salesarch no row archival" verwenden. Die zusätzlichen Spalten sind danach wieder gelöscht.

Nun kennzeichnen wir einige Zeilen als unsichtbar, in dem wir ein UPDATE auf die Spalte ORA_ARCHIVE_STATE durchführen und den Wert ändern. Wie in der Abfrage auf USER_TAB_COLS zu erkennen ist, können bis zu 4000 Zeichen zur Kennzeichnung der Archivierung verwendet werden. Die Verwendung von einstelligen Zahlenwerten - aus Gründen der Platzersparnis - ist sicherlich sinnvoll. Eine Änderung auf den Wert 1 ist also völlig ausreichend.

SQL> update salesarch set ora_archive_state=1 where amount_sold>100;

135669 rows updated.

SQL> commit;

Commit complete.

Alternativ kann man die Funktion DBMS_ILM.ARCHIVESTATENAME() beim UPDATE der Spalte verwenden. Sie bildet einen beliebigen Wert, der ungleich 0 ist, auf der Wert 1 (archviert) ab; der Wert 0 bleibt erhalten. Das UPDATE Kommando würde dann folgendermaßen aussehen.

-- update salesarch set ora_archive_state=dbms_ilm.archivestatename('archiviert') where amount_sold>100;

SQL> select dbms_ilm.archivestatename('beliebig') from dual;

DBMS_ILM.ARCHIVESTATENAME('BELIEBIG')
--------------------------------------------------------------------------------
1

SQL> select dbms_ilm.archivestatename('0')  from dual;

DBMS_ILM.ARCHIVESTATENAME('0')
--------------------------------------------------------------------------------
0

Zugriffe

Im folgenden Abschnitt werden einige Zugriffe näher beleuchtet. Es ist sofort erkennbar, dass die archivierten Zeilen nicht mehr angezeigt werden. Keine Änderungen an den Applikationen ist dazu erforderlich.

SQL> desc salesarch;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> select * from salesarch where ora_archive_state=1;

no rows selected

SQL> select count(*) from salesarch;

  COUNT(*)
----------
    783174

SQL> select count(*) from salesarch where amount_sold>100;

 COUNT(*)
----------
         0

Um einen Einblick in die Implementierung zu erhalten, generieren wir für die letzte Abfrage den Ausführungsplan.

SQL> select * from table(dbms_xplan.display_cursor())

Plan hash value: 677531968

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  1585 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |    29 |            |          |
|*  2 |   TABLE ACCESS FULL| SALESARCH |  8704 |   246K|  1585  (10)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter((DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00008$",0)),
               NULL,NVL("SALESARCH"."ORA_ARCHIVE_STATE",'0'),
               '0', NVL("SALESARCH"."ORA_ARCHIVE_STATE",'0'),
               '1',"SALESARCH"."ORA_ARCHIVE_STATE")='0' 
                AND "AMOUNT_SOLD">100))

22 rows selected.

Ähnlich wie bei der Virtual Private Database (kurz VPD) Technologie, wird offensichtlich die Filterbedingung erweitert. Somit nutzt der Optimizer beim Filtern nicht nur die Spalte AMOUNT_SOLD sondern evaluiert auch die Inhalte der Spalte ORA_ARCHIVE_STATE. Geprüft wird auf die aktiven Zeilen - also den Wert 0, nachdem die ursprünglichen Werte von ORA_ARCHIVE_STATE mit der DECODE Funktion auf 0 oder 1 umgeschlüsselt worden sind.

Da man "invisible columns" auch indizieren kann, werden wir im nächste Beispiel einen Index erzeugen und die Abfrage erneut durchführen.

SQL> create bitmap index ora_index_sales on salesarch("ORA_ARCHIVE_STATE");

Index created.

SQL> select count(*) from salesarch where amount_sold>100;

  COUNT(*)
----------
         0

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  09gx25rmm5uyj, child number 0
-------------------------------------
select count(*) from salesarch where amount_sold>100

Plan hash value: 384658619

--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   882 (100)|          |
|   1 |  SORT AGGREGATE                      |                 |     1 |    29 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALESARCH       |     1 |    29 |   882 (1)  | 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |                 |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | ORA_INDEX_SALES |       |       |            |          |
--------------------------------------------------------------------------------------------------------

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

   2 - filter("AMOUNT_SOLD">100)
   4 - access("SALESARCH"."ORA_ARCHIVE_STATE"='0')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 2 Sql Plan Directives used for this statement

27 rows selected.

Wie im Falle von VPD lässt sich der Zugriff mit zusätzlichen Indizes ändern und tunen. Die gleiche Abfrage erfolgt nun mit einem Indexzugriff.

Wei funktioniert das Datenladen über CREATE TABLE AS SELECT (CTAS) oder Data Pump Export und Import? Die Eigenschaft In-Database Archiving wird beim CTAS nicht übertragen - auch unabhängig von den Sessioneinstellungen (siehe auch nächstes Kapitel). Folgendes einfache Beispiel demonstriert diese Eigenschaft. Es werden alle Zeilen mit der Eingenschaft AMOUNT_SOLD>100 in die Tabelle COPYSALES äbertragen.

SQL> create table copysales as select * from salesarch where amount_sold>100;

Table created.

SQL>  select count(*) from copysales;

  COUNT(*)
----------
    135669

Wegen eines Bugs in 12.1 (siehe auch MOS Note Doc ID 1999047.1) wird die Eigenschaft In-Database Archiving mit Data Pump noch nicht unterstützt. Ein möglicher Workaround wäre die Tabelle zuerst manuell mit der Syntax ROW ARCHIVAL anzulegen und den Import mit TABLE_EXISTS_ACTION=APPEND durchzuführen. Dabei geht allerdings die Information welche Zeilen archiviert sind verloren. Die Spalte ORA_ARCHIVE_STATE muss für die entsprechenden Zeilen geändert werden.

Sessioneinstellungen

Ein zusätzliches Feature erlaubt die archivierten Zeilen innerhalb einer Session wieder sichtbar zu machen. Dazu gibt es die neue Sessioneinstellung ROW ARCHIVAL VISIBILITY, die die Werte ALL und ACTIVE haben kann. Der Wert ACTIVE zeigt dabei nur die aktiven Zeilen an - dies ist auch der Standardwert. Der Wert ALL hingegen, macht alle Zeilen sichtbar. Wir zeigen nun das Verhalten unter Verwendung von ALL.

SQL>  ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> select count(*) from salesarch where amount_sold>100;

  COUNT(*)
----------
    135669

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  09gx25rmm5uyj, child number 1
-------------------------------------
select count(*) from salesarch where amount_sold>100

Plan hash value: 677531968

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  1570 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| SALESARCH |   870K|  4250K|  1570   (9)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("AMOUNT_SOLD">100)
19 rows selected.

Diese Sessioneinstellung zeigt natürlich nur im Zusammenhang mit dem Feature In-Database Archiving ihre Wirkung.

Archivierung und Partitionierung

Da die Daten nicht mehr verwendet werden, wäre es sinnvoll die entsprechenden Zeilen im komprimierten Format abzulegen. Realisieren kann man diese Anforderung beispielsweise mithilfe von Partitionierung. Dazu legen wir eine partitionierte Tabelle an, die als Partitionierungskriterium die Spalte ORA_ARCHIVE_STATE verwendet. Es werden zwei Partitionen verwendet: die Partition arch_stat_0 für die aktiven Daten und die Partition arch_stat_1 für die archivierten Daten. Der Tablespace ADVTEST erhält die Default Eigenschaft COMPRESS. Danach werden alle Tabellen und Partitionen, die in diesem Tablespace erzeugt werden, komprimiert. Das Ganze sieht dann folgendermassen aus.

-- alter tablespace advtest default compress;
-- oder ein CREATE TABLESPACE ....DEFAULT COMPRESS
SQL> select compress_for, status from user_tablespaces where tablespace_name='ADVTEST';

COMPRESS_FOR                   STATUS
------------------------------ ---------
BASIC                          ONLINE

SQL> CREATE TABLE sh.salesarch_part
     (  "PROD_ID" NUMBER NOT NULL ENABLE,
        "CUST_ID" NUMBER NOT NULL ENABLE,
        "TIME_ID" DATE NOT NULL ENABLE,
        "CHANNEL_ID" NUMBER NOT NULL ENABLE,
        "PROMO_ID" NUMBER NOT NULL ENABLE,
        "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
        "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE
     )
    PARTITION BY RANGE (ORA_ARCHIVE_STATE) 
  (
  PARTITION arch_stat_0 VALUES LESS THAN (1) tablespace users,
  PARTITION arch_stat_1 VALUES LESS THAN (2) tablespace advtest)
  row archival;

Im nächsten Schritt werden die Daten aus der Tabelle SALES geladen. Alle Zeilen liegen danach in der Partition arch_stat_0.

SQL> insert into salesarch_part select * from sales;

918843 rows created.

SQL> select count(*) from salesarch_part partition (arch_stat_0);

  COUNT(*)
----------
    918843

Anschließend werden die Daten mit dem gleichen Verfahren wie oben archiviert. Dazu muss zuerst ROW MOVEMENT eingeschaltet werden, sonst erhalten wir den folgenden Fehler:
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> alter table salesarch_part enable row movement;

Table altered.

SQL> update salesarch_part set ora_archive_state=1 where amount_sold>100;

135669 rows updated.

Bei der Überprüfung der Daten wird die Sessioneinstellung ROW ARCHIVAL VISIBILITY mit dem Wert ALL verwendet. Offensichtlich liegen die archivierten Daten nun in der Partiton arch_stat_1 und sind somit komprimiert.

SQL> alter session set ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> select count(*) from salesarch_part partition (arch_stat_0);

  COUNT(*)
----------
    783174

SQL> select count(*) from salesarch_part partition (arch_stat_1);

  COUNT(*)
----------
    135669

Denkbar ist auch eine Kombination mit dem ILM Feature Automatic Data Optimization. So könnte man die zu archivierende Partition (hier arch_stat_1) mit einer Policy verknüpfen, die eine Komprimierung der Daten nach einer bestimmten Eigenschaft einleitet. Folgendes Beispiel zeigt eine mögliche Policy. (Hinweis: Vergessen Sie nicht, vorher die Heatmap einzuschalten.)

SQL> alter table sh.salesarch_part modify partition arch_stat_1 
     ILM ADD POLICY row store compress advanced SEGMENT AFTER 10 DAY OF NO MODIFICATION;

Lizenzierung

Das Feature In-Database Archiving ist in allen Editionen verfügbar. Für die im letzten Abschnitt erwähnten Features sind allerdings zusätzlich die Advanced Compression Option (für ADO) bzw. die Partitioning Option erforderlich.

Links

Oracle Learning Library: Using In-Database Row Archiving

Using in database Archiving aus Database VLDB and Partitioning Guide

12c: In-Database Archiving And Compression (Doc ID 1592186.1)

Zurück zur Community-Seite