Index unusable|disable|enable

Source: Internet
Author: User
Tags create index documentation

Oracle makes index unavailable:
Alter index index_name unusable;
After successful execution, if you need to use the index later, you must rebuild it. It will automatically become usable when rebuilt.
According to Oracle's official documentation (an unusable index must is rebulit, or dropped and re-created, before it can be used.)
There are two ways of rebuilding
1. Rebuild
Alter index INDEX_NAME rebuild;
2. Drop the index and rebuild it.
Drop index index_name;
CREATE index index_name on xxxxx;
In fact, the results of the two operations are the same, are deleted and then re-enabled, but the Rebulid method is faster and simpler.

In addition, the database also has two kinds of statements to modify the index state, called Disable and enable; 1. Enable index
Alter index index_name enable;
2. Disable Index
Alter index index_name disable;


The difference is: Enable and disable are only for function indexes.
Oracle's official documentation provides the following statement:

ENABLE Clause

Enable applies only to a function-based index this has been disabled because a user-defined function used by the index is Dropped or replaced. This clause enables such a index if these conditions are true:

The function is currently valid

The signature of the current function matches the signature's function when the index is created

The function is currently marked asdeterministic

Restriction on enabling function-based Indexes You cannot specify no other clauses of ALTER INDEX in the same statement W ITH ENABLE.

DISABLE Clause

DISABLE applies only to a function-based index. This clause lets your disable the use of a function-based index. You are might want to does so, for example, while working on the body of the function. Afterward can either rebuild the index or specify another ALTER index statement with the ENABLE keyword.

Unusable Clause

Specify unusable to mark the index or index partition (s) or index subpartition (s) unusable. An unusable index must is rebuilt, or dropped and re-created, before it can be used. While one partition is marked unusable, the other partitions of the index are still. Can execute statements that require the index if the statements does not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.

Restriction on marking Indexes unusable your cannot specify this clause to a temporary table.

If you find that an index is invalidated, use the Enable command for it, which may cause ORA-02243 errors, since enable is only valid for function indexes, you can try rebuild, and you may encounter this error if you perform an expiration command on an index, for the same reason.
So, you can change your command.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.