What is the difference between Oracle index unusable and invisible? unusableinvisible

Source: Internet
Author: User

What is the difference between Oracle index unusable and invisible? unusableinvisible

Extracted from the 11g Official Document:

UNUSABLESpecify UNUSABLE to mark the index or index partition (s) or index subpartition (s) UNUSABLE. the space allocated for an index or index partition or subpartition is freed immediately when the object is marked UNUSABLE. an unusable index must be 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 valid. you can execute statements that require the index if the statements do not access the unusable partition. you can also split or rename the unusable partition before rebuilding it. refer to create index... UNUSABLE for more information.

An index that is not maintained by DML operations and is ignored by the optimizer. All indexes are usable (default) or unusable.

VISIBLE | INVISIBLEUse this clause to specify whether the index is visible or invisible to the optimizer. an invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

Unusable index is ignored by the optimizer and not maintained by dml operations. If the index is unusable, it needs to be rebuilt.

Invisible index is ignored by the optimizer, but the dml operation still maintains the index. If you use the OPTIMIZER_USE_INVISIBLE_INDEXES = true parameter at the session or system level, the optimizer will consider using invisible index. It is applicable to checking whether the index is being used.


How Does oracle check the index?

Script: Lists invalid indexes or index partitions.
Www.askmaclean.com/...n.html
 
Can indexes be disabled for oracle 10 Gb? If this option can be disabled, how can I enable disabled indexes?

Alter index UR_INDEX_NAME UNUSABLE;
Alter index UR_INDEX_NAME REBUILD;

Related Article

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.