?
Solved

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

Posted on 2005-03-16
6
Medium Priority
?
1,280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 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