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.
avoid index rebuild in write mode
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
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 tomathewb wrote: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.
Code: Select all
OPTIONS(DIRECT=TRUE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=TRUE,SKIP_UNUSABLE_INDEXES=TRUE)
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