New Features of Oracle 11g-Description of Invisible Indexes (Invisible Indexes)

1. Description of Invisible Indexes

Oracle can create invisible indexes starting from version 11g. The OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter is explicitly set to TRUE, unless invisible indexes are ignored. The default value of this parameter is FALSE.

Making the index invisible is an alternative to making the index unavailable or deleting the index. With an invisible index, you can do the following:

(1) Before deleting an index, test the deletion of the index.

(2) Use a temporary index structure for specific operations or modules of the application, so that the entire application will not be affected.

Note:

Unlike unavailable indexes, invisible indexes are maintained during DML statements.

If the index is invisible, the scheduler generated by the optimizer will not use the index. If no performance degradation is found, you can delete the index. You can also create an initially invisible index, perform a test, and then determine whether to make the index visible.

You can query the VISIBILITY column in the * _ INDEXES data dictionary view to determine whether the index is VISIBLE or INVISIBLE.

SQL> select visibility from dba_indexes where index_name = 'idx _ id ';

VISIBILIT

---------

VISIBLE

 

 

-- Create an invisible index:

Create index index_name ONtable_name (column_name) INVISIBLE;

 

-- Modify whether the index is visible:

Alter index index_name INVISIBLE;

Alter index index_name VISIBLE;

 

 

Ii. Example
 

-- Create a table, index, and collect statistics:

 

SQL> create table dave (id number );

 

Table created.

 

SQL> begin

2 for I in 1 .. 10000 loop

3 insert into DAVE values (I );

4 end loop;

5 commit;

6 end;

7/

 

PL/SQL procedure successfully completed.

 

SQL> create index idx_id on dave (id) invisible;

 

Index created.

 

SQL> execdbms_stats.gather_table_stats (ownname => '& owner', tabname =>' & tablename', estimate_percent => & est_per, method_opt => 'forall columns size 1 ', degree => & degree, cascade => true );

Enter value for owner: sys

Enter value for tablename: dave

Enter value for est_per: 50

Enter value for degree: 2

 

PL/SQL procedure successfully completed.

 

SQL>

1 2 Next Page