Oracle
introduced INVISIBLE indexes in version 11. The example below creates
an INVISIBLE index, makes it VISIBLE then makes it INVISIBLE again. The
VISIBILITY column in USER_INDEXES is used to check that each change has
worked:
SQL> col visibility format a10
SQL> create table my_table
2 as select * from dba_tables
3 /
Table created.
SQL> create index my_index
2 on my_table(owner)
3 invisible
4 /
Index created.
SQL> select visibility
2 from user_indexes
3 where index_name = 'MY_INDEX'
4 /
VISIBILITY
----------
INVISIBLE
SQL> alter index my_index visible
2 /
Index altered.
SQL> select visibility
2 from user_indexes
3 where index_name = 'MY_INDEX'
4 /
VISIBILITY
----------
VISIBLE
SQL> alter index my_index invisible
2 /
Index altered.
SQL> select visibility
2 from user_indexes
3 where index_name = 'MY_INDEX'
4 /
VISIBILITY
----------
INVISIBLE
SQL>
********************
When
an index is INVISIBLE, it is still updated by DML but it is ignored by
the optimizer. You can make an index INVISIBLE and see if this has a
detrimental effect on an application’s performance. If it does not then
you can consider dropping it.
In the example below, an index is created and used by a SELECT statement.
The index is made INVISIBLE and the SELECT statement is repeated. This time it does not use the index:
SQL> col visibility format a10
SQL> create table my_table
2 as select * from dba_tables
3 /
Table created.
SQL> create index my_index
2 on my_table(owner)
3 /
Index created.
SQL> set autotrace on
SQL> select count(*) from my_table
2 where owner = 'SYSTEM'
3 /
COUNT(*)
----------
154
Execution Plan
----------------------------------------------------------
Plan hash value: 4077732364
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX RANGE SCAN| MY_INDEX | 154 | 2618 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYSTEM')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
68 consistent gets
1 physical reads
0 redo size
523 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter index my_index invisible
2 /
Index altered.
SQL> select count(*) from my_table
2 where owner = 'SYSTEM'
3 /
COUNT(*)
----------
154
Execution Plan
----------------------------------------------------------
Plan hash value: 228900979
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 6 (17)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| MY_TABLE | 152 | 2584 | 6 (17)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYSTEM')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
253 recursive calls
0 db block gets
164 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
No comments:
Post a Comment