Objective : How to make an index invisible so that you can test the effect on your queries without actually dropping the index.
Step1: Connect as 'SYS' user and flush caches
SQL> connect sys/welcome1@xdbvm as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
Step2: Connect as 'SH' user and check the indexes status and make the index 'INVISIBLE'
SQL> connect sh/welcome1@xdbvm
Connected.
SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';
INDEX_NAME STATUS VISIBILITY
------------------------------ -------- -------------
CUSTOMERS_PK VALID VISIBLE
SQL> alter index CUSTOMERS_PK invisible;
Index altered.
SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';
INDEX_NAME STATUS VISIBILITY
------------------------------ -------- ------------
CUSTOMERS_PK VALID INVISIBLE
Step3: Execute the following Query after making index 'INVISIBLE'
SQL> set autotrace on explain
SQL> select avg(cust_credit_limit) from customers where cust_id between 2000 and 2500;
AVG(CUST_CREDIT_LIMIT)
----------------------
5996.80639
Elapsed: 00:00:01.59
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 405 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS STORAGE FULL| CUSTOMERS | 268 | 2412 | 405 (1)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("CUST_ID"<=2500 AND "CUST_ID">=2000)
filter("CUST_ID"<=2500 AND "CUST_ID">=2000)
Note: Oracle Exadata used 'Smart Scan' feature rather than an Index Range Scan.
Step4: Connect as 'SYS' user and flush caches
SQL> connect sys/welcome1@xdbvm as sysdba
Connected.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
Step5: Connect as 'SH' user and check the indexes status and make the index 'VISIBLE'
SQL> connect sh/welcome1@xdbvm
Connected.
SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';
INDEX_NAME STATUS VISIBILITY
------------------------------ -------- -------------
CUSTOMERS_PK VALID INVISIBLE
SQL> alter index CUSTOMERS_PK visible;
Index altered.
SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';
INDEX_NAME STATUS VISIBILITY
------------------------------ -------- -------------
CUSTOMERS_PK VALID VISIBLE
Step6: Execute the following Query after making index 'VISIBLE'
SQL> set autotrace on explain
SQL> select avg(cust_credit_limit) from customers where cust_id between 2000 and 2500;
AVG(CUST_CREDIT_LIMIT)
----------------------
5996.80639
Elapsed: 00:00:02.44
Execution Plan
----------------------------------------------------------
Plan hash value: 3995619262
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 265 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 268 | 2412 | 265 (0)| 00:00:04 |
|* 3 | INDEX RANGE SCAN | CUSTOMERS_PK | 268 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CUST_ID">=2000 AND "CUST_ID"<=2500)
Note: This above has used an Index Range Scan instead of Oracle Exadata 'Smart Scan' feature.
Note: Before dropping the Indexes, check the application nature and execution scenarios using 'INVISIBLE' index.
