Использование Oracle Index в представлении с агрегатами

Совет построить индекс на основе функций ON IDcpdata (TRUNC(day, 'HH')) является разумным. Есть ли у вас другие функциональные индексы? Если нет, это может объяснить, почему оптимизатор его не использует.

Этот тип индекса был представлен в версии 8i, и, следовательно, тогда его реализация была немного неуклюжей. В частности, вам нужно установить некоторые параметры базы данных, иначе оптимизатор проигнорирует индекс.

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

Я думаю, что вам также нужно ВЫЧИСЛИТЬ СТАТИСТИКУ в 8i.

(Я в долгу перед Google и Тимом Холлом за сайт Oracle-Base, который заменял мою плохую память).

Ваш запрос просмотра фильтрует TRUNC(day,'HH'), а не day.

Поскольку вы определили, что ваше представление возвращает TRUNC(day,'HH') AS day, это усеченное значение дня, к которому применяется предложение BETWEEN, и оно не подлежит анализу.

Создайте индекс для TRUNC(day, 'HH'):

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

Обновление:

Это работает на моем 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

В первом случае «день» в предложении WHERE ссылается на столбец таблицы «день», а не на столбец результатов запроса «день», поэтому можно использовать индекс, но результаты не включают данные за 14 июня 2009 г. 13 :00:01 и далее.

Во втором случае «день» в предложении WHERE ссылается на столбец представления «день», который определяется как TRUNC (день, «ЧЧ»). Таким образом, он не может использовать индекс и включает данные за 14 июня 2009 г. 13:00:01 и далее, т. е. два запроса не эквивалентны.

Вы можете надеяться достичь лучшего из обоих подходов, например:

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

Однако, как указано в комментариях ниже, это не удается, поскольку день столбца не указан в предложении GROUP BY.

Поэтому, как уже предлагали другие, лучше всего придерживаться исходного представления и запроса и добавить индекс на основе функций (FBI) следующим образом:

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