asked on

Oracle Snapshot (materialized views) - WHERE clause on another table (ORA-12015)

Hi,

is it possible to make a snapshot including a WHERE clause naming another table.
like
CREATE MATERIALIZED VIEW A
TABLESPACE TS_DWEB_DATA
NOCACHE
LOGGING
NOPARALLEL
USING INDEX
TABLESPACE TS_DWEB_DATA
REFRESH FAST
START WITH TO_DATE('17-MAR-2005','dd-mon-yyyy')
NEXT TRUNC(SYSDATE + 1)
WITH PRIMARY KEY
AS
select a.
FROM a, b
WHERE a.numcpt = b.numcpt
AND B.typcpt = 'X'

I have this error :
ORA-12015: cannot create a fast refresh materialized view from a complex query

Can I avoid this problem or is it really impossible.

I work in oracle 9.2.0.4.

Thanks a lot

neo9414

actually you are missing the materialized view log. to be able to refresh fast you will need it.
or you can modify your materialized view to refresh complete

check the below oracle documentation for more info
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96520/mv.htm#721

ASKER CERTIFIED SOLUTION

cjjclifford

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cjjclifford

actually... is that a typo in the post, or in the materialized view code itself?

AS
select a.
FROM a, b
WHERE a.numcpt = b.numcpt
AND B.typcpt = 'X'

"select a." - doesn't specify what column!!!

fba_mainsys

ASKER

The materialized view log is OK.
The select is also OK, I type a litle bit to quick.

I will try to use the COMPLETE REFRESH to test, if it will work.

fba_mainsys

ASKER

Yes it's OK, it works in COMPLETE MODE.

I have just to test if it's better to make complete refresh with small tables or make a fast refresh with complete tables.

Thanks

cjjclifford

you can also create a silly reference table to hold the value required for the B.typcpt column...

CREATE TABLE silly_typcpt_ref AS SELECT 'X' typcpt FROM DUAL;

Then join on this in your materialized view... (Its the fact that there is a join on a constant, not on another table). For fast you'll have to also have a materialized view log on this silly table :-)

To help performance of the FAST refresh, add the ROWID of each master table to the materialized view select, base the Materialzied view logs "WITH ROWID", and create indexes on the materialized view on the various ROWID columns... hope this helps!