Home »

An Index or Disaster, You Choose (It’s The End Of The World As We Know It)

Start here

Richard Foote's Oracle Blog

This come up recently with a customer and I saw a related question on OTN that made me realize I haven’t previously discussed the SKIP_UNUSABLE_INDEXES parameter.

Let me start by making a very simple point. All it potentially takes to make your entire database effectively inoperableor indeed to maybe bring down a RAC instance is for one teeny weeny index to become unusable. That’s it.

If the unusable/missing index is used often enough on a table that is big enough, the resultant explosion of load on the database generate by 10,000s of (say) massive Full Table Scans can be catastrophic on all users of the database (or indeed on all users of the disk subsystem or database servers as in the case of my customer).

Sometimes the lesser evil would be to simply ensure the statement(s) that were reliant on the now unusable index are prevented from executing and causing general havoc…

View original post 419 more words

Advertisements

1 Comment

  1. venushuynh says:

    Disable an Oracle index

    Question: I want to disable an Oracle index. What is the syntax to disable an index?

    Answer: First, why do you want to disable an index? If you are doing batch loads, the proper procedure is to drop and then re-create the index.

    In most releases, Oracle allows you to disable an index ONLY if it is a function-based index! For non-function-based indexes, you disable the index by marking the index as unusable. You can disable a function-based index with this command:

    SQL> create index my_fbi on mytab (upper(emp_name));
    SQL> alter index my_fbi disable

    You can mark an index as unusable with this command:

    SQL > alter index my_nonfbi_index unusable;
    SQL> alter session set skip_unusable_indexes = true

    (BEWARE: Marking an index as unusable will prevent any DML against the base table!)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: