Oracle 11g Invisible Indexes.

With Oracle 11g you can turn any index invisible with this feature.

When you turn a index invisible the optimizer cannot see it and therefore
the executions plans are generated without the use of index in the plans.

This feature is helpful when you want to check whether a table is better without its
index. Instead of dropping the index just make it invisible and check for performance
benifits. If you want the index back just make it visible.

SQL> conn scott/tiger

SQL> create table test (id int);

Table created.

SQL> create index test_id on test(id);

Index created.

SQL> select index_name, visibility from user_indexes where index_name = 'TEST_ID';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_ID                        VISIBLE

SQL> alter index test_id invisible;

Index altered.

SQL> select index_name, visibility from user_indexes where index_name = 'TEST_ID';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_ID                        INVISIBLE

SQL> alter index test_id visible;

Index altered.

SQL> select index_name, visibility from user_indexes where index_name = 'TEST_ID';


INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_ID                        VISIBLE