title: Oracle Index Usage in View with Aggregates
body: <p>Here's the background:</p>
  
  <p>Version:
  Oracle 8i (Don't hate me for being out of date.  We are upgrading!)</p>
  
  <pre><code>SQL&gt; describe idcpdata
  Name                                      Null?    Type
  ----------------------------------------- -------- ---------------------------
  
  ID                                        NOT NULL NUMBER(9)
  DAY                                       NOT NULL DATE
  STONE                                              NUMBER(9,3)
  SIMPSON                                            NUMBER(9,3)
  OXYCHEM                                            NUMBER(9,3)
  PRAXAIR                                            NUMBER(9,3)
  </code></pre>
  
  <p>Here's a query that returns right away:</p>
  
  <pre><code>SQL&gt; select to_char(trunc(day,'HH'),'DD-MON-YYYY HH24') day,
  2  avg(decode(stone,-9999,null,stone)) stone,
  3  avg(decode(simpson,-9999,null,simpson)) simpson,
  4  avg(decode(oxychem,-9999,null,oxychem)) oxychem,
  5  avg(decode(praxair,-9999,null,praxair)) praxair
  6  from IDcpdata
  7  where day between
  8  to_date('14-jun-2009 0','dd-mon-yyyy hh24') and
  9  to_date('14-jun-2009 13','dd-mon-yyyy hh24')
  10  group by trunc(day,'HH');
  </code></pre>
  
  <p>When I create a view based on that query, just without the where clause, a query against that view, with the where clause, fails to use the view.  There is a highly selective index which IS used in the direct SQL query version.  A full table scan takes 20 minutes.</p>
  
  <pre><code>create or replace view theview as
  select TRUNC(day,'HH') day, 
  avg(decode(stone,-9999,null,stone)) stone, 
  avg(decode(simpson,-9999,null,simpson)) simpson, 
  avg(decode(oxychem,-9999,null,oxychem)) oxychem, 
  avg(decode(praxair,-9999,null,praxair)) praxair 
  from IDcpdata group by TRUNC(day,'HH');
  
  
  SQL&gt; select * from theview
  2  where day between
  3  to_date('14-jun-2009 0','dd-mon-yyyy hh24') and
  4  to_date('14-jun-2009 13','dd-mon-yyyy hh24');
  </code></pre>
  
  <p>I tried INDEX() hints in the view, the query and both.  I tried global INDEX hint, specifying the fully qualified name of the underlying table.  I also tried MERGE.</p>
  
  <p>It seems to me that Oracle should be able to use the index, since inline SQL does.  I just can't figure out how to force it to.  I'm sure it's me, not Oracle, I am just not seeing it.</p>
  
  <p>Thanks in advance for any suggestions!</p>