Make index invisible

If we want to test the impact of dropping the index,we can do it in an easy way by making the index invisible.Here I am going to execute a query which will utilize an existing index PRODUCTS_PROD_CAT_IX.


SQL> set autotrace on EXPLAIN

SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Peripherals and Accessories                              108.824588
Photo                                                    188.064642


Execution Plan
----------------------------------------------------------
Plan hash value: 1197568639

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY           |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|*  2 |   HASH JOIN              |                      |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                  | VW_GBC_5             |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY        |                      |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL |                      |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL  | SALES                |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    VIEW                  | index$_join$_002     |    72 |  1512 |     3  (34)| 00:00:01 |       |       |
|*  8 |     HASH JOIN            |                      |       |       |            |          |       |       |
|   9 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
|  10 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="ITEM_1")
   8 - access(ROWID=ROWID)




Now lets make the index invisible and compare the difference.

SQL> alter index SH.PRODUCTS_PROD_CAT_IX invisible;

Index altered.

SQL> set autotrace on EXPLAIN
SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Photo                                                    188.064642
Peripherals and Accessories                              108.824588


Execution Plan
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY          |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|*  2 |   HASH JOIN             |          |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                 | VW_GBC_5 |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY       |          |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL|          |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    TABLE ACCESS FULL    | PRODUCTS |    72 |  1512 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="ITEM_1")

So if we compare the two explain plans ,in the second plan we are doing full table scan instead of index fast full scan and also the cost of the second explain plan is high.