Having function based index on trunc(actual_bill_dtm).


select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   trunc(actual_bill_dtm) > DATE '2015-12-08'

Explain Plan:

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |   114K|    26M| 18691   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BILLSUMMARY            |   114K|    26M| 18691   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | BILLSUMMARY_KPI_OPT_IX |   114K|       | 17853   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   trunc(actual_bill_dtm) > DATE '2015-12-08'

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm > DATE '2015-12-08'

select *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm > DATE '2015-12-08'

Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm >= DATE '2015-12-09'