P: n/a
|
It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?
Thanks
M. Mehta
Please follow my example below:
created 2 tables:
select * from emp;
EMP_ID DEPT_ID SAL
---------- ---------- ----------
1 1 20000
2 1 50000
select * from dept;
DEPT_ID DEPT_NAME
---------- ----------
1 HR
2 IT
3 CC
create materialized view (with out outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id
5 group by b.dept_id, b.dept_name
6 /
Materialized view created.
SQL> drop materialized view mv_test;
create materialized view (WITH outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id (+)
5 group by b.dept_id, b.dept_name
6 /
from emp a, dept b
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view | |
Share this Question
P: n/a
|
M. Mehta wrote: It seems that you can not create a materialized view if you are using outer joins...can someone please verify this?
Thanks M. Mehta
Please follow my example below:
created 2 tables:
select * from emp;
EMP_ID DEPT_ID SAL ---------- ---------- ---------- 1 1 20000 2 1 50000
select * from dept;
DEPT_ID DEPT_NAME ---------- ---------- 1 HR 2 IT 3 CC
create materialized view (with out outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id 5 group by b.dept_id, b.dept_name 6 /
Materialized view created.
SQL> drop materialized view mv_test;
create materialized view (WITH outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id (+) 5 group by b.dept_id, b.dept_name 6 / from emp a, dept b * ERROR at line 3: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Versions may help... this is 9.2.0.4:
SQL> connect system/manager
Connected.
SQL> grant create materialized view to hr;
Grant succeeded.
SQL> connect hr/hr
Connected.
SQL> @t
Materialized view created.
SQL> l
1 create materialized view mv_test build immediate refresh complete
2 on commit as
3 select count(*), b.department_id, b.department_name, sum(salary)
salary,cou
nt(salary)
4 from employees a, departments b
5 where a.department_id = b.department_id (+)
6* group by b.department_id, b.department_name
--
Regards,
Frank van Bortel | |
P: n/a
|
I gave the following permission: grant create materialized view to hr;
and I still get the following error:ORA-12054: cannot set the ON
COMMIT refresh attribute for the materialized view.
Here is what I am doing: ( Release 9.2.0.4.0 )
CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
ON COMMIT AS
SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@netscape.net> wrote in message news:<c8**********@news3.tilbu1.nb.home.nl>... M. Mehta wrote: It seems that you can not create a materialized view if you are using outer joins...can someone please verify this?
Thanks M. Mehta
Please follow my example below:
created 2 tables:
select * from emp;
EMP_ID DEPT_ID SAL ---------- ---------- ---------- 1 1 20000 2 1 50000
select * from dept;
DEPT_ID DEPT_NAME ---------- ---------- 1 HR 2 IT 3 CC
create materialized view (with out outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id 5 group by b.dept_id, b.dept_name 6 /
Materialized view created.
SQL> drop materialized view mv_test;
create materialized view (WITH outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id (+) 5 group by b.dept_id, b.dept_name 6 / from emp a, dept b * ERROR at line 3: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Versions may help... this is 9.2.0.4: SQL> connect system/manager Connected. SQL> grant create materialized view to hr;
Grant succeeded.
SQL> connect hr/hr Connected. SQL> @t
Materialized view created.
SQL> l 1 create materialized view mv_test build immediate refresh complete 2 on commit as 3 select count(*), b.department_id, b.department_name, sum(salary) salary,cou nt(salary) 4 from employees a, departments b 5 where a.department_id = b.department_id (+) 6* group by b.department_id, b.department_name | |
P: n/a
|
Maybe will help, if you create on base tables "MATERIALIZED VIEW LOG"
regards, Matej
"M. Mehta" <ml****@yahoo.com> wrote in message
news:a7**************************@posting.google.c om... I gave the following permission: grant create materialized view to hr; and I still get the following error:ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view.
Here is what I am doing: ( Release 9.2.0.4.0 )
CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT AS SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal) FROM EMP a, DEPT b WHERE a.dept_id = b.dept_id (+) GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@netscape.net> wrote in message
news:<c8**********@news3.tilbu1.nb.home.nl>... M. Mehta wrote: It seems that you can not create a materialized view if you are using outer joins...can someone please verify this?
Thanks M. Mehta
Please follow my example below:
created 2 tables:
select * from emp;
EMP_ID DEPT_ID SAL ---------- ---------- ---------- 1 1 20000 2 1 50000
select * from dept;
DEPT_ID DEPT_NAME ---------- ---------- 1 HR 2 IT 3 CC
create materialized view (with out outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id 5 group by b.dept_id, b.dept_name 6 /
Materialized view created.
SQL> drop materialized view mv_test;
create materialized view (WITH outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as 2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal) 3 from emp a, dept b 4 where a.dept_id = b.dept_id (+) 5 group by b.dept_id, b.dept_name 6 / from emp a, dept b * ERROR at line 3: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Versions may help... this is 9.2.0.4: SQL> connect system/manager Connected. SQL> grant create materialized view to hr;
Grant succeeded.
SQL> connect hr/hr Connected. SQL> @t
Materialized view created.
SQL> l 1 create materialized view mv_test build immediate refresh complete 2 on commit as 3 select count(*), b.department_id, b.department_name, sum(salary) salary,cou nt(salary) 4 from employees a, departments b 5 where a.department_id = b.department_id (+) 6* group by b.department_id, b.department_name | | This discussion thread is closed Replies have been disabled for this discussion. | | Question stats - viewed: 17109
- replies: 3
- date asked: Jul 19 '05
|