Oracle Database Discussions

Hi,

What is the jobs type ? sql*loader indirect mode can produce unusable indexes.

Can you provide more details on your processes ?

Rgds

Are these indexes on partitioned tables? Partition maintenance operations can make indexes unusable.

If the index became unusable as a result of ALTER INDEX ... UNUSABLE operation, then LAST_DDL_TIME in DBA_OBJECTS will be updated. Otherwise, if it is a result of operations on the underlying table, that column will not be updated, and I don't think there is a way to find out exact time when the index became unusable.

This would have happened when there is a ALTER tabe Move option or there might jobs running through sqlloader

Anything happening like this

Index become unusable when the rowid is changing

The rowid can change when the table is move or shrinked, for example

are you doing some job like this on the table ? did you enable row movement ?

Hi Jafar,
Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild.
Don't do anything (reorgs) that will cause a change in a rows ROWID.
Yeah, as "unusable", they are not available.
You can rebuild them super-fast in parallel, if you have an SMP server.

Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference":
http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm

Hi,


Thanks all for your inputs. So index become unsuable when we issue

1) enable row movement.
2) shrinking the indices.
3) alter table move.
4) During loading through sqlldr.

Now out of the above four options we exercise only one i.e. shrinking the indices to relciam some space, but we shrink other indices also that doesn't become unusable then why only selected indices become unusable.

And one more thing, if there is job running which loads data into table then thier respective indices become unusable, so what we have to do so that indices stays valid thorugh loading.

Thanks

Jafar

www.idatamax.com

I do not understand what "shrink indices" is.

A Rebuild of an Index does not cause it to be UNUSABLE.

Similarly, an ALTER TABLE .. SHRINK SPACE shoud not cause the indexes on the table to be UNUSABLE.

An SQLloader DIRECT=Y option makes indexes UNUSABLE and leaves the indexes UNUSABLE if the load fails. Else, the load would merge the new entries indexes as well. That is similar to an APPEND insert which updates the indexes at the end of the operation.

The one operation that will always leave indexes UNUSABLE is an ALTER TABLE .. MOVE as there is no option to REBUILD Indexes automatically with the move. Indexes must be rebuilt as part of the job/script that does the MOVE.

Hi,

Thanks that was helpful, so to keep indices unusable during sqlldr operations we should issue direct=y option, and i know it would slow the loading operation. What is the difference between index in invalid status and index in unusable state.

Thanks

Jafar

www.idatamax.com