ALTER TABLE PRODUCTION_TABLE RENAME TO tmp_TABLE,
ALTER TABLE STAGE_TABLE RENAME TO PRODUCTION_TABLE

If you have validated your stage_table to death - you know that ALL constraints for it are valid - any foreign keys it has are true, any foreign keys that will be to it are true - you can use partitioning to do this.

P in the example below is the table you want to reload.

P will have a single partition. All indexes will be "local".

You can load and index a new_table.

You will *verify* that new_table is correct - you will verify this to death (because we won't - the goal is "save time")

You will disable foreign keys - table P has a foreign key to PP, table P has a foreign key that points to it.

You can then exchange the data in without validation.

And then re-enable but do not validate the foreign keys.


If you set up for the foreign keys to be "rely", you can still allow the optimizer to use them to optimize queries as well, see:

http://asktom.oracle.com/Misc/stuck-in-rut.html

ops$tkyte%ORA11GR2> create table pp
  2  ( y varchar2(30) primary key,
  3    z date
  4  )
  5  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE p
  2  (
  3    x         int primary key using index (create unique index p_pk_idx on p(x) local),
  4    y         varchar2(30) constraint p_fk_pp references pp,
  5    z         varchar2(30)
  6  )
  7  PARTITION BY range(x)
  8  (
  9    PARTITION part1 VALUES LESS THAN (maxvalue)
 10  )
 11  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table c
  2  ( x int constraint c_fk_p references p(x),
  3    y int,
  4    z varchar2(30),
  5    constraint c_pk primary key(x,y)
  6  )
  7  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into pp select username, created from all_users;

44 rows created.

ops$tkyte%ORA11GR2> insert into p select rownum, username, 'some data' from all_users, (select level l from dual connect by level <= 2 );

88 rows created.

ops$tkyte%ORA11GR2> insert into c select x, rownum, '.....' from p, (select level l from dual connect by level <= 3 );

264 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table new_table
  2  as
  3  select x, y, upper(z) z
  4    from p;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table new_table add constraint new_table_pk primary key(x) using index (create unique index new_table_pk on new_table(x) );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table c disable constraint c_fk_p;

Table altered.

ops$tkyte%ORA11GR2> alter table p disable constraint p_fk_pp;

Table altered.

ops$tkyte%ORA11GR2> alter table p
  2  exchange partition part1
  3  with table new_table
  4  without validation;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table p enable novalidate constraint p_fk_pp;

Table altered.

ops$tkyte%ORA11GR2> alter table c enable novalidate constraint c_fk_p;

Table altered.