In Database archiving
Both active data and archived data can be kept on
the same tables, while the application can be limited to access only active data.
With the high compressed features available we can
keep the archived data online in the same database itself without dropping it or move to any archived
database.
HCC – HCC provides high compression ratio, and the
goal is to keep the data online.
HCC supports Exadata / Pillar Axiom and Sun ZFS storages.
Ware house compression -> up to 10 times compression
ratio. (Useful for Data warehouse environment)
Archive compression
-> up to 50 times compression ratio. (Useful for keeping dormant data
online)
SQL> create table emp (emp_id number, ename
varchar2(30), join_date date) row archival;
Table created.
Here we have added a key word as row archival which
means the in database archival has been enabled for this table.
This will create a psuedo column ORA_ARCHIVE_STATE,
for the active data the value will be set as 0 and as time passes the value will be set a 0 which mean the in
active data.
SQL> begin
for i in 1..1000 loop
insert into emp values (i,
dbms_random.string('U',30), to_date('01-JAN-2000','DD-MON-YYYY'));
end loop;
for i in 1001..2000 loop
insert into emp values (i, dbms_random.string('U',30), to_date('01-JAN-2001','DD-MON-YYYY'));
end loop;
for i in 2001..3000 loop
insert into emp values (i, dbms_random.string('U',30),
to_date('01-JAN-2005','DD-MON-YYYY'));
end loop;
for i in 3001..4000 loop
insert into emp values (i, dbms_random.string('U',30),
to_date('01-JAN-2010','DD-MON-YYYY'));
end loop;
for i in 4001..5000 loop
insert into emp values (i, dbms_random.string('U',30), sysdate);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
SQL> col ora_archive_state for a20;
SQL> select trunc(join_date), ora_archive_state ,
count(*) from emp group by trunc(join_date), ora_archive_state;
TRUNC(JOI ORA_ARCHIVE_STATE COUNT(*)
--------- -------------------- ----------
01-JAN-00 0 1000
01-JAN-01 0
1000
01-JAN-05 0 1000
01-JAN-10 0 1000
01-SEP-15 0 1000
As we can see the ora_archive_state is showing as 0
which means active data. It is also possible to make the data inactive by session the ora_archive_state
to some other value other than 0.
Data administrator has to manually set the rows to
inactive.
SQL> update emp set ORA_ARCHIVE_STATE = 1 where
join_date < to_date ('01-JAN-2011','DD-MON-YYYY');
4000 rows updated.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
1000
We have set the old data as inactive data, as you
can see the select count(*) from emp returns only 1000, which means application
visibility is set to active data only, even though your inactive data is
residing on the same table.
We can use the row archival visibility value to ALL
to view both active and in active data.
SQL> alter session set row archival visibility =
ALL;
Session altered.
SQL> select count(*) from emp;
COUNT(*)
----------
5000
SQL> select trunc(join_date), ora_archive_state ,
count(*) from emp group by trunc(join_date), ora_archive_state;
TRUNC(JOI ORA_ARCHIVE_STATE COUNT(*)
--------- -------------------- ----------
01-JAN-00 1 1000
01-SEP-15 0 1000
01-JAN-05 1 1000
01-JAN-10 1 1000
01-JAN-01 1 1000
You can activate it using the value active.
SQL> alter session set row archival visibility =
ACTIVE;
Session altered.
SQL> select count(*) from emp;
COUNT(*)
----------
1000
SQL> select trunc(join_date), ora_archive_state ,
count(*) from emp group by trunc(join_date), ora_archive_state;
TRUNC(JOI ORA_ARCHIVE_STATE COUNT(*)
--------- -------------------- ----------
01-SEP-15 0 1000
SQL> create index idx_emp on emp (emp_id);
Index created.
SQL> select * from emp where emp_id = 4500;
EMP_ID
ENAME JOIN_DATE
---------- ------------------------------ ---------
4500
RJHLGZTVSSFATUYTJSUBPJZTCSOBMX 01-SEP-15
Explan plan of above statement.
select * from emp where emp_id = 4500
Plan hash value: 1624282332
-----------------------------------------------------------------------------------------------
| Id |
Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP |
1 | 2041 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 |
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("EMP"."ORA_ARCHIVE_STATE"='0')
2 -
access("EMP_ID"=4500)
Note
-----
- dynamic
statistics used: dynamic sampling (level=2)
24 rows selected.
SQL> drop index idx_emp;
Index dropped.
SQL> alter table emp add primary key (emp_id);
Table altered.
Explain plan
select * from emp where emp_id = 4356
Plan hash value: 356330805
--------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT |
| | |
2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |
1 | 2041 | 2
(0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011204 | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("EMP"."ORA_ARCHIVE_STATE"='0')
2 - access("EMP_ID"=4356)
20 rows selected.
SQL> select min(emp_id), max(emp_id) from emp;
MIN(EMP_ID) MAX(EMP_ID)
----------- -----------
4001 5000
SQL> insert into emp values (1,
'Manzoor',sysdate);
insert into emp values (1, 'Manzoor',sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (MANZOOR.SYS_C0011204)
violated
From the above we can see that the primary key is
enforced even for the data which is not active.
SQL> select * from emp where emp_id =1;
no rows selected
select * from emp where emp_id =1
Plan hash value: 356330805
--------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 2 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |
1 | 2041 | 2 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011204 | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("EMP"."ORA_ARCHIVE_STATE"='0')
2 -
access("EMP_ID"=1)
20 rows selected.
SQL> alter session set row archival visibility
=all;
Session altered.
SQL> select * from emp where emp_id = 3645;
EMP_ID
ENAME JOIN_DATE
---------- ------------------------------ ---------
3645
ULGWLEUQIKJRRXYQEPPGPWVURAMNPX 01-JAN-10
select * from emp where emp_id = 3645
Plan hash value: 356330805
--------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
1 | 39 | 2
(0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011204 | 1 |
| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("EMP_ID"=3645)
When the visibility is set to ALL, the psuedo column
search will be ignored.
Always add the ora_archive_state column to the index
which you’re creating on the table.
To disable in-Database archiving
SQL> alter table emp no row archival;
Table altered.
SQL> select trunc(join_date), ora_archive_state ,
count(*) from emp group by trunc(join_date), ora_archive_state;
select trunc(join_date), ora_archive_state ,
count(*) from emp group by trunc(join_date), ora_archive_state
*
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid
identifier
Once disabled the psudo column ora_archive_state
will be dropped automatically.
Note-
Oracle will not automatically set appropriate values
when the data ages. It has to be done manually.
No comments:
Post a Comment