Materialized view

[mahajanakhil198]

I HAVE CREATEAD AN MV AS:

CREATE MATERIALIZED VIEW LOG ON E_MVIEW
WITH SEQUENCE,ROWID(DEPARTMENT_ID,SALARY)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW E_MVIEW
REFRESH FAST ON COMMIT
AS
SELECT DEPARTMENT_ID,COUNT(*),SUM(SALARY)
FROM E
GROUP BY DEPARTMENT_ID;

CREATE MATERIALIZED VIEW LOG ON E_MVIEW
WITH SEAUENCE,ROWID
INCLUDING NEW VALUES;


I HAVE READ RESTRICTIONS ON FAST REFRESH MVs WITH AGGREAGTE FUNCTIONS, ON SINGLE BASE TABLE. THATS Y INCLUDED COUNT(*)..
BUT STILL VIEW IS NOT REFRESHED AFTER UPDATE ON BASE TABLE..
WHY IS IT SO?

[PAVB]

Please post your actual code e.g. copy/paste

The code you posted does not compiles.

[mahajanakhil198]

This time i m creating MV on differnt base table:

SQL> create MATERIALIZED VIEW LOG ON e
2 WITH SEQUENCE,ROWID(department_id,salary)
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> create MATERIALIZED VIEW e_mview
2 refresh fast on commit
3 as
4 select department_id,count(*),sum(salary) sal_total
5 from e group by department_id;

Materialized view created.

SQL> create MATERIALIZED VIEW LOG ON e_mview
2 WITH SEQUENCE,ROWID
3 INCLUDING NEW VALUES;

Materialized view log created.

SQL> select * from e_mview;

DEPARTMENT_ID COUNT(*) SAL_TOTAL
------------- ---------- ----------
10 1 5000
20 2 19000
30 6 24900
40 1 6500
50 45 156400
60 5 28800
70 1 10000
80 34 304500
90 3 53000
100 6 51600
110 2 20300
1 7000

12 rows selected.

SQL> select salary from e where DEPARTMENT_ID =10;

SALARY
----------
5000

SQL> update e set salary=4500 DEPARTMENT_ID =10;
update e set salary=4500 DEPARTMENT_ID =10
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> update e set salary=4500 where DEPARTMENT_ID =10;

1 row updated.

SQL> select * from e_mview;

DEPARTMENT_ID COUNT(*) SAL_TOTAL
------------- ---------- ----------
10 1 5000
20 2 19000
30 6 24900
40 1 6500
50 45 156400
60 5 28800
70 1 10000
80 34 304500
90 3 53000
100 6 51600
110 2 20300
1 7000

12 rows selected.

SQL> commit;

Commit complete.

SQL> select * from e_mview;

DEPARTMENT_ID COUNT(*) SAL_TOTAL
------------- ---------- ----------
10 1 5000
20 2 19000
30 6 24900
40 1 6500
50 45 156400
60 5 28800
70 1 10000
80 34 304500
90 3 53000
100 6 51600
110 2 20300
1 7000

12 rows selected.

===>>no change before and after commit. Havent got it..

[PAVB]

I should work -please see example below.

My wild guess -because I can't see the specs of your base table - is that NOT NULL constraint is missing on your "SALARY" column. Please check.

SQL> 
SQL> create table mv_basetable
  2  (
  3  dep_id number not null,
  4  salary number not null
  5  )
  6  ;

Table created.

SQL> insert into mv_basetable values(10,1000);

1 row created.

SQL> insert into mv_basetable values(20,2000);

1 row created.

SQL> insert into mv_basetable values(20,3000);

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on mv_basetable
  2  with rowid, sequence ( dep_id, salary )
  3  including new values
  4  ;

Materialized view log created.

SQL> create materialized view my_mv
  2  refresh fast on commit
  3  enable query rewrite
  4  as
  5  select dep_id,
  6         sum(salary) as SumSalary,
  7         count(*)    as RowCount 
  8  from   mv_basetable
  9  group by dep_id
 10  ;

Materialized view created.

SQL> select * from mv_basetable;

    DEP_ID     SALARY
---------- ----------
        10       1000
        20       2000
        20       3000

SQL> select * from my_mv;

    DEP_ID  SUMSALARY   ROWCOUNT
---------- ---------- ----------
        20       5000          2
        10       1000          1

SQL> 
SQL> insert into mv_basetable values(10,1000);

1 row created.

SQL> insert into mv_basetable values(30,5000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv_basetable;

    DEP_ID     SALARY
---------- ----------
        10       1000
        20       2000
        20       3000
        10       1000
        30       5000

SQL> select * from my_mv;

    DEP_ID  SUMSALARY   ROWCOUNT
---------- ---------- ----------
        20       5000          2
        10       2000          2
        30       5000          1

SQL> update mv_basetable
  2  set    salary = 1500
  3  where  dep_id = 20
  4  ;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from mv_basetable;

    DEP_ID     SALARY
---------- ----------
        10       1000
        20       1500
        20       1500
        10       1000
        30       5000

SQL> select * from my_mv;

    DEP_ID  SUMSALARY   ROWCOUNT
---------- ---------- ----------
        20       3000          2
        10       2000          2
        30       5000          1

SQL>

[mahajanakhil198]

yes both department_id,salary didnt have not null..but i didnt read this restriction

[PAVB]

yes both department_id,salary didnt have not null..but i didnt read this restriction

Call it "experience".

Set NOT NULL constraint on the column you are applying sum() function, that should do the trick -please tell us if not.

[mahajanakhil198]

==>>ooops insert working well but update doesnt..

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
D NOT NULL NUMBER
SAL NOT NULL NUMBER

SQL> create materialized view log on test
2 with sequence,rowid(d,sal)
3 including new values;

Materialized view log created.

SQL> select * from test;

D SAL
---------- ----------
1 3000
1 2000
2 4000
2 5000
3 1000
3 2000

6 rows selected.

SQL>
SQL> create materialized view mv
2 refresh fast on commit
3 enable query rewrite
4 as select d,sum(sal) tot,count(*) from test group by d;

Materialized view created.

SQL> select * from mv;

D TOT COUNT(*)
---------- ---------- ----------
1 5000 2
2 9000 2
3 3000 2

SQL> insert into test values(3,5500);

1 row created.

SQL> select * from mv;

D TOT COUNT(*)
---------- ---------- ----------
1 5000 2
2 9000 2
3 3000 2

SQL> commit;

Commit complete.

SQL> select * from mv;

D TOT COUNT(*)
---------- ---------- ----------
1 5000 2
2 9000 2
3 8500 3

SQL> select * from test;

D SAL
---------- ----------
1 3000
1 2000
2 4000
2 5000
3 1000
3 2000
3 5500

7 rows selected.

SQL> update test set sal=4700 where sal=5500 ;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test;

D SAL
---------- ----------
1 3000
1 2000
2 4000
2 5000
3 1000
3 2000
3 4700

7 rows selected.

SQL> select * from mv;

D TOT COUNT(*)
---------- ---------- ----------
1 5000 2
2 9000 2
3 8500 3

SQL>

[mahajanakhil198]

any help?
i have observerd dat even query rewrite is not possible on fast refreshable MVs dat i created

[PAVB]

i have observerd dat even query rewrite is not possible on fast refreshable MVs dat i created

Start by checking your initialization paramenters a.k.a. init.ora, is QUERY_REWRITE_ENABLED set to TRUE?

Please test query rewrite feature using a query that exactly matches what you have in your MV. Once that works move ahead. What kind of architecture do you have for real?... is it a star schema?

[mahajanakhil198]

SQL> show parameter query

NAME TYPE VALUE
------------------------------------ ----------- -------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string ENFORCED

I actually copied query from the "QUERY" cloumn of user_mviews.
Moreover, i created one more MV with complete refresh option. On this MV, query rewrite is possible.
I feel dat bcos the fast refershable MV that i created is not being refreshed with UPDATE statement on base table, so this might have caused sm problem in query rewrite.