Oracle Index Usage in View with Aggregates

Question

Here's the background:

Version: Oracle 8i (Don't hate me for being out of date. We are upgrading!)

SQL> 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)

Here's a query that returns right away:

SQL> 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');

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.

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> 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');

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.

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.

Thanks in advance for any suggestions!

Answer 1

Your view query filters on TRUNC(day,'HH'), not on day.

Since you defined your view to return TRUNC(day,'HH') AS day, it's the truncated day value the BETWEEN clause is applied to, and it's not sargable.

Create an index on TRUNC(day, 'HH'):

CREATE INDEX ix_idcpdata_truncday ON IDcpdata (TRUNC(day, 'HH'))

Update:

This works on my Oracle 10g XE:

CREATE TABLE t_group (id INT NOT NULL PRIMARY KEY, day DATE NOT NULL)
/

INSERT
INTO    t_group
SELECT  level, TRUNC(SYSDATE) - level
FROM    dual
CONNECT BY
        level <= 100
/

CREATE INDEX ix_group_truncday ON t_group (TRUNC(day, 'HH'))
/

CREATE VIEW v_group AS
SELECT  TRUNC(day, 'HH') AS day
FROM    t_group
GROUP BY
        TRUNC(day, 'HH')
/

EXPLAIN PLAN FOR
SELECT  *
FROM    v_group
WHERE   day BETWEEN TO_DATE('01.08.2009', 'dd.mm.yyyy') AND TO_DATE('02.08.2009', 'dd.mm.yyyy')
/

SELECT  *
FROM    TABLE(DBMS_XPLAN.display)
/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1656741214
--------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |     9 |     2
|   1 |  HASH GROUP BY               |                   |     1 |     9 |     2
|   2 |   TABLE ACCESS BY INDEX ROWID| T_GROUP           |     1 |     9 |     1
|*  3 |    INDEX RANGE SCAN          | IX_GROUP_TRUNCDAY |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(TRUNC(INTERNAL_FUNCTION("DAY"),'fmhh')>=TO_DATE('2009-08-01 00:00:
              'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("DAY"),'fmhh'
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

17 rows selected

Answer 2

In the first case , the "day" in the WHERE clause references the table column "day", not the query result column "day", so the index can be used but the results do not include data for 14-jun-2009 13:00:01 onwards.

In the second case, the "day" in the WHERE clause references the view column "day", which is defined as TRUNC(day,'HH'). So this cannot use the index and does include data for 14-jun-2009 13:00:01 onwards - i.e. the 2 queries are not equivalent.

You might hope achieve the best of both approaches like this:

create or replace view theview as
select day,
TRUNC(day,'HH') trunc_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> select trunc_day, stone, simpson, oxychem, pracair
2  from theview
3  where day >= to_date('14-jun-2009 0','dd-mon-yyyy hh24')
4  and day < to_date('14-jun-2009 13','dd-mon-yyyy hh24');

However, as comments below point out, this fails because column day isn't in the GROUP BY clause.

Therefore, as others have already suggested, it is best to stick with the original view and query, and add a function based index (FBI) like this:

create index IDcpdata_truncday_idx ON IDcpdata (TRUNC(day,'HH'));

Answer 3

The advice to build a function-based index ON IDcpdata (TRUNC(day, 'HH')) is sound. Do you have other function-based indexes? If not, that might explain why the optimizer doesn't use it.

This index type was introduced in 8i, and consequently the implementation was a bit clunkier then. Specifically you need to set some database parameters, otherwise the optimizer ignores the index.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

I think you also need to COMPUTE STATISTICS in 8i.

(I am indebted to Google and Tim Hall's Oracle-Base site which deputized for my failing memory).