Demonstation of performance gain using indexes

SQL> set autotrace on
SQL> select COUNT(*) from CALC_DTL WHERE user_id='ADM09';

  COUNT(*)
----------
     22425


Execution Plan
----------------------------------------------------------
Plan hash value: 1448036659

--------------------------------------------------------------------------------
-------------------

| Id  | Operation          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                              |     1 |     9 | 58989   (1)| 00:11:48 |

|   1 |  SORT AGGREGATE    |                              |     1 |     9 |       |          |

|*  2 |   TABLE ACCESS FULL| CALC_DTL |   170K|  1502K| 58989   (1)| 00:11:48 |

---------------------------------------------------------------------------------------------------


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

   2 - filter("USER_ID"='ADM09')


Statistics
----------------------------------------------------------
      14604  recursive calls
          0  db block gets
     223238  consistent gets
     221950  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select COUNT(*) from CALC_DTL WHERE cmp_num='45';

  COUNT(*)
----------
  10113484


Execution Plan
----------------------------------------------------------
Plan hash value: 3988562582

---------------------------------------------------------------------------------------------------

| Id  | Operation             | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                           |     1 |     3 | 39241   (1)| 00:07:51 |

|   1 |  SORT AGGREGATE       |                           |     1 |     3 |       |          |

|*  2 |   INDEX FAST FULL SCAN| PK_CALC_DTL |  9718K|    27M| 39241   (1)| 00:07:51 |

---------------------------------------------------------------------------------------------------


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

   2 - filter("CMP_NUM"=45)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
     155453  consistent gets
     155438  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>