Steps briefly
Disable constraints
Truncate target table
Disable indexes
Run bulk upload ETL job
Rebuild indexes
Create unique indexes
Enable constraints
Gather optimizer statistics
Details
Disable
R constraints
Disable
U,P constraints
-
It
drops UNIQUE index if it was created by ALTER TABLE ADD CONSTRAINT statement (use KEEP|DROP INDEX to change the behavior)
-
Other
indexes remain
Truncate
target table
It
validates unusable indexes!
Make
indexes UNUSABLE
-
UNUSABLE
UNIQUE index does not allow DML. It has to be dropped.
ORA-26026:
unique index BID.TEST1_I1 initially in unusable state (Direct-path Insert)
ORA-01502:
index 'BID.TEST1_I1' or partition of such index is in unusable state (Regular
Insert)
-
U,P
constraints can also be policed by non-unique indexes
Run
bulk upload ETL job
DataStage bulk load is seen as /*+ SYS_DL_CURSOR */ (OCI direct path load).
Target
table needs to be NOLOGGING to reduce REDO (unrecoverable operation!)
For direct-path INSERT
e.g. /*+ APPEND */, /*+ SYS_DL_CURSOR */
table
mode
|
DB
log_mode
|
REDO
|
recovery
|
logging
|
noarchivelog
|
no
redo
|
PITR
before insert
|
nologging
|
noarchivelog
|
no
redo
|
PITR
before insert
|
logging
|
archivelog
|
redo
|
to
the failure
|
nologging
|
archivelog
|
no
redo
|
PITR
before insert
|
Other DML always generate REDO.
If you do direct-path insert into table with indexes,
index update will generate redo whether it is LOGGING or NOLOGGING. (and index is recoverable)
Rebuild
indexes
Use
NOLOGGING, PARALLEL to speed up. Unrecoverable operation!
After DB/TS
recovery you might have:
SELECT
COUNT(1) FROM BID.S_CLAIM_STATS_YEARLY WHERE INDUSTRY_ID=18005047
*
Error
at line 0
ORA-01578:
ORACLE data block corrupted (file # 73, block # 10488)
ORA-01110:
data file 73: '/data001/oradata/BIDDEV/biddev_bid_data_test.dbf'
ORA-26040: Data block was loaded using the
NOLOGGING option
Attempt to rebuild index returns the same errors. (ALTER INDEX BID.CLMSTSYR_IND_FK REBUILD;)
Index has to be (dropped and recreated) or (made unusable
and then rebuild).
Create
unique indexes
If it is
not part of constraint or you can leave it for CONSTRAINT ENABLE statements
Enable
constraints R,U,P
If there
are no proper indexes for U,P constrains, CONSTRAINT ENABLE statement will
create the index. By default it will be LOGGING in DEFAULT tablespace. Use USING INDEX to change it (you don’t have
to specify index name), e.g.
ALTER TABLE BID.TEST1 ENABLE CONSTRAINT TEST1_U USING INDEX NOLOGGING TABLESPACE BID_DATA_TEST;
Gather optimizer statistics
Gather statistics for the table. Indexes can be excluded if they were rebuilt (CASCADE=FALSE) as Oracle automatically collects statistics during index creation and rebuild.
Alternatively statistics for table+indexes can be gathered before rebuilding indexes, especially if not all indexes are disabled or dropped.
Documentation
Direct Path Loading
INSERT statement
logging_clause
