materialized view
sathyamurthy.s, May 07, 2002 - 2:14 am UTC
Hi
i am using oracle 8i , i want to create materialized view.
but it is giving the problem .
i am giving the script
create table t1 (a int,b int, y number,primary key(a,b));
Table created.
create materialized view log on t1 with rowid(a,y) including new values;
Materialized view log created.
create materialized view t2
build immediate
refresh force on commit
as
select dno, sum(sal)
as sum_sal
from emp
group by dno
cannot set the ON COMMIT refresh attribute for the materialized view
what is the problem please give solutions
Regards
sathya
May 07, 2002 - 7:22 am UTC
Please re-read my answer to the original question.
Pay close attention to second point I make about including "sum(sal)"
MATERIALIZED VIEW
sathyamurthy, May 07, 2002 - 8:23 am UTC
Hi Tom,
After adding count in the query it is working fine.
Thanks to you.
How to know ?
Raman Pujani, June 14, 2002 - 4:50 am UTC
Hi Tom,
Nice explanation. How do we come to know whether the query executed by user is actually using the materialized view, means user's query is rewrited ?
Is it true that, we should not include joins with SYSDATE (end_date > SYSDATE) while creating materialized view?
Regards,
Raman.
June 14, 2002 - 7:25 am UTC
explain plans.
you can explain the query,
you can use autotrace,
you can use (my favorite) sql_trace+tkprof
As for the last part -- think about it. as sysdate is an ever changing thing, the mat. view is out of date the second it finished refreshing.
mat. view , why do i get this
umesh, June 13, 2003 - 8:35 am UTC
1 CREATE SNAPSHOT LOG ON emp WITH rowid( deptno,sal )
2* INCLUDING NEW VALUES
3 /
Materialized view log created.
1 create materialized view t2
2 build immediate
3 refresh force on commit
4 as
5 select deptno, sum(sal)
6 as sum_sal, count( *) cnt
7 from emp
8* group by deptno
scott>/
from emp
*
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
June 13, 2003 - 8:56 am UTC
<b>amazingly, this is exactly the same thing that is addressed in the original question... repitition being the key to sucess.... I'll do it again :) </b>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#42218
That table shows in order to have
SUM(expr)
You must also include
COUNT(expr) and COUNT(*)
You do not have COUNT(expr)
ops$tkyte@ORA817DEV> create materialized view emp_mv
2 build immediate
3 refresh force on commit
4 as
5 select deptno, sum(sal) sum_sal, <b>count(sal) count_sal,</b> count(*) cnt
6 from emp
7 group by deptno
8 /
Materialized view created.
query_rewrite_enabled
Tony, July 07, 2003 - 10:46 am UTC
Oracle recommends to set query_rewrite_enabled = true for Mviews but I'm working on mviews created by someone else and the DDL starts like :
CREATE MATERIALIZED VIEW test_mv
PCTFREE 0 TABLESPACE MV_DATA STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
NOLOGGING NOCACHE
BUILD IMMEDIATE
USING INDEX TABLESPACE MV_INDEX STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
REFRESH COMPLETE ON DEMAND
START WITH (TRUNC(SYSDATE) + 1 + (4/24))
NEXT (TRUNC(SYSDATE) + 1 + (4/24) + (1/24))
WITH PRIMARY KEY
USING LOCAL ROLLBACK SEGMENT MV_RB
DISABLE QUERY REWRITE
AS
SELECT sa.sale_num AS sale_number,
sa.sale_desc AS sale_type,
or.cust_cd AS customer_code,
or.item AS item_id,
or.item_desc AS item_description,
...........
my question is why DISABLE QUERY REWRITE is used in this case. (we have some functions used in mviews)
July 07, 2003 - 10:55 am UTC
you would have to ask the guy who wrote it why they chose that option (and why they would use a storage clause in this day and age...)
That is true
Tony, July 07, 2003 - 11:23 am UTC
But my dilemma is that this guy is no more working with us and I'm working on mviews for the first time. Any input will be highly appreciated.
Regards
July 07, 2003 - 11:40 am UTC
my input is "i have no clue what he was thinking and why he might have not wanted query rewrite enabled"
bharath, July 07, 2003 - 1:44 pm UTC
>>and why they would use a storage clause in this day and >>age...)
what do you mean by that.
July 07, 2003 - 2:04 pm UTC
I mean, use locally managed tablespaces and never use initial, next, pctincrease, minextents, maxextents - let it just happen.
ORA-01001: invalid cursor
Kamal Kishore, September 14, 2003 - 11:36 am UTC
Hi Tom,
When I try to create a MVIEW using the UNION query, I get the Invalid Cursor error. Then I re-create the MVIEW without the UNION query, then DROP it, and then when I re-create the MVIEW again with the UNION QUERY, it is successful.
What could be the reason?
Any input will be appreciated.
Thanks,
All tables have MVIEW LOG created on them and SCOTT.BONUS table has ENAME as the PRIMARY KEY.
SQL> create materialized view mview_emp_dept
2 build immediate
3 refresh fast on demand
4 as
5 select * from scott.emp e
6 where
7 exists (select 1 from scott.dept d1
8 where
9 d1.deptno = e.deptno)
10 union
11 select * from scott.emp e
12 where
13 exists (select 1 from scott.bonus b
14 where
15 b.ename = e.ename)
16 /
select * from scott.emp e
*
ERROR at line 5:
ORA-01001: invalid cursor
ORA-00600: internal error code, arguments: [21051], [], [], [], [], [], [], []
SQL>
SQL> create materialized view mview_emp_dept
2 build immediate
3 refresh fast on demand
4 as
5 select * from scott.emp e
6 where
7 exists (select 1 from scott.dept d1
8 where
9 d1.deptno = e.deptno)
10 /
Materialized view created.
SQL>
SQL> drop materialized view mview_emp_dept ;
Materialized view dropped.
SQL>
SQL> create materialized view mview_emp_dept
2 build immediate
3 refresh fast on demand
4 as
5 select * from scott.emp e
6 where
7 exists (select 1 from scott.dept d1
8 where
9 d1.deptno = e.deptno)
10 union
11 select * from scott.emp e
12 where
13 exists (select 1 from scott.bonus b
14 where
15 b.ename = e.ename)
16 /
Materialized view created.
SQL>
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
materialized view over a database link
vll, December 16, 2003 - 3:37 pm UTC
Tom,
are there any additional restrictions on creating materialized views over a database link? Is it possible to have "refresh fast", "refresh on commit"? Thank you very-very much
December 16, 2003 - 5:42 pm UTC
you cannot refresh on commit -- but you can refresh fast, yes (i prefer to call it "refresh incremental" since it can be slower then a complete refresh of course!)
ORA-00600 while creating materialized view.
Udayan Gupt, August 08, 2006 - 11:02 am UTC
Hi Tom,
Well I get a different kind of an error while trying to create a materialized view.
Its the oracle internal error and the first argument is [16608]
This happened when I dropped a materialized view and I want to recreate it using the original script.
The snapshot uses a select query over a dblink, which selects about seventeen thousand records. The query is working fine, but the snapshot does not get created.
Btw, the error message says that the error has occured in the dblink
August 09, 2006 - 9:52 am UTC
ora-600
ora-7445
ora-3113
all of those imply "please utilize support"