Invisible Indexes in the Oracle Database

Invisible indexes are a simple Oracle Database feature that let you test index changes safely and manage new/specialized indexing needs without impacting the normal database operations. This feature, added in Oracle 11g, gives you another way to manage indexes and improve database performance tuning.

What Are Invisible Indexes?

As stated above, Invisible indexes are regular database indexes that Oracle maintains but don't use during normal query optimization. They stay current with your data changes, but Oracle won't use them in queries unless you specifically tell it to.

Getting Started with Invisible Indexes

Let's walk through a practical example. We'll create a simple employee database and see invisible indexes in action:

-- First, let's create a table to store employee information
create table employees (
    emp_id number primary key,
    first_name varchar2(50),
    last_name varchar2(50),
    email varchar2(100)
);

-- Add some test data
insert into employees values (1, 'John', 'Smith', 'john.smith@email.com');
insert into employees values (2, 'Jane', 'Doe', 'jane.doe@email.com');
insert into employees values (3, 'Bob', 'Johnson', 'bob.j@email.com');

-- Create an invisible index
create index emp_email_idx on employees(email) invisible;

Working with Invisible Indexes

There are two main ways to work with invisible indexes:

1. Making an index visible/invisible:

-- Toggle index visibility
alter index emp_email_idx invisible;

alter index emp_email_idx visible;

2. Enabling invisible indexes for a session:

-- Allow optimizer to use invisible indexes
alter session set optimizer_use_invisible_indexes = true;

Verifying Index Usage

You can verify whether Oracle is using your invisible index by checking execution plans:

-- Check execution plan
explain plan for 
select * from employees where email = 'john.smith@email.com';

-- Display the plan
select * from table(dbms_xplan.display);

When the index is invisible, you'll see a full table scan. After making it visible or enabling invisible indexes for the session, you'll see an index scan.

Checking Index Visibility

You can view the current visibility status of your indexes:

select index_name, visibility
from user_indexes 
where table_name = 'EMPLOYEES';

Common Use Cases for Invisible Indexes

1. Index Testing: Test the impact of new indexes without affecting existing applications

2. Specialized Processing: Create indexes for specific batch jobs that shouldn't affect normal runtime

3. Index Removal Testing: Test the impact of removing an index before actually dropping it

Important things to remember

• Invisible indexes still consume storage space

• They are maintained during DML operations, affecting insert/update performance

• The OPTIMIZER_USE_INVISIBLE_INDEXES parameter affects all invisible indexes in the session

• Statistics should be gathered on invisible indexes just like regular indexes

Invisible VS Unusable, which one should I use?

By default, both Invisible and Unusable indexes are ignored by the database optimizer. They differ in the fact that invisible indexes are maintained as the underlying tables change, but the unusable index is not maintained during the same DML. This means the unusable index is a great way to speed up bulk load performance because the database doesn't have to worry about keeping that index up to date. To learn more about making an index unusable, check out the index docs.

Additional Resources

For more detailed info about invisible indexes, check the Oracle docs:

• Oracle DBA's Guide - Creating Invisible Indexes

-Killian