DBA Hub

📋Steps in this guide1/9

How to archive table rows in oracle 12c - DBACLASS DBACLASS

In oracle 12c a new feature called In-Database Archiving has been introduced. With this we can archive specific rows of a table as per our requirement. This is very helpful,when table contains lot of historical data and for full scan it is taking a lot of time.Using this we can archive the historical data. We […]

oracle clusteringintermediate
by OracleDba
13 views
1

We will see an example:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--- DBATOOL has 8 rows

SQL> select * from dbatool;

       EMP
----------
	 1
	 2
	 3
	 4
	 4
	 3
	 3
	 3

8 rows selected.
2

You can check the explain plan( Scanning all 8 rows)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> explain plan for select * from dbatool;

Explained.


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2022247455

-----------------------------------------------------------------------------
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	    |	  8 |	104 |	  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DBATOOL |	  8 |	104 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic statistics used: dynamic sampling (level=2)

12 rows selected.
3

Enable archiving of that table.

This will create one invisible column ORA_ARCHIVE_STATE:(0/1) ORA_ARCHIVE_STATE:0 – Means that row is not archived ORA_ARCHIVE_STATE:1 – Means that row is archived

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> alter table dbatool row archival;

Table altered.

SQL> ;
  1* select ora_archive_state,emp from dbatool
SQL> /

ORA_	    EMP
---- ----------
0	      1
0	      2
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

8 rows selected.
4

Lets archive the rows whose EMP is 1 or 2 .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
SQL> 
SQL> update dbatool set ora_archive_state=dbms_ilm.archivestatename(1) where emp in (1,2);

2 rows updated.

SQL> commit;

Commit complete
5

Now we can see the table has 6 rows and rest were archived.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select ora_archive_state,emp from dbatool;

ORA_	    EMP
---- ----------
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

6 rows selected.

SQL> select count(*) from dbatool;

  COUNT(*)
----------
	 6
6

If you check the explain ( It will scan only 6 rows, excluding the archival rows)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> explain plan for select * from dbatool;



Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2022247455

-----------------------------------------------------------------------------
| Id  | Operation	  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	    |	  6 | 12090 |	  2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DBATOOL |	  6 | 12090 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.
7

If you want to see the rows including archived rows at session level , then use

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> alter session set ROW ARCHIVAL VISIBILITY  = all;

Session altered.

SQL> select ora_archive_state,emp from dbatool;

ORA_	    EMP
---- ----------
1	      1
1	      2
0	      3
0	      4
0	      4
0	      3
0	      3
0	      3

8 rows selected.
8

For disabling archiving:

This will drop that invisible column.

Code/Command (click line numbers to comment):

1
2
alter table DBATOOL no row archival;
Table altered.
9

Performance Impact on sql queries and its fix:

Enable row archival  , can impact the performance of the sql queries. Lets see the below example: BEFORE ENABLEING ROW ARCHIVAL: Below query is taking proper index and completing in 1 second. Now lets enable row archival, and check the performance. AFTER ENABLING ROW ARCHIVAL: Now after enabling row archival, same query is doing full scan on the table and taking around 4 seconds. Because the row archival has added one virtual column , so the existing index not getting picked. 2 – filter(“OWNER”=’SYS’ AND “TEST2”.”ORA_ARCHIVE_STATE”=’0′) To fix it, we need to drop the existing index and recreate the index by including the virtual column ORA_ARCHIVE_STATE. Now query is running fine with proper index

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:01.01

16:34:40 SQL> explain plan for
16:34:43   2  select count(*) from test2 where owner='SYS';

Explained.

Elapsed: 00:00:00.03
16:34:46 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 225723219

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    66 |   258   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    66 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_INDX |     1 |    66 |   258   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYS')

14 rows selected.

Elapsed: 00:00:00.23

16:36:11 SQL> alter table test2 row archival;

SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:04.16
16:37:09 SQL> explain plan for
16:37:16   2  select count(*) from test2 where owner='SYS';

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 147238325

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  2068 |   367   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |  2068 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST2 |     1 |  2068 |   367   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SYS' AND "TEST2"."ORA_ARCHIVE_STATE"='0')

14 rows selected.

Elapsed: 00:00:00.03
16:37:28 SQL>

SQL> drop index TEST_INDX;

Index dropped.

Elapsed: 00:00:00.28


16:39:16 SQL> create index TEST_INDX on TEST2(OWNER,ORA_ARCHIVE_STATE) parallel 8;

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname =>'DBATEST',tabname =>'TEST2',cascade => true, method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

PL/SQL procedure successfully completed.

16:41:51 SQL>  select count(*) from test2 where owner='SYS';

  COUNT(*)
----------
   5458048

Elapsed: 00:00:01.06
16:41:55 SQL>  explain plan for
16:42:00   2  select count(*) from test2 where owner='SYS';

Explained.

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 225723219

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     8 |  1366   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_INDX |   503K|  3932K|  1366   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYS' AND "TEST2"."ORA_ARCHIVE_STATE"='0')

14 rows selected.

Elapsed: 00:00:00.03

Comments (0)

Please to add comments

No comments yet. Be the first to comment!