| SQL LOADER INDEX ISSUE [message #398219] |
Wed, 15 April 2009 13:12  |
OracleNovice
Messages: 1
Registered: April 2009
|
Junior Member |
|
|
I have chronic issue with a non-unique index on an oracle table. SQL Loader appears to be disabling this index when it runs, not dependably, or everytime, but often enough to render this index useless.
Here's the call to sql loader (in perl):
my @log = `sqlldr / control=$file_ctl log=$file_log data=$data_file direct=true`;
I've tried the following modifications:
my @log = `sqlldr / control=$file_ctl log=$file_log data=$data_file direct=false SKIP_INDEX_MAINTENANCE=false`;
Both the above examples work in test, but I've been unable to replicate the issue in the test environment.
Other pertinent facts:
- Users are occaisionally accessing this table in production, possibly when the loader process is running.
- The unique, primary key is not affected by this problem.
- Neither the table, nor its indices are partitioned.
- The test db has substantially fewer rows than the production db.
Specific questions(unanswered by Oracle):
- Is there an incompatibility between SQL Loader and index integrity?
- Beyond changing the DIRECT keyword value to 'FALSE', can any one suggest a simple solution?
Thanks in advance for any answers.
|
|
|
|
| Re: SQL LOADER INDEX ISSUE [message #398226 is a reply to message #398219] |
Wed, 15 April 2009 13:20  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From doc:
| Quote: | You can improve performance of direct path loads by using temporary storage. After each block is formatted, the new index keys are put in a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed.
|
If this process cannot succeed, the index is put in an unusable state and has to be rebuilt.
Regards
Michel
[Updated on: Wed, 15 April 2009 13:26] Report message to a moderator |
|
|
|
| Re: SQL LOADER INDEX ISSUE [message #398230 is a reply to message #398219] |
Wed, 15 April 2009 13:25 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Also:
| Quote: | SQL*Loader leaves indexes in an Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.
[...]The following conditions cause a direct path load to leave an index or a partition of a partitioned index in an Index Unusable state:
* SQL*Loader runs out of space for the index and cannot update the index.
* The data is not in the order specified by the SORTED INDEXES clause.
* There is an instance failure, or the Oracle shadow process fails while building the index.
* There are duplicate keys in a unique index.
* Data savepoints are being used, and the load fails or is terminated by a keyboard interrupt after a data savepoint occurred.
|
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
