This will work in Oracle 11g
An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint. Applications often have to be modified without being able to bring the complete Application offline. Create invisible indexes temporarily for specialized non-standard operations, such as online application upgrades, without affecting the behavior of any existing application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.
Adding an Index Without Impacting Existing Applications Problem You know from experience that sometimes when an index is added to a third-party application, this can cause performance issues and also can be a violation of the support agreement with the vendor. You want to implement an index in such a way that the application won’t ever use the index.
Solution Often, third-party vendors don’t support customers adding their own indexes to an application.However, there may be a scenario in which you’re certain you can increase a query’s performance without impacting other queries in the application. You can create the index as invisible and then explicitly instruct a query to use the index via a hint—for example:
SQL> create index emp_idx1 on emp(empno) invisible
;Next, ensure that the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter is set to TRUE (the default is FALSE). This instructs the optimizer to consider invisible indexes:
SQL> alter system set optimizer_use_invisible_indexes=true;
Now, use a hint to tell the optimizer that the index exists:
SQL> select /*+ index (emp emp_IDX1) */ emp_no from inv where empno=1;
You can verify that the index is being used by setting AUTOTRACE TRACE EXPLAIN and running theSELECT statement:SQL> set autotrace trace explain;
SQL> select /*+ index (emp_IDX1) */empno from emp where empno=1;
Keep in mind that an invisible index means only that the optimizer can’t see the index. Just like any other index, an invisible index consumes space and resources when executing DML statements.How it works
In Oracle Database 11g and higher, you have the option of making an index invisible to the optimizer.
Oracle still maintains invisible indexes but doesn’t make them available for use by the optimizer. If you want the optimizer to use an invisible index, you can do so with a SQL hint. Invisible indexes have a
couple of interesting uses
• You can add an invisible index to a third-party application without affecting
existing code or support agreements. • Altering an index to invisible before dropping it allows you to quickly recover if you later determine that the index is required.
The first bulleted item was discussed in the “Solution” section of this recipe.
The second scenario is discussed in this section. For example, suppose you’ve identified an index that isn’t being used and are considering dropping it. In earlier releases of Oracle, you could mark the index as UNUSABLE and then later drop indexes that you were certain weren’t being used.
If you later determined that you needed an unusable index, the only way to re-enable the index was to rebuild it. For large indexes, this could take a long time and consume considerable database resources. Making an index invisible has the advantage that it tells the optimizer only to not use the index.
The invisible index is still maintained as the underlying table has records inserted, updated, or deleted. If you decide that you later need the index, there is no need to rebuild it. You simply have to mark it as visible again—
for example: SQL> alter index emp_idx1 visible;
You can verify the visibility of an index via this query:
SQL> select index_name, status, visibility from user_indexes;
Here’s some sample output:
emp_IDX1 VALID VISIBLE
