Oracle Database Row Archiving

In a recent customer project, I was suffering to delete millions of rows from a LOG table.

Removing 3 months of data took more than 30 minutes with the best optimization possible.

After a while, I remembered that a very nice oracle feature existing since Oracle 12cR1 enables us to archive rows automatically.

Let’s me explain you how it works:

I have a big table with 10 millions of rows:

SQL> create table bigtable
segment creation immediate
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 10000000
)
select
        rownum                          id,
        mod(rownum-1,3)                 val1,
        mod(rownum-1,10)                val2,
        lpad('x',100,'x')               padding
from
        generator       v1
order by
        dbms_random.value
 21  ;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','BIGTABLE');

PL/SQL procedure successfully completed.

SQL> desc bigtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VAL1                                               NUMBER
 VAL2                                               NUMBER
 PADDING                                            VARCHAR2(100)

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  10000000

SQL>

Let’s see the data dispersion related to VAL2 column:

SQL> select count(*),val2 from bigtable group by val2;

  COUNT(*)       VAL2
---------- ----------
   1000000          6
   1000000          1
   1000000          7
   1000000          8
   1000000          2
   1000000          4
   1000000          5
   1000000          9
   1000000          3
   1000000          0

10 rows selected.

SQL>

Let’s deleting rows related to VAL2 value = 8 :

SQL> set timing on
SQL> delete from bigtable where val2=8;
SQL > commit;

1000000 rows deleted.

Elapsed: 00:00:02.97
SQL> explain plan for delete from bigtable where val2=8;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1385633249

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |          |  1000K|  2929K| 44506   (1)| 00:00:02 |
|   1 |  DELETE            | BIGTABLE |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| BIGTABLE |  1000K|  2929K| 44506   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("VAL2"=8)

14 rows selected.

Elapsed: 00:00:00.02
SQL>

The rows have been removed and the oracle optimizer does a Full Scan to access the data because there is no Index on the VAL2 column. The DELETE sql statement took about 3 sec to execute.

For any reason If you want to restore this “deleting” rows, only “flashback” or “restore” from a backup will help you.

Now let’s enabling ROW ARCHIVAL on the table BIGTABLE:

SQL> alter table bigtable row archival;

Table altered.

Elapsed: 00:00:00.05
SQL>

A new hidden column called ora_archive_state is created indicating whether a row is archived or not. The column is displayed only via the SELECT command, not in the DESC command.

SQL> desc bigtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VAL1                                               NUMBER
 VAL2                                               NUMBER
 PADDING                                            VARCHAR2(100)

SQL> select ID,VAL1,VAL2,PADDING,ora_archive_state from bigtable where rownum = 1;

ID       VAL1       VAL2  PADDING       ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
4091832  2          1     xxxxxxxxxxx   0


Elapsed: 00:00:00.00
SQL>

The value 0 in the ora_archive_state means the row is not archived, thus visible via the SELECT.

Let’s confirm all rows are active (not archived):

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  10000000

Now let’s suppose we want to archive all rows having value VAL2=6.

Lets’s modify the value of ora_archive_state:

SQL> update bigtable set ora_archive_state=dbms_ilm.archivestatename(1) where val2=6;

1000000 rows updated.

Elapsed: 00:00:37.94
SQL> commit;

Commit complete.

Elapsed: 00:00:01.28
SQL>

Let’s count the number of rows active now:

SQL> select count(*) from bigtable;

  COUNT(*)
----------
   8000000

Elapsed: 00:00:02.17
SQL>

The rows archived are no more visible, they have been deleted “logically” (or archived):

If we want to view all rows archived or not, set the archival visibility to ALL for the session:

SQL> alter session set row archival visibility = all;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  90000000

SQL> select count(*),val2,ora_archive_state from bigtable group by val2, ora_archive_state;

  COUNT(*)       VAL2         ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
1000000          1           0
1000000          4           0
1000000          6           1
1000000          3           0
1000000          5           0
1000000          9           0
1000000          7           0
1000000          2           0
1000000          0           0

9 rows selected.

To see only rows unarchived, set the archival visibility to ACTIVE:

SQL> alter session set row archival visibility = active;

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*),val2,ora_archive_state from bigtable group by val2, ora_archive_state;

  COUNT(*)       VAL2 O
---------- ---------- -
   1000000          1 0
   1000000          7 0
   1000000          2 0
   1000000          4 0
   1000000          5 0
   1000000          9 0
   1000000          3 0
   1000000          0 0

8 rows selected.

CONCLUSION

Oracle Database Row Archiving enables you to archive and delete “logically” the rows.

Generally, it can be used to archive rows in big table.

The implementation of this feature is very easy and can help you when you have to deal archiving/deleting rows in big table.