Index Elimination with Oracle Exadata

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.