ignoring index in aggregate query

  1* create table t7(c1 date,c2 number,c3 varchar2(100))
SQL> /
Table created.
SQL> insert into t7 select sysdate,mod(level,5),mod(level,10000) from dual connect by level<=100000;

100000 rows created.
SQL> insert into t7 select sysdate-1,mod(level,5),mod(level,10000) from dual connect by level<=10000
;
10000 rows created. 

create index i_idx on t7(c1);
index created

select TRUNC(c1, 'HH') c1 from t7
  where c1  between sysdate-5 and sysdate-1
  group by TRUNC(c1, 'HH')
   
SQL> /
C1
---------
08-JUL-13
09-JUL-13

  1  create view v1 as
  2  select TRUNC(c1, 'HH') vc1 ,count(*) vc2 from t7
  3  --where c1  between sysdate-2 and sysdate-1
  4* group by TRUNC(c1, 'HH')
SQL> /
View created.

SQL> conn scott/tiger@orcl11g
Connected.
SQL> set autot on
SQL> select * from v1 where vc1 between sysdate-2 and sysdate-1;
VC1              VC2
--------- ----------
09-JUL-13      11000

Execution Plan
----------------------------------------------------------
Plan hash value: 1490029489
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 12762 |   112K|   289   (6)| 00:00:04 |
|   1 |  HASH GROUP BY      |      | 12762 |   112K|   289   (6)| 00:00:04 |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T7   | 12762 |   112K|   288   (6)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   3 - filter(TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')>=SYSDATE@!-2 AND
              TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')<=SYSDATE@!-1)
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        931  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> exec dbms_stats.gather_table_stats(user,'t7',estimate_percent=>null,method_opt=>'for all column
s size 254',cascade=>true);
PL/SQL procedure successfully completed.


  1   select TRUNC(c1, 'HH') vc1 ,count(*) vc2 from t7
  2   where c1  between sysdate-2 and sysdate-1
  3*  group by TRUNC(c1, 'HH')
SQL> /
VC1              VC2
--------- ----------
09-JUL-13      11000

Execution Plan
----------------------------------------------------------
Plan hash value: 3216186273
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     8 |    45   (3)| 00:00:01 |
|   1 |  HASH GROUP BY     |       |     1 |     8 |    45   (3)| 00:00:01 |
|*  2 |   FILTER           |       |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| I_IDX | 11000 | 88000 |    44   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   3 - access("C1">=SYSDATE@!-2 AND "C1"<=SYSDATE@!-1)

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

REMEDY:

SQL> create index i_idx2 on t7(TRUNC(c1, 'HH'));
Index created.
Elapsed: 00:00:08.69
SQL> select * from v1 where vc1 between sysdate-2 and sysdate-1;
VC1              VC2
--------- ----------
09-JUL-13      11000
Elapsed: 00:00:00.19
SQL> set autot on
SQL> set lines 300 pages 80
SQL> /
VC1              VC2
--------- ----------
09-JUL-13      11000
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 3507030160
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     5 |    40 |    12   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                |        |     5 |    40 |    12   (9)| 00:00:01 |
|*  2 |   FILTER                      |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T7     |   778 |  6224 |    11   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | I_IDX2 |  1400 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   4 - access(TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')>=SYSDATE@!-2 AND
              TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')<=SYSDATE@!-1)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed