Forum Stats

  • 3,854,309 Users
  • 2,264,351 Discussions
  • 7,905,642 Comments

Discussions

Categories

Unusable Indexes

Jaffy
Jaffy Member Posts: 1,234
edited Sep 3, 2008 11:48PM in General Database Discussions
Hi,

I have been experiencing some indexes being unusable due to which queries and jobs taking much longer to finish. As it is very big indices its a big overhead to rebuild those indices on time, so please can anyone help here what may be cause of indices being unusable how to resolve it and factors should we look into to avoid the problem in near future.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 on Solaris.

Thanks and Regards

Jafar

http://www.oracledbarea.blogspot.com
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 408327
    408327 Member Posts: 130
    Hi,

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

    Can you provide more details on your processes ?

    Rgds
    · Share on TwitterShare on Facebook
  • 521217
    521217 Member Posts: 845
    Are these indexes on partitioned tables? Partition maintenance operations can make indexes unusable.
    · Share on TwitterShare on Facebook
  • Jaffy
    Jaffy Member Posts: 1,234
    hi,

    no they are not partitioned indices, or can we find when the index was unsable, exact time possible.

    thanks

    Jafar
    · Share on TwitterShare on Facebook
  • 521217
    521217 Member Posts: 845
    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.
    · Share on TwitterShare on Facebook
  • Maran Viswarayar
    Maran Viswarayar Member Posts: 6,130
    This would have happened when there is a ALTER tabe Move option or there might jobs running through sqlloader

    Anything happening like this
    · Share on TwitterShare on Facebook
  • 595286
    595286 Member Posts: 243
    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 ?
    595286
    · Share on TwitterShare on Facebook
  • 108476
    108476 Member Posts: 2,184
    Hi Jafar,
    can anyone help here what may be cause of indices being unusable how to resolve it and
    Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild.
    factors should we look into to avoid the problem in near future.
    Don't do anything (reorgs) that will cause a change in a rows ROWID.
    I have been experiencing some indexes being unusable due to which queries and jobs taking much longer to finish.
    Yeah, as "unusable", they are not available.
    As it is very big indices its a big overhead to rebuild those indices on time
    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
    · Share on TwitterShare on Facebook
  • Jaffy
    Jaffy Member Posts: 1,234
    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
    · Share on TwitterShare on Facebook
  • 26741
    26741 Member Posts: 4,923 Gold Trophy
    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.
    · Share on TwitterShare on Facebook
  • Jaffy
    Jaffy Member Posts: 1,234
    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
    · Share on TwitterShare on Facebook
This discussion has been closed.