Diff between Index Drop/Create and Unusable/Rebuild

Which of the two, DROP/CREATE or UNUSABLE/REBUILD as an intermediate step, is faster when doing a BULK DELETE, followed by a BULK INSERT. I am trying to delete over 6million rows from the target table, followed by an equal number of inserts in the target table, using a Stage table for comparison, and this is done every night.

No primary key or existing index on the table. The yet to be created index would be based on the logical key on target table which is :

PERNR    VARCHAR2(8)
TXCMP    VARCHAR2(4) -- could have NULL values
TAXAU    VARCHAR2(4) -- could have NULL values
LGART    VARCHAR2(4)
CUMTY    VARCHAR2(1)
CUMNO    VARCHAR2(2)
CUMYR    VARCHAR2(4)

The DELETEs are --
DELETE1
delete from TARGET where (PERNR, TXCMP, TAXAU, LGART, CUMTY, CUMNO, CUMYR) in (select PERNR, TXCMP, TAXAU, LGART, CUMTY, CUMNO, CUMYR from STAGE)
DELETE2
delete from TARGET where (PERNR, LGART, CUMTY, CUMNO, CUMYR) in (select PERNR, LGART, CUMTY, CUMNO, CUMYR from STAGE) and (TARGET.TXCMP is null or TARGET.TAXAU is null)

The INSERT follows this with a KEY-Generation mechanism.

The DELETEs are rightnow taking about 17-30 minutes. Hence thinking of creating the index, and use it for deletion, and I am not sure which of the two mechanism DROP/CREATE or UNUSABLE/REBUILD be more efficient.

Thanks,

Rohit Received on Tue Sep 02 2003 - 17:26:01 CDT