CREATE MATERIALIZED VIEW ...on commit.. не работает в 817
Павел
пример:
CREATE TABLE TPR2D_111 ( SC NUMBER(10), PRETV NUMBER
(10,2) )
CREATE MATERIALIZED VIEW LOG ON TPR2D_111
WITH ROWID (SC,pretv) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW MV_PR_PRLIST_TVR
BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS
SELECT sc,SUM(pretv) pp FROM TPR2D_111 A
GROUP BY sc;
под Oracle 8.1.7 даёт ошибку
ORA-12054: нельзя установить атрибут регенерации ON COMMIT для
материализ. представления,
a под 9i работает.
почему?
Павел
--
Отправлено через сервер Форумы@mail.ru - http://talk.mail.ru
Gennady Khudiakov
"Павел" <in...@uniacc.md> сообщил/сообщила в новостях следующее:
news:cpbs07$sof$1...@host.talk.ru...
> a под 9i работает.
>
> почему?
А потому, что валидность рассматривается в разных условиях:
одна БД - продукивная, а вторая - тестовая.
Валидность MV определяется не версией СУБД - а условиями его обновления.
Основное условие - привести MV в валидное состояние.
Самый легкий путь - в ОЕМ с консоли дать команду refresh на конкретный MV.
Правда, последствия стоит оценить заранее - в ОЛТП-системе можно подвесить
все и навсегда.
Основное понятие - MV есть сложно-продолжительный запрос(как написан) - и
если данные в исходных (базовых) таблицах меняются слишко уж часто, то
получить ответ на него - ждать долго надо.:)
А изменений в условиях обновления нельзя получить, если MV не валиден.
Сначала - обновить или пересоздать и не иначе(пересоздать - проще зачастую).
Еще раз - если ОЛТП, то про MV надо забыть.
Так что не в версиях проблема - с 8.1.х оно не менялось (внутренние
особенности не играют роли).
Удачи.
Vladimir Begun
> 701327178
> "Павел" <in...@uniacc.md> сообщил/сообщила в новостях следующее:
> news:cpbs07$sof$1...@host.talk.ru...
>
>>a под 9i работает.
>>почему?
>
> А потому, что валидность рассматривается в разных условиях:
> одна БД - продукивная, а вторая - тестовая.
В оригинальном сообщении такой информации не было. Прочитав то что ты
написал (квотировано) ниже, я могу сделать вывода что удалённое чтение
мыслей на расстоянии не твоя стезя. :)
> Валидность MV определяется не версией СУБД - а условиями его обновления.
Довод неверный. Почитав документацию от 8, 8i, 9i, 10g + metalink notes можно
найти отличия -- прогресс не стоит на месте, как вообще, так и в области
суммаризации и агрегации данных. Чуть ниже ты это увидишь сам.
> Основное условие - привести MV в валидное состояние.
>
> Самый легкий путь - в ОЕМ с консоли дать команду refresh на конкретный MV.
Самый лёгкий путь -- dbms_mview.explain_mview (присутствует начиная с 9i):
-- explain_mview - explain an mv or potential mv
> Правда, последствия стоит оценить заранее - в ОЛТП-системе можно подвесить
> все и навсегда.
Для общего случая данный довод неверен.
> Основное понятие - MV есть сложно-продолжительный запрос(как написан) - и
Неверно. Данное mview -- это FAST REFRESHABLE mview. Его структура проста
и понятна. Ты не можешь делать такой вывод не зная ничего о бизнес-процессах,
приложении, среде, требованиях по скорости отклика и объёмах данных в
транзакциях.
> если данные в исходных (базовых) таблицах меняются слишко уж часто, то
> получить ответ на него - ждать долго надо.:)
Неверно. Есть materialized view logs, они хранят дельту, также есть понятие
read consistency.
> А изменений в условиях обновления нельзя получить, если MV не валиден.
Иногда можно, но это corner case (upgrade 8i->9iR2) и рассматривать мы его
не будем.
> Сначала - обновить или пересоздать и не иначе(пересоздать - проще зачастую).
В данном случае оригинальный вопрос был о том как создать, а не о том как
пересоздать.
> Еще раз - если ОЛТП, то про MV надо забыть.
Ещё раз - это слишком категоричное заявление. Ты не знаешь как они используются
у Павла, поэтому не можешь этого утверждать.
> Так что не в версиях проблема - с 8.1.х оно не менялось (внутренние
> особенности не играют роли).
Ну-ну... ты видел код ядра Oracle? Видимо нет, поэтому не стоит делать
слишком голословных выводов, особенно когда столько много слов дано,
но не сказано как исправить положение. Чуть ниже пример того что есть
разница в работе версий.
Павел, проверка сделана для 10g, 9.2.0.5 и для 8.1.7.4. Ключевые слова:
SUM(expr) without COUNT(expr)
COUNT(*) is not present in the select list
Если ты уберешь комментарий
8 -- , COUNT(pretv) cp
9 -- , COUNT(*) cnt
всё будет работать. Данные ограничения присутствуют в документации. Пожалуйста
почитай.
SQL> SELECT * FROM v$version WHERE ROWNUM = 1
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
SQL> DROP TABLE tpr2d_111
2 /
Table dropped.
SQL> DROP MATERIALIZED VIEW mv_pr_prlist_tvr
2 /
Materialized view dropped.
SQL> CREATE TABLE tpr2d_111 (
2 sc NUMBER(10)
3 , pretv NUMBER (10, 2)
4 )
5 /
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON tpr2d_111 WITH ROWID (sc, pretv) INCLUDING NEW VALUES
2 /
Materialized view log created.
SQL> DEFINE mv_name=MV_PR_PRLIST_TVR
SQL> SET VERIFY OFF
SQL> CREATE MATERIALIZED VIEW mv_pr_prlist_tvr
2 BUILD IMMEDIATE
3 REFRESH FAST
4 ON COMMIT
5 AS
6 SELECT sc
7 , SUM(pretv) sp
8 -- , COUNT(pretv) cp
9 -- , COUNT(*) cnt
10 FROM tpr2d_111
11 GROUP BY sc
12 /
Materialized view created.
SQL> EXEC dbms_mview.refresh('mv_pr_prlist_tvr', 'C');
PL/SQL procedure successfully completed.
SQL> DROP TABLE mv_capabilities_table
2 /
Table dropped.
SQL> CREATE TABLE MV_CAPABILITIES_TABLE (
2 STATEMENT_ID VARCHAR(30),
3 MVOWNER VARCHAR(30),
4 MVNAME VARCHAR(30),
5 CAPABILITY_NAME VARCHAR(30),
6 POSSIBLE CHARACTER(1),
7 RELATED_TEXT VARCHAR(2000),
8 RELATED_NUM NUMBER,
9 MSGNO INTEGER,
10 MSGTXT VARCHAR(2000),
11 SEQ NUMBER)
12 /
Table created.
SQL> DELETE FROM mv_capabilities_table
2 WHERE mvname = '&&mv_name'
3 /
0 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> EXEC dbms_mview.explain_mview('&&mv_name');
PL/SQL procedure successfully completed.
SQL> COLUMN related_text FORMAT A30
SQL> COLUMN msgtxt FORMAT A60 WORD_WRAP
SQL> COLUMN related_num FORMAT 99999
SQL> SELECT capability_name
2 , possible
3 , related_text
4 , related_num
5 , msgno
6 , msgtxt
7 , seq
8 FROM mv_capabilities_table
9 WHERE mvname = '&&mv_name'
10 ORDER BY seq
11 /
CAPABILITY_NAME P RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
------------------------------ - ------------------------------ ----------- --------- ------------------------------------------------------------ ---------
PCT N 1
REFRESH_COMPLETE Y 1002
REFRESH_FAST Y 2003
REWRITE N 3004
PCT_TABLE N TPR2D_111 93 2068 relation is not a partitioned table 4005
REFRESH_FAST_AFTER_INSERT Y 5006
REFRESH_FAST_AFTER_ONETAB_DML N SP 19 2143 SUM(expr) without COUNT(expr) 6007
REFRESH_FAST_AFTER_ONETAB_DML N 2142 COUNT(*) is not present in the select list 6008
REFRESH_FAST_AFTER_ANY_DML N APPS.TPR2D_111 2165 mv log does not have sequence # 7009
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7010
REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the 8011
materialized view
REWRITE_FULL_TEXT_MATCH N 2159 query rewrite is disabled on the materialized view 9012
REWRITE_PARTIAL_TEXT_MATCH N 2159 query rewrite is disabled on the materialized view 10013
REWRITE_GENERAL N 2159 query rewrite is disabled on the materialized view 11014
REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on 12015
any of the detail tables
PCT_TABLE_REWRITE N TPR2D_111 93 2068 relation is not a partitioned table 13016
16 rows selected.
SQL> INSERT INTO tpr2d_111 VALUES(1, 2);
1 row created.
SQL> INSERT INTO tpr2d_111 VALUES(2, 2);
1 row created.
SQL> INSERT INTO tpr2d_111 VALUES(3, 3);
1 row created.
SQL> DELETE tpr2d_111 WHERE sc = 2;
1 row deleted.
SQL> UPDATE tpr2d_111 SET pretv = -pretv * 2;
2 rows updated.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT * FROM mv_pr_prlist_tvr;
no rows selected
SQL>
SQL> SPOOL OFF
Павел
VB> Gennady Khudiakov wrote:
VB>> 701327178
VB>> "Павел" <in...@uniacc.md> сообщил/сообщила в новостях следующее:
VB>> news:cpbs07$sof$1...@host.talk.ru...
VB>>
VB>>>a под 9i работает.
VB>>>почему?
VB>>
VB>> А потому, что валидность рассматривается в разных условиях:
VB>> одна БД - продукивная, а вторая - тестовая.
VB> В оригинальном сообщении такой информации не было. Прочитав то что
ты
VB> написал (квотировано) ниже, я могу сделать вывода что удалённое
чтение
VB> мыслей на расстоянии не твоя стезя. :)
VB>> Валидность MV определяется не версией СУБД - а условиями его
обновления.
VB> Довод неверный. Почитав документацию от 8, 8i, 9i, 10g + metalink
notes можно
VB> найти отличия -- прогресс не стоит на месте, как вообще, так и в
области
VB> суммаризации и агрегации данных. Чуть ниже ты это увидишь сам.
VB>> Основное условие - привести MV в валидное состояние.
VB>>
VB>> Самый легкий путь - в ОЕМ с консоли дать команду refresh на
конкретный MV.
VB> Самый лёгкий путь -- dbms_mview.explain_mview (присутствует начиная
с 9i):
VB> -- explain_mview - explain an mv or potential mv
VB>> Правда, последствия стоит оценить заранее - в ОЛТП-системе можно
подвесить
VB>> все и навсегда.
VB> Для общего случая данный довод неверен.
VB>> Основное понятие - MV есть сложно-продолжительный запрос(как
написан) - и
VB> Неверно. Данное mview -- это FAST REFRESHABLE mview. Его структура
проста
VB> и понятна. Ты не можешь делать такой вывод не зная ничего о бизнес-
процессах,
VB> приложении, среде, требованиях по скорости отклика и объёмах данных
в
VB> транзакциях.
VB>> если данные в исходных (базовых) таблицах меняются слишко уж
часто, то
VB>> получить ответ на него - ждать долго надо.:)
VB> Неверно. Есть materialized view logs, они хранят дельту, также есть
понятие
VB> read consistency.
VB>> А изменений в условиях обновления нельзя получить, если MV не
валиден.
VB> Иногда можно, но это corner case (upgrade 8i->9iR2) и рассматривать
мы его
VB> не будем.
VB>> Сначала - обновить или пересоздать и не иначе(пересоздать - проще
зачастую).
VB> В данном случае оригинальный вопрос был о том как создать, а не о
том как
VB> пересоздать.
VB>> Еще раз - если ОЛТП, то про MV надо забыть.
VB> Ещё раз - это слишком категоричное заявление. Ты не знаешь как они
используются
VB> у Павла, поэтому не можешь этого утверждать.
VB>> Так что не в версиях проблема - с 8.1.х оно не менялось (внутренние
VB>> особенности не играют роли).
VB> Ну-ну... ты видел код ядра Oracle? Видимо нет, поэтому не стоит
делать
VB> слишком голословных выводов, особенно когда столько много слов дано,
VB> но не сказано как исправить положение. Чуть ниже пример того что
есть
VB> разница в работе версий.
VB> Павел, проверка сделана для 10g, 9.2.0.5 и для 8.1.7.4. Ключевые
слова:
VB> SUM(expr) without COUNT(expr)
VB> COUNT(*) is not present in the select list
VB> Если ты уберешь комментарий
VB> 8 -- , COUNT(pretv) cp
VB> 9 -- , COUNT(*) cnt
VB> всё будет работать. Данные ограничения присутствуют в документации.
Пожалуйста
VB> почитай.
VB> SQL> SELECT * FROM v$version WHERE ROWNUM = 1
VB> 2 /
VB> BANNER
VB> ----------------------------------------------------------------
VB> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
VB> SQL> DROP TABLE tpr2d_111
VB> 2 /
VB> Table dropped.
VB> SQL> DROP MATERIALIZED VIEW mv_pr_prlist_tvr
VB> 2 /
VB> Materialized view dropped.
VB> SQL> CREATE TABLE tpr2d_111 (
VB> 2 sc NUMBER(10)
VB> 3 , pretv NUMBER (10, 2)
VB> 4 )
VB> 5 /
VB> Table created.
VB> SQL> CREATE MATERIALIZED VIEW LOG ON tpr2d_111 WITH ROWID (sc,
pretv) INCLUDING NEW VALUES
VB> 2 /
VB> Materialized view log created.
VB> SQL> DEFINE mv_name=MV_PR_PRLIST_TVR
VB> SQL> SET VERIFY OFF
VB> SQL> CREATE MATERIALIZED VIEW mv_pr_prlist_tvr
VB> 2 BUILD IMMEDIATE
VB> 3 REFRESH FAST
VB> 4 ON COMMIT
VB> 5 AS
VB> 6 SELECT sc
VB> 7 , SUM(pretv) sp
VB> 8 -- , COUNT(pretv) cp
VB> 9 -- , COUNT(*) cnt
VB> 10 FROM tpr2d_111
VB> 11 GROUP BY sc
VB> 12 /
VB> Materialized view created.
VB> SQL> EXEC dbms_mview.refresh('mv_pr_prlist_tvr', 'C');
VB> PL/SQL procedure successfully completed.
VB> SQL> DROP TABLE mv_capabilities_table
VB> 2 /
VB> Table dropped.
VB> SQL> CREATE TABLE MV_CAPABILITIES_TABLE (
VB> 2 STATEMENT_ID VARCHAR(30),
VB> 3 MVOWNER VARCHAR(30),
VB> 4 MVNAME VARCHAR(30),
VB> 5 CAPABILITY_NAME VARCHAR(30),
VB> 6 POSSIBLE CHARACTER(1),
VB> 7 RELATED_TEXT VARCHAR(2000),
VB> 8 RELATED_NUM NUMBER,
VB> 9 MSGNO INTEGER,
VB> 10 MSGTXT VARCHAR(2000),
VB> 11 SEQ NUMBER)
VB> 12 /
VB> Table created.
VB> SQL> DELETE FROM mv_capabilities_table
VB> 2 WHERE mvname = '&&mv_name'
VB> 3 /
VB> 0 rows deleted.
VB> SQL> COMMIT;
VB> Commit complete.
VB> SQL> EXEC dbms_mview.explain_mview('&&mv_name');
VB> PL/SQL procedure successfully completed.
VB> SQL> COLUMN related_text FORMAT A30
VB> SQL> COLUMN msgtxt FORMAT A60 WORD_WRAP
VB> SQL> COLUMN related_num FORMAT 99999
VB> SQL> SELECT capability_name
VB> 2 , possible
VB> 3 , related_text
VB> 4 , related_num
VB> 5 , msgno
VB> 6 , msgtxt
VB> 7 , seq
VB> 8 FROM mv_capabilities_table
VB> 9 WHERE mvname = '&&mv_name'
VB> 10 ORDER BY seq
VB> 11 /
VB> CAPABILITY_NAME P RELATED_TEXT
RELATED_NUM MSGNO
MSGTXT SEQ
VB> ------------------------------ - ------------------------------ ----
------- --------- ------------------------------------------------------
------ ---------
VB> PCT
N
1
VB> REFRESH_COMPLETE
Y
1002
VB> REFRESH_FAST
Y
2003
VB> REWRITE
N
3004
VB> PCT_TABLE N
TPR2D_111 93 2068 relation is not a
partitioned table 4005
VB> REFRESH_FAST_AFTER_INSERT
Y
5006
VB> REFRESH_FAST_AFTER_ONETAB_DML N
SP 19 2143 SUM(expr) without
COUNT(expr) 6007
VB> REFRESH_FAST_AFTER_ONETAB_DML
N 2142 COUNT(*) is not
present in the select list 6008
VB> REFRESH_FAST_AFTER_ANY_DML N
APPS.TPR2D_111 2165 mv log does not
have sequence # 7009
VB> REFRESH_FAST_AFTER_ANY_DML
N 2161 see the reason
why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7010
VB> REFRESH_FAST_PCT
N 2157 PCT is not
possible on any of the detail tables in the 8011
VB>
materialized view
VB> REWRITE_FULL_TEXT_MATCH
N 2159 query rewrite is
disabled on the materialized view 9012
VB> REWRITE_PARTIAL_TEXT_MATCH
N 2159 query rewrite is
disabled on the materialized view 10013
VB> REWRITE_GENERAL
N 2159 query rewrite is
disabled on the materialized view 11014
VB> REWRITE_PCT
N 2158 general rewrite
is not possible or PCT is not possible on 12015
VB>
any of the detail tables
VB> PCT_TABLE_REWRITE N
TPR2D_111 93 2068 relation is not a
partitioned table 13016
VB> 16 rows selected.
VB> SQL> INSERT INTO tpr2d_111 VALUES(1, 2);
VB> 1 row created.
VB> SQL> INSERT INTO tpr2d_111 VALUES(2, 2);
VB> 1 row created.
VB> SQL> INSERT INTO tpr2d_111 VALUES(3, 3);
VB> 1 row created.
VB> SQL> DELETE tpr2d_111 WHERE sc = 2;
VB> 1 row deleted.
VB> SQL> UPDATE tpr2d_111 SET pretv = -pretv * 2;
VB> 2 rows updated.
VB> SQL> COMMIT;
VB> Commit complete.
VB> SQL>
VB> SQL> SELECT * FROM mv_pr_prlist_tvr;
VB> no rows selected
VB> SQL>
VB> SQL> SPOOL OFF
Всем привет,
Владимир прав.
я почитал доку и это всё заработало.
т.е. в 8.1.7 требования к fast-mv жёстче.
а какой наиболее быстрый способ обновления MV можно в случае:
CREATE TABLE tpr2d_111 (
unit NUMBER(10),
dateBEGIN date,
Saleprice NUMBER (10, 2)
);
CREATE MATERIALIZED VIEW mv_pr_prlist_tvr
... as
SELECT A.*
,NVL(LEAD(dateBEGIN,1)
OVER(PARTITION BY unit ORDER BY DATEBegin)
,'1.1.3001')-1 as DATEEnd
from tpr2d_111;
т.е. используется window-фунkция (analytical function).
в примере иммитируется период действия цены для товара
mv-on commit метод даже в 10g не идёт...
может здесь надо отказаться от MV и написать на trigger-e свою табличку?
Gennady Hudiakov
> VB>> одна БД - продукивная, а вторая - тестовая.
>
> VB> В оригинальном сообщении такой информации не было. Прочитав то что
> чтение мыслей на расстоянии не твоя стезя. :)
Однако...что ж тут было не понятно?
И что - неправильно? Хотя.. ну, пусть будет не тестовой, что меняется?
> VB>> Валидность MV определяется не версией СУБД - а условиями его
> обновления.
> VB> Довод неверный. Почитав документацию от 8, 8i, 9i, 10g + metalink
> notes можно
Эх, читать надо бы чуть повнимательней. Не только документацию, но и тексты
попроще.
Структура МВ и валидность - ну не одно и то же, речь о чем шла?
> VB> найти отличия -- прогресс не стоит на месте, как вообще, так и в
> области
> VB> суммаризации и агрегации данных. Чуть ниже ты это увидишь сам.
Примерчик красивый, слов нет - получил удовольствие,
да только к валидности вроде не относится, в различиям в версиях - да,
но в исходном примере о таком подборе функций речь не шла.
> VB>> Правда, последствия стоит оценить заранее - в ОЛТП-системе можно
> подвесить
> VB>> все и навсегда.
> VB> Для общего случая данный довод неверен.
Ах! Да не может быть! Еще раз - перечитай, что написано.
Замечено, что стоит оценить последствия -
так что же тут против академической логики и документации?
> VB>> Основное понятие - MV есть сложно-продолжительный запрос(как
> написан) - и
>
> VB> Неверно. Данное mview -- это FAST REFRESHABLE mview. Его структура
> проста и понятна.
Ну и что? Опять же сказано - основное понятие.
Про общий случай кто чуть ранее рассуждал?
Ты не можешь делать такой вывод не зная ничего о бизнес-
> процессах,
> VB> приложении, среде, требованиях по скорости отклика и объёмах данных
> в транзакциях.
Аналогично.
> VB>> Еще раз - если ОЛТП, то про MV надо забыть.
>
> VB> Ещё раз - это слишком категоричное заявление. Ты не знаешь как они
> используются
> VB> у Павла, поэтому не можешь этого утверждать.
А я не сам придумал, у уважаемых людей позаимствовал.
Да и практика вроде бы подтверждает
(не Павла, хотя и ему от этого не деться никуда).
> VB>> Так что не в версиях проблема - с 8.1.х оно не менялось (внутренние
> VB>> особенности не играют роли).
> VB> слишком голословных выводов, особенно когда столько много слов дано,
> VB> но не сказано как исправить положение.
Товарисч, вы не профессором подрабатываете между скриптами?
Так здесь не аудитория, пальчиком ругать не стоит.
Чуть ниже пример того что
> есть разница в работе версий.
Ну и я про то же - примерчик про разницу в работе версий.
Кстати, можно было не загоняться так:
при создании МВ ошибки выводятся в читаемом виде :)
и в чем было дело - там виднее на месте было и сразу.
Пел ты красиво и песню сложил высоко.
Послушал с большим удовольствием.
Молодец, и Павлу было счастье.
Удачи.
Vladimir Begun
сожалению, либо talk.ru, либо что-то ещё куда-то задевало мои
сообщения, возможно они когда-нибудь появятся...
Я повторяю пост, компонуя всё в одно сообщение.
Павел wrote:
> а какой наиболее быстрый способ обновления MV можно в случае:
> CREATE TABLE tpr2d_111 (
> unit NUMBER(10),
> dateBEGIN date,
> Saleprice NUMBER (10, 2)
> );
>
> CREATE MATERIALIZED VIEW mv_pr_prlist_tvr
> ... as
> SELECT A.*
> ,NVL(LEAD(dateBEGIN,1)
> OVER(PARTITION BY unit ORDER BY DATEBegin)
> ,'1.1.3001')-1 as DATEEnd
> from tpr2d_111;
>
> т.е. используется window-фунkция (analytical function).
> в примере иммитируется период действия цены для товара
> mv-on commit метод даже в 10g не идёт...
> может здесь надо отказаться от MV и написать на trigger-e свою табличку?
При использовании analytical function будут сложности. В документации
написано о том какие функции агрегации можно использовать для FAST
refresh -- analytical functions среди них нет, в принципе, это очевидно,
там есть очень много мелких и побольше сложностей и нюансов.
В твоём случае я бы
а) посмотрел нужна ли такая аналитическая выборка для всего набора данных,
возможно присутствие WHERE clause позволить избваиться от агрегации как
таковой для всех данных таблицы.
б) рассмотрел nested materialized views. Они не будут refresh on commit,
но они будут fast refreshable. Это будет не одно mview, а 4-5, которые будут
выбирать данные одно из другого, и возможно на последнем уровне возвращаться
к данным базовой таблицы. О nested mviews написано в документации. Т.е.
ты просто переписываешь свой код используя обычные функции агрегирования и
оформляешь это в виде цепочки выборок из mviews:
CREATE MVIEW mv1...
CREATE MVIEW mv2... SELECT ... FROM mv1
...
Ниже будет пример для 10g -- всего 3 mview, они nested, refresh fast on
commit, погоняй тесты, оцени приемлема ли скорость. Для 8i придётся делать
по-другому.
в) рассмотрел trigger -- тоже решение, возможно даже лучшее нежели mview.
Не нужно городить огород с набором дополнительных объектов и тратить время
на refresh оных. Получится ли написать эффективный триггер, который заменит
логику SELECT statement с analytical function -- это пока вопрос (думаю, это
реализуемо), можно попробовать. Акромя тригера нужно будет написать код,
который бы заново делал refresh данных в твоей целевой таблице (возможно по-
периодам), на тот случай если триггер будет disabled для maintenance и проч.
Дополнение: здесь и в fido7 были дискуссии (поищи на google) по-поводу хранения
двух или одной дат для систем хранения goods/prices. Для многих случаев хранение
двух дат -- эффективно, но есть варианты когда при массовой загрузке нужно
пересчитывать overlapped data ranges, тут хранение двух дат может оказаться
проблемой -- пересчёт диапазонов будет долгим. Павел Шендрыгайлов делал
исследования:
http://www.dbgroup.ru/stuff/orapapers/timeser1/
http://www.dbgroup.ru/stuff/orapapers/timeser2/
Рекомендую обратить на них внимание.
Пример:
SET TRIMSPOOL ON TERMOUT ON LINES 300 ECHO ON
SELECT * FROM v$version WHERE ROWNUM = 1
/
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
DROP TABLE tpr2d_111;
CREATE TABLE tpr2d_111 (
unit NUMBER(10) NOT NULL
, datebegin DATE NOT NULL
, saleprice NUMBER(10, 2) NOT NULL
)
/
CREATE UNIQUE INDEX idx$tpr2d_111 ON tpr2d_111 (unit, datebegin)
/
CREATE MATERIALIZED VIEW LOG ON tpr2d_111 WITH ROWID, SEQUENCE (unit, datebegin, saleprice) INCLUDING NEW VALUES
/
INSERT INTO tpr2d_111 VALUES(1, SYSDATE, 1.1);
INSERT INTO tpr2d_111 VALUES(2, SYSDATE, 2.2);
INSERT INTO tpr2d_111 VALUES(2, SYSDATE + 1, 2.4);
INSERT INTO tpr2d_111 VALUES(3, SYSDATE + 1 , 3.1);
INSERT INTO tpr2d_111 VALUES(3, SYSDATE, 3.3);
COMMIT;
DROP MATERIALIZED VIEW mv01;
CREATE MATERIALIZED VIEW mv01
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT a.ROWID arid
, b.ROWID brid
, a.unit
, a.datebegin
, b.datebegin - 1 dateend
FROM tpr2d_111 a
, tpr2d_111 b
WHERE a.unit = b.unit
AND a.datebegin < b.datebegin
/
CREATE MATERIALIZED VIEW LOG ON mv01 WITH ROWID, SEQUENCE (unit, datebegin, dateend) INCLUDING NEW VALUES
/
CREATE UNIQUE INDEX idx$mv01 ON mv01 (unit, datebegin)
/
DROP MATERIALIZED VIEW mv02;
CREATE MATERIALIZED VIEW mv02
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT unit
, MAX(datebegin) datebegin
, TO_DATE('31.12.3000', 'DD.MM.YYYY') dateend
, COUNT(*) cnt
FROM tpr2d_111
GROUP BY
unit
/
CREATE MATERIALIZED VIEW LOG ON mv02 WITH ROWID, SEQUENCE (unit, dateend) INCLUDING NEW VALUES
/
CREATE UNIQUE INDEX idx$mv02 ON mv02 (unit, datebegin)
/
DROP MATERIALIZED VIEW mv03;
CREATE MATERIALIZED VIEW mv03
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT '1' umarker, mv01.ROWID mrid, base.ROWID brid, mv01.unit, mv01.datebegin, mv01.dateend, base.saleprice
FROM mv01, tpr2d_111 base
WHERE base.unit = mv01.unit
AND base.datebegin = mv01.datebegin
UNION ALL
SELECT '2' umarker, mv02.ROWID mrid, base.ROWID brid, mv02.unit, mv02.datebegin, mv02.dateend, base.saleprice
FROM mv02, tpr2d_111 base
WHERE base.unit = mv02.unit
AND base.datebegin = mv02.datebegin
/
DEFINE mv_name=MV03
DELETE FROM mv_capabilities_table
WHERE mvname = '&&mv_name'
/
COMMIT;
EXEC dbms_mview.explain_mview('&&mv_name');
COLUMN related_text FORMAT A30
COLUMN msgtxt FORMAT A60 WORD_WRAP
COLUMN related_num FORMAT 99999
SELECT capability_name
, possible
, related_text
, related_num
, msgno
, msgtxt
, seq
FROM mv_capabilities_table
WHERE mvname = '&&mv_name'
ORDER BY seq
/
SELECT * FROM tpr2d_111;
SELECT * FROM mv01;
SELECT * FROM mv02;
SELECT * FROM mv03 ORDER BY unit, datebegin;
UPDATE tpr2d_111
SET datebegin = '20.12.2004'
WHERE unit = 3
AND saleprice = 3.1
/
COMMIT;
SELECT * FROM mv03 ORDER BY unit, datebegin;
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.