Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore require a complete refresh. Oracle implemented an atomic complete refresh as a delete and insert of every record. It does this even if there are ultimately no changes to the data.

Is there a way to make this replication intelligent with regard to redo generation? A MERGE followed by a DELETE requires querying the source twice. Would it be worth it to bulk collect the data to do a BULK MERGE and DELETE? Is there a better way?

Update:

I explored using a global temporary table as a staging area. Although they use less than half the redo, they still use to much.

share|improve this question
    
Can you post the gtt code? gtt's don't generate redo directly, but they do generate undo - and undo generates redo. insert ops generate much less undo than delete or update ops (almost none in fact). Having multiple gtts to avoid any expensive ops might be a good approach –  Jack Douglas Jun 14 '11 at 8:05
    
@Jack Douglas psoug.org/reference/gtt.html has a GTT Redo Generation Demo showing a 60% reduction in redo between a physical table and a GTT for inserts. This closely matches the results I am seeing and is better but not as good as I would like. –  Leigh Riffel Jun 14 '11 at 13:30
    
Those tests (row-by-row and no append hint) are not ideal conditions for reducing redo - I've run some tests to show what I mean. Posted as an answer because they won't fit in a comment –  Jack Douglas Jun 14 '11 at 20:34
add comment

2 Answers 2

up vote 2 down vote accepted
+50

This is just intended to demonstrate redo usage of various insert operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.

For the heap tables, I do not know why the insert /*+ append */ generated more redo.

testbed:

create table heap_noappend(id integer, dummy char(500));
create table heap_append(id integer, dummy char(500));
create global temporary table gtt_noappend(id integer, dummy char(500));
create global temporary table gtt_append(id integer, dummy char(500));
create global temporary table gtt_results(stage integer, val integer);

test:

insert into gtt_results(stage, val)
select 0, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert into heap_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 1, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert /*+ append */ into heap_append(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 2, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert into gtt_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 3, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert /*+ append */ into gtt_append(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 4, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

result:

select * 
from( select decode(stage,1,'heap noappend',
                          2,'heap append',
                          3,'gtt noappend',
                          4,'gtt append') as operation, 
             val-lag(val) over(order by stage) as redo 
      from gtt_results)
where redo is not null;

OPERATION     REDO                   
------------- ---------------------- 
heap noappend 606932                 
heap append   690768                 
gtt noappend  41488                  
gtt append    256                   
share|improve this answer
    
You are correct of course. I should have caught that in their tests. I'll give it a try. –  Leigh Riffel Jun 16 '11 at 11:49
add comment

Good question. I "solved" this problem for my situation a while back by making the MV's and any indexes on them NOLOGGING. There was no point to it my situation - I was doing a full refresh of the view anyway, why would I need redo?

share|improve this answer
1  
You may need ATOMIC_REFRESH=false too (on 10g and above). Not sure what the implications are for any standby database or recovery with archive logs? –  Jack Douglas Jun 12 '11 at 12:01
    
I run a logical and a physical standby on the database I did this with. No issues there. I did run into an issue with making DB copies - have to look at my notes but there was an error I would sometimes get about performing recovery on a tablespace with nologging tables. I've read recommendations to create a tablespace reserved for tables/indexes that are nonlogging to avoid such issues. I did figure out how to solve it though. –  DCookie Jun 13 '11 at 2:13
    
@Jack, I believe I did have to use non atomic refresh. –  DCookie Jun 13 '11 at 2:14
    
Hmmm, if I use a standard materialized view, it needs to do an atomic refresh, so this won't work for me. Someone else may find it useful, so it is still a good answer. –  Leigh Riffel Jun 13 '11 at 13:49
    
Why does it need an atomic refresh? As I understand it, setting that to false only affects a FULL refresh. See this asktom post: asktom.oracle.com/pls/apex/… –  DCookie Jun 13 '11 at 15:39
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.