Well, we can use partitions to reduce to a tiny fraction the redo (and insert /*+ append */ would "help" you in reducing redo for it skips undo generation on the table insert -- but since you don't truncate, you'd always be writing above the HWM....)


So, how can we reduce the redo?

by reducing significantly the undo generated. We can in fact skip undo on both the TABLE and the INDEX by using a partitioned table with a single partition. Consider this small example:


ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 OWNER,
4 OBJECT_NAME,
5 SUBOBJECT_NAME,
6 OBJECT_ID,
7 DATA_OBJECT_ID,
8 OBJECT_TYPE,
9 CREATED,
10 LAST_DDL_TIME,
11 TIMESTAMP,
12 STATUS,
13 TEMPORARY,
14 GENERATED,
15 SECONDARY
16 )
17 PARTITION BY RANGE (object_id)
18 (
19 PARTITION junk VALUES LESS THAN (MAXVALUE)
20 )
21 as
22 select * from all_objects where rownum <= 5
23 /

Table created.

where object_id < maxvalue, single partition -- starts with just 5 rows in this case


ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(object_id) using index local;

Table altered.

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.

so, that is your table you want to refresh from time to time... We'll set up a one time "mirror" of that table:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table new_t
2 as
3 select *
4 from all_objects
5 where 1=0;

Table created.

it'll start empty. We create a view and a procedure on table T to see what effect this technique will have on them "invalidation wise" (none as we'll see)


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v as select * from t;

View created.

ops$tkyte@ORA9IR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t ) loop null; end loop;
5 end;
6 /

Procedure created.

ok, here is the refresh routine, I'll inject comments in bold in it:


ops$tkyte@ORA9IR2> create or replace procedure refresh_t
2 as
3 no_constraint exception;
4 pragma exception_init( no_constraint, -2443 );
5 l_numrows number;
6 l_numblks number;
7 l_numlblks number;
8 l_avgrlen number;
9 l_numdist number;
10 l_avglblk number;
11 l_avgdblk number;
12 l_clstfct number;
13 l_indlevel number;
14 begin

remove pretty much the redo from the "delete" (your biggest part!), truncate is standby safe 100% it just updates the dictionary:

15 execute immediate 'truncate table new_t';
16

Now, we don't want to maintain the index in real time, so, if one exists, drop it, ignore "no constraint" errors:

17 begin
18 execute immediate 'alter table new_t drop constraint new_t_pk';
19 exception
20 when no_constraint
21 then
22 null;
23 end;
24

Now, using insert append (skips undo generation on the table) load the new table:

25 insert /*+ append */ into new_t
26 select *
27 from all_objects;
28

and index it, the create index doesn't have to generate undo (we just rollback the dictionary change -- standby "safe"

29 execute immediate
30 'alter table new_t
31 add constraint new_t_pk
32 primary key(object_id)';
33

lets get the correct stats and save them, if you use column stats, you can add that as well:

34 dbms_stats.gather_table_stats
35 ( user, 'NEW_T', cascade=>true );
36
37 dbms_stats.get_table_stats
38 ( user, 'NEW_T', numrows => l_numrows,
39 numblks => l_numblks, avgrlen => l_avgrlen );
40
41 dbms_stats.get_index_stats
42 ( user, 'NEW_T_PK',
43 numrows => l_numrows, numlblks => l_numlblks,
44 numdist => l_numdist, avglblk => l_avglblk,
45 avgdblk => l_avgdblk, clstfct => l_clstfct,
46 indlevel => l_indlevel );
47

now, do the swap -- make the newly loaded table become the partitioned table and vice versa:

48 execute immediate
49 'alter table t
50 exchange partition junk
51 with table new_t
52 including indexes
53 without validation';
54

and update the table level stats, the swap will get the partition stats -- this just makes the table level stuff "correct" as well


55 dbms_stats.set_table_stats
56 ( user, 'T',
57 numrows => l_numrows, numblks => l_numblks,
58 avgrlen => l_avgrlen );
59
60 dbms_stats.set_index_stats
61 ( user, 'T_PK',
62 numrows => l_numrows, numlblks => l_numlblks,
63 numdist => l_numdist, avglblk => l_avglblk,
64 avgdblk => l_avgdblk, clstfct => l_clstfct,
65 indlevel => l_indlevel );
66
67 end;
68 /

Procedure created.

Ok, lets see what we see, remember table starts with 5 rows:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 5

ops$tkyte@ORA9IR2> select index_name, num_rows from user_indexes where index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 5

ops$tkyte@ORA9IR2> select table_name, num_rows from user_tab_partitions where table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 5

ops$tkyte@ORA9IR2> select index_name, num_rows from user_ind_partitions where index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 5


we do the swap:


ops$tkyte@ORA9IR2> exec refresh_t;

PL/SQL procedure successfully completed.


code and views stay "valid", they are safe

ops$tkyte@ORA9IR2> select object_name, status from user_objects where object_name in ('V','P');

OBJECT_NAME STATUS
------------------------------ -------
P VALID
V VALID


and the stats are all in place...


ops$tkyte@ORA9IR2> select table_name, num_rows from user_tables where table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 26334

ops$tkyte@ORA9IR2> select index_name, num_rows from user_indexes where index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 26334

ops$tkyte@ORA9IR2> select table_name, num_rows from user_tab_partitions where table_name = 'T';

TABLE_NAME NUM_ROWS
------------------------------ ----------
T 26334

ops$tkyte@ORA9IR2> select index_name, num_rows from user_ind_partitions where index_name = 'T_PK';

INDEX_NAME NUM_ROWS
------------------------------ ----------
T_PK 26334


Now, how about redo, what effect will this have on it?

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off

NAME VALUE
------------------------------ ----------
redo size 29524876

ops$tkyte@ORA9IR2> exec refresh_t;

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off

NAME V DIFF
------------------------------ ---------- ----------------
redo size 30085276 560,400

that way took about 560k of redo, lets check out the delete+insert approach:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off

NAME VALUE
------------------------------ ----------
redo size 30085276

ops$tkyte@ORA9IR2> delete from new_t;

26334 rows deleted.

ops$tkyte@ORA9IR2> insert into new_t select * from all_objects;

27894 rows created.

ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off

NAME V DIFF
------------------------------ ---------- ----------------
redo size 48908380 18,823,104


18meg, very significant difference -- you'll find the consistent gets go way down too (that was the index I think you'll discover)