avoid index rebuild in write mode

[mathewb]

Hi,

I am writing the data into Oracle table with write method as Load and write mode as Append. The table is empty before loading. In this scenario, I don't want to rebuild the index after loading. How can I achieve this.

If I specify the APT_ORACLE_LOAD_OPTIONS as
OPTIONS(DIRECT=FALSE, PARALLEL=TRUE) will this work?? I am not seeing rebuilding step in log but since the index is valid I doubt that the index was disabled while loading.

Also, if I specify the parameter as OPTIONS(DIRECT=FALSE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE) will this help and what does this perform different from previous setting.

If I have the index in unusable state before starting the job, the loading fails. Could you guys pls help.

[chulett]

DIRECT=FALSE gives you a conventional load, which means normal INSERT statements using a bind array buffer which doesn't disable or rebuild your index. Also note you can only use the PARALLEL option with direct path loads.

[mathewb]

so is there a way for direct load and not to rebuild the index at end.

[chulett]

Not that I'm aware of, but that's more of a DBA question than a DataStage question.

[mathewb]

Hi,

Thanks for the proper direction...I was searching the DS manuals for these options. It is there in the SQL Loader manuals... I have completed the task by using the following option

APT_ORACLE_LOAD_OPTIONS=
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES
=TRUE)

The last option helped to avoid the error while loading into the table with disabled indexes. I now disable the index then perform the direct load.

Thanks
Biju Mathew

[Kryt0n]

Hi,

APT_ORACLE_LOAD_OPTIONS=
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES
=TRUE)

The last option helped to avoid the error while loading into the table with disabled indexes. I now disable the index then perform the direct load.

Has anyone else tried this (will be hard to believe no-one has in the two years since!)? I am setting my APT_ORACLE_LOAD_OPTIONS to

Code: Select all

OPTIONS(DIRECT=TRUE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)

But still getting errors (have also tried "YES" instead of "TRUE" with same result). Rather annoyingly the error tells me to refer to the oracle log files for which the log file is telling me everything loaded fine.

The indexes are definitely in an unusable state and I'm doing a load append with no index maintenance applied at the stage level. Is there anything else that should be set?

I do note that the log files are still referring to the indexes with the message "... was made unusable due to:
SKIP_INDEX_MAINTENANCE option requested" although I had made them unusable prior to running... doesn't look like an error to me though!

TIA