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

fba_mainsysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

neo9414Commented:
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
0
cjjcliffordCommented:
remove the B.typcpt = 'X' from the view definition, this is not allowed on FAST REFRESH views... you're view will contain everything, you'll just have to ignore the values that you are not interested...

You should also be able to do a COMPLETE REFRESH that does include this argument, and since you are only refreshing once a day, I don't see COMPLETE REFRESH being a problem (could actually be faster than FAST REFRESH, depending on the amount of changes that have been made!!!)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cjjcliffordCommented:
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!!!
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fba_mainsysAuthor Commented:
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.

0
fba_mainsysAuthor Commented:
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
0
cjjcliffordCommented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.