Инкрементальное обновление мат. вьюхи с агрегацией

samatom
Дата: 11.11.2008 15:02:36
Добрый день.
Не могу понять, то ли я что-то неправильно делаю, либо такое не поддерживается: необходимо создать мат. вью с инкрементальным обновлением, которое при обновлении не выполняет заново запрос, указанный в её теле, а добавляет информацию из вью-лога. Сама вьюха создается согласно описания в документации, но при изменении данных в исходной таблице, при её обновлении, мне говорится о том, что FAST REFRESH такое "не вытягивает" и надо делать COMPLETE REFRESH.

SQL*Plus: Release 8.0.6.0.0 - Production on Втн Ноя 11 14:40:24 2008

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Присоединен к:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> CREATE TABLE TABLE1 AS 
  2  (SELECT O.OBJECT_ID, O.OBJECT_NAME, O.OWNER,O.OBJECT_TYPE FROM ALL_OBJECTS O);

Таблица создана.

SQL> CREATE MATERIALIZED VIEW LOG ON TABLE1 WITH ROWID
  2  (OBJECT_ID, OBJECT_NAME, OWNER, OBJECT_TYPE)
  3  INCLUDING NEW VALUES;

Журнал моментальной копии создан.

SQL> CREATE MATERIALIZED VIEW MV
  2  BUILD IMMEDIATE
  3  REFRESH FAST ON DEMAND
  4  AS
  5  SELECT OWNER,OBJECT_TYPE,SUM(OBJECT_ID) SM
  6  FROM TABLE1
  7  WHERE OWNER='SYS'
  8  GROUP BY OWNER,OBJECT_TYPE;

Моментальная копия создана.

SQL> SELECT * FROM MV;

OWNER                          OBJECT_TYPE               SM
------------------------------ ------------------ ---------
SYS                            TYPE                 1966101
SYS                            VIEW               129471001
SYS                            INDEX               24140430
SYS                            TABLE               44180559
SYS                            CONTEXT                 6284
SYS                            LIBRARY               268521
SYS                            PACKAGE             35913893
SYS                            SYNONYM                23703
SYS                            TRIGGER             14884176
SYS                            FUNCTION             8920603
SYS                            OPERATOR                9694
SYS                            SEQUENCE              101989
SYS                            PROCEDURE             105400
SYS                            TYPE BODY              94528
SYS                            PACKAGE BODY        35892174
SYS                            CONSUMER GROUP          7752
SYS                            EVALUATION CONTEXT      5908

17 строк выбрано.

SQL> update table1 set owner = 'SYS1' where owner = 'SYS';

4066 строк обновлено.

SQL> EXEC DBMS_MVIEW.REFRESH ('MV');
BEGIN DBMS_MVIEW.REFRESH ('MV'); END;

*
ошибка в строке 1:
ORA-32314: REFRESH FAST of "IGG"."MV" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

SQL> 

ORA-32314 REFRESH FAST of "string"."string" unsupported after deletes/updates

Cause: One or more deletions or updates has been performed on one or more of the detail tables referenced by the specified materialized view. This materialized view does not support fast refresh after deletions or updates.

Action: Use REFRESH COMPLETE. You can determine why your materialized view does not support fast refresh after deletions or updates using the DBMS_MVIEW.EXPLAIN_MV() API.


Если создать вьюху без агрегации, с простым условием-фильтром, то обновление идёт методом FAST REFRESH. Подскажите, плиз, если кто сталкивался с таким, в чем может быть дело? Жизненно необходимо FAST REFRESH, иначе сама мат. вьха теряет смысл - в моей реально задаче она будет обновляться COMPLETE минут 10-20, а хотелось бы обновляь её за 10-20 секунд.
orawish
Дата: 11.11.2008 15:08:54
полное впечатление, что вы считаете будто булки на деревьях растут
alecsey
Дата: 11.11.2008 15:08:54
нельзя такого
samatom
Дата: 11.11.2008 15:20:25
Спасибо большое.
tru55
Дата: 11.11.2008 15:20:59
Да елки...
Уже который вопрос на эту тему. Такое впечатление, что доку народ принципиально не читает

Data Warehousing Guide

Restrictions on Fast Refresh on Materialized Views with Aggregates
samatom
Дата: 11.11.2008 15:28:12
Читал я... Сорри, видимо с первого раза не понял -
Restrictions on Fast Refresh on Materialized Views with Aggregates
...
If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

   Materialized views with MIN or MAX aggregates
   Materialized views which have SUM(expr) but no COUNT(expr)
   Materialized views without COUNT(*)

Such a materialized view is called an insert-only materialized view.
..
samatom
Дата: 11.11.2008 15:28:56
И не удалось найти подобных тем на форуме, поэтому писал. Спасибо.
tru55
Дата: 11.11.2008 15:35:27
samatom
И не удалось найти подобных тем на форуме, поэтому писал. Спасибо.


:)
http://www.sql.ru/forum/actualthread.aspx?tid=508245&hl=insert+only+materialized+view#5074950
samatom
Дата: 11.11.2008 16:22:08
[url=http://docs.tpu.ru/docs/oracle/en/od/10.2.0.0.1/B19306_01/server.102/b14223/basicmv.htm#g1014280
]замечания[/url]

Согласно этих рекомендаций получилось сделать!

CREATE MATERIALIZED VIEW LOG ON TABLE1 WITH SEQUENCE, ROWID
(OBJECT_ID, OBJECT_NAME, OWNER, OBJECT_TYPE)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW MV
REFRESH FAST ON DEMAND
AS
SELECT owner,object_type,sum(object_id) sm,COUNT(object_id) cnt,COUNT(*) cntall
FROM TABLE1
WHERE owner='SYS'
GROUP BY owner,object_type;

update table1 set owner = 'SYS' where owner = 'SYS1'

EXEC DBMS_MVIEW.REFRESH ('MV'); 

insert into table1 values (11111,'sdfsdfs','SYS','SVBTG')

EXEC DBMS_MVIEW.REFRESH ('MV'); 


И теперь всё работает. Бог есть )
sdibrov
Дата: 20.11.2009 14:39:03
Учите мат.часть.
Была таблица 98 млн строк (бланки по ведомостям), и нужно было сделать часто используемые запросы, которые получют последнюю ведомость бланка

Сам с бубном плясал 4 дня, пока не заглянул в Data Warehousing Guide (Materialized Views with Aggregates)
читаем

SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns.

То есть, Агрегатное MVIEW по FAST должно обязательное включать:
- count(*)
- count({все колонки, по которым считается аггрегатные функции})

После этого, даже булки начинают расти на дереве
:-)