Exadata: Index Elimination

Brief:

In some cases, Exadata Smart Scan may deliver better query performance than using an index. However, this may not always be the case. Even in cases where an index delivers better query performance you might choose to remove it if you determine that the un-indexed query performance is acceptable and the index is otherwise unnecessary. Removing unnecessary indexes saves space and improves DML performance by eliminating the maintenance operations associated with the index. 

Scenario:

My development team wants to be sure that application does not compromise performance after eliminating indexes on some very large tables. For testing purpose we made an index invisible so that development team can test the effect of removing the index on application queries without actually dropping the index.

Table: TRANSACTION_LOG
Non-unique index on column RECORD_ID 

Steps

1- Connect with SQLPlus session and flush the buffer cache to ensure consistent results.
SQL> set timing on
SQL> set autotrace on explain
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.30
SQL>

2- Run the below query to check index usage, observe the execution plan.
SQL> select avg(nin)
  2  from system.TRANSACTION_LOG
  3  where record_id between 19000 and 200000 ;

  AVG(NIN)
----------
109523.325

Elapsed: 00:00:11.96

Execution Plan
----------------------------------------------------------
Plan hash value: 3140749760

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    10 | 13797   (1)| 00:02:46 |
|   1 |  SORT AGGREGATE              |                 |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSACTION_LOG | 13760 |   134K| 13797   (1)| 00:02:46 |
|*  3 |    INDEX RANGE SCAN          | IDX_REC_ID      | 13760 |       |    33   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   3 - access("RECORD_ID">=19000 AND "RECORD_ID"<=200000)

3- Make the index invisible now , Please note that  if the index is associated with constraint like Primary Key it will still be enabled.

SQL> alter index IDX_REC_ID invisible;

Index altered.

Elapsed: 00:00:00.05

4- Re-execute the query . Notice that an Exadata Smart Scan is used rather than an index range scan. 

SQL> select avg(nin)
  2  from system.TRANSACTION_LOG
  3  where record_id between 19000 and 200000;

  AVG(NIN)
----------
109523.325

Elapsed: 00:00:00.26

Execution Plan
----------------------------------------------------------
Plan hash value: 1515581512

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |     1 |    10 | 18556   (1)| 00:03:43 |
|   1 |  SORT AGGREGATE            |                 |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| TRANSACTION_LOG | 13760 |   134K| 18556   (1)| 00:03:43 |
----------------------------------------------------------------------------------------------

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

   2 - storage("RECORD_ID"<=200000 AND "RECORD_ID">=19000)
       filter("RECORD_ID"<=200000 AND "RECORD_ID">=19000)

5-  Compare the time taken to execute the query with and without the index. If you decide not to remove the index, you can quickly and easily make it visible.
SQL> alter index IDX_REC_ID visible;

Index altered.