Oracle Illustrated

Feb 21 2010   7:36AM GMT

Migrating from 9i to 11g – Invisible indexes

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Its a new feature in Oracle 11g. It is an index available in the database but is not used by the optimizer unless hinted. This is one of the best features provided by Oracle 11g. Sometimes we confront situation after creating a new index that the intended module is working fine but some other module gets unimaginably screwed up. This invisible index comes handy when certain queries in the processes require a specific index without affecting the rest of the queries / processes. It can also be used as an alternative to dropping / disabling an index. If you want to quickly test the index for a particular query and to see how it would work with a particular index without distrubing the other processes then creating invisible index, testing and dropping the same is the best way to go. Yet another use of this index is certain programs might require index temporarily – in such cases invisible index can be created before start of the process, hinted in the query and then dropped before exiting the process.

Prior to 11g usually the index would be set to unused then it would be observed to determine whether it adversely impacts the performance before dropping the index. Now there is a option of setting it invisible against making it unused.

Query all_indexes table to check whether the index is invisible / not. Net net, if you don’t want the new index to impact the database and want only certain processes to use it then its safe to create invisible index and reference the same in the queries. However, it should be taken into note that any DML operations would write to this invisible index also – so create invisible indexes only when necessary – as DQL’s might not be affected but DML’s do gets affected.

Note: Rebuilding an invisible index will make it visible automatcially.

Oracle 9i
ALTER INDEX unusable

ALTER INDEX usable

create unique index emp1_idx1 on emp1 (empno)

set autotrace traceonly

SELECT * FROM EMP1 WHERE EMPNO = 1

ALTER INDEX emp1_idx1 unusable

Execution Plan
———————————————————-
Plan hash value: 2226897347

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMPNO”=1)

Statistics
———————————————————-
254 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1;

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1
*
ERROR at line 1:
ORA-01502: index ‘SYSTEM.EMP1_IDX1′ or partition of such index is in unusable
state

Oracle 11g
ALTER INDEX invisible

ALTER INDEX visible

create index on

() invisible

create unique index emp1_idx1 on emp1 (empno) invisible

set autotrace traceonly

SELECT * FROM EMP1 WHERE EMPNO = 1

Execution Plan
———————————————————-
Plan hash value: 2226897347

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMPNO”=1)

Statistics
———————————————————-
179 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1

Execution Plan
———————————————————-
Plan hash value: 797959521

————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP1_IDX1 | 1 | | 0 (0)| 00:00:01 |
————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPNO”=1)

Statistics
———————————————————-
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: