RE: Data load ideas

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 29 Apr 2004 12:52:58 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F877E7@irvmbxw02>

No, after the exchange the indexes are not left in an unusable state. See example below.

SQL> remark
SQL> remark  create original table
SQL> remark
SQL> create table names (id number, name varchar2 (30),

  2 constraint names_pk primary key (id) using index local)   3 partition by range (id)
  4 (partition names_p1 values less than (100),   5 partition names_p2 values less than (200)   6 ) ;
Table créée.

SQL> insert into names (id, name) values (50, 'PERCIVAL') ; 1 ligne créée.
SQL> insert into names (id, name) values (160, 'MARION') ; 1 ligne créée.
SQL> commit ;
Validation effectuée.

SQL> remark
SQL> remark  display data
SQL> remark
SQL> select * from names ;
        ID NAME                                                                


---------- ------------------------------                                      

        50 PERCIVAL                                                            
       160 MARION                                                              

SQL> remark
SQL> remark  create exchange table with enable novalidate constraint

SQL> remark
SQL> create table names_ex (id number, name varchar2 (30),   2 constraint names_ex_pk primary key (id) enable novalidate) ; Table créée.

SQL> insert into names_ex (id, name) values (60, 'ARCHIBALD') ; 1 ligne créée.
SQL> commit ;
Validation effectuée.

SQL> remark
SQL> remark  index on original table is usable
SQL> remark
SQL> select index_name, partition_name, status

  2 from user_ind_partitions
  3 where index_name = 'NAMES_PK' ;

INDEX_NAME                     PARTITION_NAME                 STATUS           


------------------------------ ------------------------------ --------         

NAMES_PK                       NAMES_P2                       USABLE           
NAMES_PK                       NAMES_P1                       USABLE           

SQL> remark
SQL> remark  change constraint to novalidate

SQL> remark
SQL> alter table names modify constraint names_pk enable novalidate ; Table modifiée.

SQL> remark
SQL> remark  after novalidate of PK: index on original table is still usable
SQL> remark
SQL> select index_name, partition_name, status

  2 from user_ind_partitions
  3 where index_name = 'NAMES_PK' ;

INDEX_NAME                     PARTITION_NAME                 STATUS           


------------------------------ ------------------------------ --------         

NAMES_PK                       NAMES_P2                       USABLE           
NAMES_PK                       NAMES_P1                       USABLE           

SQL> remark
SQL> remark  exchange partition

SQL> remark
SQL> alter table names exchange partition names_p1 with table names_ex   2 including indexes without validation ; Table modifiée.

SQL> remark
SQL> remark  after exchange: index on original table is still usable
SQL> remark
SQL> select index_name, partition_name, status

  2 from user_ind_partitions
  3 where index_name = 'NAMES_PK' ;

INDEX_NAME                     PARTITION_NAME                 STATUS           


------------------------------ ------------------------------ --------         

NAMES_PK                       NAMES_P2                       USABLE           
NAMES_PK                       NAMES_P1                       USABLE           

SQL> remark
SQL> remark  verify that exchange actually happened

SQL> remark
SQL> select * from names ;

        ID NAME                                                                


---------- ------------------------------                                      

        60 ARCHIBALD                                                           
       160 MARION                                                              

SQL> remark
SQL> remark  revalidate constraint (this can happen while table is being used
SQL> remark   for DML)

SQL> remark
SQL> alter table names modify constraint names_pk enable validate ; Table modifiée.

-----Original Message-----

From: Paul Baumgartel

Jacques, thanks, you are quite right. Somehow I missed that constraint state. However, after the exchange, the indexes are left in unusable state, and must be rebuilt, no? At least that's what happened when I tried it. That, in turn, is not compatible with keeping the site fully available during the data load operation.

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Thu Apr 29 2004 - 14:56:12 CDT