Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-16
6
Medium Priority
?
1,308 Views
Last Modified: 2008-01-09
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

0
Comment
Question by:fba_mainsys
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:neo9414
ID: 13556715
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
 
LVL 11

Accepted Solution

by:
cjjclifford earned 200 total points
ID: 13556722
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
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13556767
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:fba_mainsys
ID: 13556796
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
 

Author Comment

by:fba_mainsys
ID: 13556846
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
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13557009
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question