What Performance benefit (IF ANY) would this "Optimizer Predicate Filter" of data in an archive state, have on:
Execution Plans, Retrieval Speed and Response Times?

Or is this feature simply to Enable an ILM/Archive strategy?

SQL> create table t (
  2             x int,
  3             y int
  4      )
  5      row archival
  6      enable row movement
  7  partition by list ( ORA_ARCHIVE_STATE )
  8  (
  9    partition p1 values ('1'),
 10    partition p2 values ('0')
 11  );

Table created.

Hi,

What do you suggest if the table is already partitioned (not partitioned on ora_achive column).

Regards!!

create table t (
  x int,
  y int
) row archival enable row movement
partition by hash (x) 
subpartition by list ( ORA_ARCHIVE_STATE )
subpartition template
(
  subpartition p1 values ('1'),
  subpartition p2 values ('0')
) partitions 4;

create table t (
  x int,
  y int
) row archival enable row movement
partition by list ( ORA_ARCHIVE_STATE )
subpartition by hash (x) subpartitions 4 (
  partition p1 values ('1'),
  partition p2 values ('0')
);

In Database archiving is still possible on Partitioned Tables.

Think of In-Database archiving as something like this.

Rather than deleting the data, some application has the concept of “mark for delete”, so that data remains in table, but not used by application – this can be achieved by doing

a) Add an extra column to table – that holds a flag to indicate the data is deleted.

b) Add an extra column to SQL – that checks to exclude the deleted(inactive) rows from processing ( where deleted <> ‘N’)

In database archiving is a feature added up in 12c to allow this type of “mark for delete” functionality with few changes to application code


Rows that have been marked as archived are not visible (equivalent to a logical delete) unless the session is enabled to see archived data.

so think of In-Database archival as something equivalent to a logical deletes. no clean up of old data in tables (no physical delete), no more white space in tables, no more index rebuilds for table data cleanup.

demo@ORA12C> create table t
  2  partition by hash(object_id)
  3  ( partition p1, partition p2 )
  4  as
  5  select owner,object_id,object_name,object_type
  6  from all_objects
  7  where rownum <=10;

Table created.

demo@ORA12C> alter table t row archival;

Table altered.

demo@ORA12C> column ora_archive_state format a10
demo@ORA12C> select t.*,ora_archive_state from t;

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE             ORA_ARCHIV
---------- ---------- ------------------------------ ----------------------- ----------
SYS               133 ORA$BASE                       EDITION                 0
PUBLIC            143 DUAL                           SYNONYM                 0
SYS               417 MAP_OBJECT                     TABLE                   0
SYS               523 TABLE_PRIVILEGE_MAP            TABLE                   0
SYS               524 I_TABLE_PRIVILEGE_MAP          INDEX                   0
SYS               142 DUAL                           TABLE                   0
PUBLIC            418 MAP_OBJECT                     SYNONYM                 0
SYS               520 SYSTEM_PRIVILEGE_MAP           TABLE                   0
SYS               521 I_SYSTEM_PRIVILEGE_MAP         INDEX                   0
PUBLIC            522 SYSTEM_PRIVILEGE_MAP           SYNONYM                 0

10 rows selected.

demo@ORA12C> update t
  2  set ora_archive_state = dbms_ilm.archiveStateName(1)
  3  where object_id <=200;

3 rows updated.

demo@ORA12C> select t.*,ora_archive_state from t;

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE             ORA_ARCHIV
---------- ---------- ------------------------------ ----------------------- ----------
SYS               417 MAP_OBJECT                     TABLE                   0
SYS               523 TABLE_PRIVILEGE_MAP            TABLE                   0
SYS               524 I_TABLE_PRIVILEGE_MAP          INDEX                   0
PUBLIC            418 MAP_OBJECT                     SYNONYM                 0
SYS               520 SYSTEM_PRIVILEGE_MAP           TABLE                   0
SYS               521 I_SYSTEM_PRIVILEGE_MAP         INDEX                   0
PUBLIC            522 SYSTEM_PRIVILEGE_MAP           SYNONYM                 0

7 rows selected.

demo@ORA12C> alter session set row archival visibility=all;

Session altered.

demo@ORA12C> select t.*,ora_archive_state from t;

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE             ORA_ARCHIV
---------- ---------- ------------------------------ ----------------------- ----------
SYS               133 ORA$BASE                       EDITION                 1
PUBLIC            143 DUAL                           SYNONYM                 1
SYS               417 MAP_OBJECT                     TABLE                   0
SYS               523 TABLE_PRIVILEGE_MAP            TABLE                   0
SYS               524 I_TABLE_PRIVILEGE_MAP          INDEX                   0
SYS               142 DUAL                           TABLE                   1
PUBLIC            418 MAP_OBJECT                     SYNONYM                 0
SYS               520 SYSTEM_PRIVILEGE_MAP           TABLE                   0
SYS               521 I_SYSTEM_PRIVILEGE_MAP         INDEX                   0
PUBLIC            522 SYSTEM_PRIVILEGE_MAP           SYNONYM                 0

10 rows selected.

demo@ORA12C> alter session set row archival visibility=active;

Session altered.

demo@ORA12C> select t.*,ora_archive_state from t;

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE             ORA_ARCHIV
---------- ---------- ------------------------------ ----------------------- ----------
SYS               417 MAP_OBJECT                     TABLE                   0
SYS               523 TABLE_PRIVILEGE_MAP            TABLE                   0
SYS               524 I_TABLE_PRIVILEGE_MAP          INDEX                   0
PUBLIC            418 MAP_OBJECT                     SYNONYM                 0
SYS               520 SYSTEM_PRIVILEGE_MAP           TABLE                   0
SYS               521 I_SYSTEM_PRIVILEGE_MAP         INDEX                   0
PUBLIC            522 SYSTEM_PRIVILEGE_MAP           SYNONYM                 0

7 rows selected.

demo@ORA12C>

I tried a demo in LiveSQL, and the VISIBILITY=ACTIVE session alteration doesn't seem to work:

SCRIPT

create table EMP as select * from scott.emp;
alter table EMP add primary key ( empno );
alter table EMP row archival;
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
select deptno, count(*), sum(ora_archive_state) sum_hidden
  from EMP group by deptno order by 1;
UPDATE EMP
   SET ora_archive_state = '1'
 WHERE deptno = 20;
COMMIT;
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
select deptno, count(*), sum(ora_archive_state) sum_hidden
  from EMP group by deptno order by 1;

Did I miss something? I see all the records, although some are marked:

RESULTS

Table created.

Table altered.

Table altered.

Statement processed.

Result Set 1
DEPTNO COUNT(*) SUM_HIDDEN
10 3 0
20 5 0
30 6 0
Download CSV
3 rows selected.

5 row(s) updated.

Statement processed.

Statement processed.

Result Set 2
DEPTNO COUNT(*) SUM_HIDDEN
10 3 0
20 5 5
30 6 0

3 rows selected.

As Vidar Eidissen noticed, the LiveSQL demo script suggests that VISIBILITY is undetermined. Explicitly it say:

"Note that the WHERE clause has been added for demonstration purposes in Live SQL. Outside the Live SQL environment the WHERE clause would not be needed because the ROW ARCHIVAL VISIBILITY session setting can be determined."

https://livesql.oracle.com/apex/livesql/docs/vldbg/indbarchive/indatabase-archiving-example.html

I can't see to find it in V$PARAMETERS. How do I know what the current setting in my session?

SQL> create table t ( x int ) row archival;

Table created.

SQL> insert into t values (1);

1 row created.

SQL> update t set ora_archive_state=dbms_ilm.archivestatename(1);

1 row updated.

SQL>
SQL> select * From t;

no rows selected

SQL> alter session set row archival visibility = all;

Session altered.

SQL> select * From t;

         X
----------
         1

I guess kludge it is! ;)

drop table Z$_ROW_ARCHIVAL;
drop view Z$_ROW_ARCHIVAL_VISIBILITY;
create table Z$_ROW_ARCHIVAL ( visibility varchar2(10) primary key ) row archival;
insert into Z$_ROW_ARCHIVAL values ( 'active' );
insert into Z$_ROW_ARCHIVAL values ( 'all' );
UPDATE z$_ROW_ARCHIVAL
   SET ora_archive_state = '1'
 WHERE visibility = 'all'
 ;
 commit;
create view Z$_ROW_ARCHIVAL_VISIBILITY AS select max(visibility) as visibility from Z$_ROW_ARCHIVAL;
select * from Z$_ROW_ARCHIVAL_VISIBILITY;

VISIBILITY
----------
active

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = active;

Session altered.

select * from Z$_ROW_ARCHIVAL_VISIBILITY;

VISIBILITY
----------
active

select * from z$_ROW_ARCHIVAL;

VISIBILITY
----------
active

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = all;

Session altered.

select * from Z$_ROW_ARCHIVAL_VISIBILITY;

VISIBILITY
----------
all

select * from z$_ROW_ARCHIVAL;

VISIBILITY
----------
active
all

It's summer for you Antipodeans!

Setting that aside: I've poked at v$sql_optimizer_env, v$ses_optimizer_env, etc. Unfortunately I don't have access to the underlying objects.