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
Best Answer
ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the "live" table. The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.