Oracle Enterprise, loader, index rebuild
Moderators: chulett, rschirm, roy
I wish to explain why I need some DS help before I ask my DBA that question:
I did an experiment with a target table that has no partitioned indexes, so index mode=rebuild may be used.
I found the CTL file and it had:
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES).
So I used this option for APT_ORACLE_LOAD_OPTIONS job parameter, and no index option in Oracle stage, and a table with a partitioned index. The job went fine.
Status of an index partition affected was unusable after the job, as one would expect.
Now I need to know what does DS try to do for rebuild (which statement does it issue), when maintenance option is chosen. Then I will ask my DBA if this statement is valid for partitioned indexes (or I will try it myself).
I did an experiment with a target table that has no partitioned indexes, so index mode=rebuild may be used.
I found the CTL file and it had:
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES).
So I used this option for APT_ORACLE_LOAD_OPTIONS job parameter, and no index option in Oracle stage, and a table with a partitioned index. The job went fine.
Status of an index partition affected was unusable after the job, as one would expect.
Now I need to know what does DS try to do for rebuild (which statement does it issue), when maintenance option is chosen. Then I will ask my DBA if this statement is valid for partitioned indexes (or I will try it myself).
I use partitioned indexes as a primary key on my paritioned tables. Unfortunately, DataStage will attempt to reubild the whole index instead of just the partitioned index. I noticed as my tables got larger the more time the job would take to run. The only way to handle this is to load a temporary table which has the same index structure as the partitioned table and then exchange the temp table with a partition created at run-time. I found this to be the most effective way to load these types of tables. It does involve more work because you need routines to handle the partition creation exchanging, etc.
Hope this helps.
Hope this helps.
Research SINGLEROW option in SQL*Loader
Research SINGLEROW option in SQL*Loader manual.
If it holds true for your db table, ask your IBM contact for SINGLEROW APT option fix.
We had something similar, plus we were restricted to index storage size.
The IBM team created a fix for us by supplying a patch that includes the SINGLEROW option into the control file.
However, to date, this option is incompatible when requesting an Oracle 8 partition.
I do not have access to our DS right now but I am about 90% sure they fix they sent us was APT_SINGLEROW_OPTION.
Hope this helps ! :D
If it holds true for your db table, ask your IBM contact for SINGLEROW APT option fix.
We had something similar, plus we were restricted to index storage size.
The IBM team created a fix for us by supplying a patch that includes the SINGLEROW option into the control file.
However, to date, this option is incompatible when requesting an Oracle 8 partition.
I do not have access to our DS right now but I am about 90% sure they fix they sent us was APT_SINGLEROW_OPTION.
Hope this helps ! :D