Home » Server Options » Replication » Help with creating fast updating materialised view (Cannot create a FAST materialised view of simple aggregates.)
Help with creating fast updating materialised view [message #275298] Fri, 19 October 2007 05:43 Go to next message
temple_cloud
Messages: 4
Registered: October 2007
Location: Bristol
Junior Member
Hello All,

I hope you can help. I am java developer and not experienced with Oracle but I am trying to leverage Oracle to create some aggregate tables. I am trying to create a FAST materialised view of simple aggregates.


I have a base table that I have defined a materilaised view log on it (this specifies ALL columns in the underlying table - correct?)

CREATE MATERIALIZED VIEW LOG ON HSDCube_FACT
WITH SEQUENCE, ROWID
(
DEPARTUREDATE,
NUMBEROFNIGHTS,
DEPARTUREPOINTCODE,
ARRIVALPOINTCODE,
CLASSCODE,
BOARDCODE,
FLAVOUR,
LEADINPRICE,
OPERATORCODE,
DEPARTUREDATETIME,
ACCOMMODATIONID,
DEPARTURETRAVELCODE,
RETURNTRAVELCODE,
PRICEID
)
INCLUDING NEW VALUES;


I am then trying to create a fast updating materilazed view from this table:

CREATE MATERIALIZED VIEW dpc_level_mv
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT DISTINCT DEPARTUREPOINTCODE,
min(LEADINPRICE) AS MIN_LEADINPRICE,
max(LEADINPRICE) AS MAX_LEADINPRICE,
avg(LEADINPRICE) AS AVG_LEADINPRICE,
count(*) AS COUNT
FROM HSDCube_FACT
GROUP BY DEPARTUREPOINTCODE
ORDER BY DEPARTUREPOINTCODE

Upon execution I get the error:

ORA-12015: cannot create a fast refresh materialized view from a complex query

I have looked at the docco and tried a few other queries but cannot get it to work with the "REFRESH FAST" option... which for my application is definately required. I have looked carefully at the docco for "complex queries" and "aggregates" and they seem to suggest the materialisation should work for my query.... so I am left scratching my head...

IF anyone can help me before my head falls off, I would be eternaly thankfull.

Apologies if the answer to my query is well known; or I have some pebcak...


Thanks for reading...

Tim

[Updated on: Fri, 19 October 2007 05:44]

Report message to a moderator

Re: Help with creating fast updating materialised view [message #275300 is a reply to message #275298] Fri, 19 October 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post your Oracle version (don't you think there are changes between them?)
2/ Trust Oracle, if it says it can't then it can't
3/ In order to refresh fast you must have mv logs
4/ Read and follow OraFAQ Forum Guide, especially "How to format your post?" section. Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button
5/ What is the use of DISTINCT?
6/ What is the use of ORDER BY?

Regards
Michel
Re: Help with creating fast updating materialised view [message #275304 is a reply to message #275300] Fri, 19 October 2007 06:35 Go to previous messageGo to next message
temple_cloud
Messages: 4
Registered: October 2007
Location: Bristol
Junior Member
Hello Michel,

Thank you for the reply. Apologies for the bad post.
I was not intending to be troublesome.


#1: The version of Oracle I am using is:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

#2: I trust the error and the fact that oracle cant do it the way I specified;
I was trying to get help regarding if I had
specified it incorrectly.

#3: I have the table logs as specified in the initial DDL
statement. Is more required?

#4: Have done. Apologies. My bad.

#5/#6: Agreed, although they should also not break it. They are
present because I was experimenting getting it to work. If they
are removed I get the same error.

So you are suggesting Oracle simply cannot maintain this as a
fast updating materialised view?

Thanks,

Tim

Re: Help with creating fast updating materialised view [message #275309 is a reply to message #275304] Fri, 19 October 2007 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try without DISTINNT and ORDER BY?
SQL> create materialized view t_mv refresh fast as select distinct id, nam from t;
create materialized view t_mv refresh fast as select distinct id, nam from t
                                                                           *
ERROR at line 1:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> create materialized view t_mv refresh fast as select id, nam from t;

Materialized view created.

Regards
Michel
Re: Help with creating fast updating materialised view [message #275312 is a reply to message #275309] Fri, 19 October 2007 07:27 Go to previous messageGo to next message
temple_cloud
Messages: 4
Registered: October 2007
Location: Bristol
Junior Member
Hi Michel,

Thanks for your time and help.

Yes, I have tried several combinations, including
without the distinct and order by clauses...

The following query fails with the same exception...


CREATE MATERIALIZED VIEW dpc_level_mv
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT DEPARTUREPOINTCODE,
min(LEADINPRICE) AS MIN_LEADINPRICE,
max(LEADINPRICE) AS MAX_LEADINPRICE,
avg(LEADINPRICE) AS AVG_LEADINPRICE,
count(*) AS COUNT
FROM HSDCube_FACT
GROUP BY DEPARTUREPOINTCODE


The query is complex becuase of the aggregates, but the
following web page seems to demonstrate such queries provided
that certain conditions are met:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#574241

Many of the examples seem more complex than mine.
I was wondering if I was missing a parameter or option.
As I am new to oracle I am a bit short-sighted when reading the
docco.

Anyway, thanks for the help. Much appreciated.

Tim
Re: Help with creating fast updating materialised view [message #275318 is a reply to message #275312] Fri, 19 October 2007 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case that I can reproduce and FORMAT IT.

Regards
Michel
Re: Help with creating fast updating materialised view [message #275324 is a reply to message #275318] Fri, 19 October 2007 08:23 Go to previous messageGo to next message
temple_cloud
Messages: 4
Registered: October 2007
Location: Bristol
Junior Member
Hi Michel...

Good news (for me)! I think I have solved it. I will
detail here for others.

In the url docs posted above it mentions that for aggregate
expressions avr min, max, etc. you need to define the count
of each aggregate expression in the table for fast refresh.

So the query should have been this:

create materialized view dpc_level_mv
build immediate
refresh fast
enable query rewrite
as
select
departurepointcode,
min(leadinprice) as min_leadinprice,
max(leadinprice) as max_leadinprice,
avg(leadinprice) as avg_leadinprice,
count(leadinprice) as leadinprice_count,
count(*) as count
from hsdcube_fact fact
group by
departurepointcode


In otherwords I had to add the "count(leadinprice)" to
allow it to fast update.

Without the aggregates the extra count field is not required.
So this also works:

create materialized view dpc_level_mv
build immediate
refresh fast
enable query rewrite
as
select
departurepointcode,
count(*) as count
from hsdcube_fact fact
group by
departurepointcode

If you have aggregates dependent on different expressions
then they also each need to have their own count column.

Apologies for the CAPS. My sql is autogenerated from my
java program.

Thanks for your help.
Re: Help with creating fast updating materialised view [message #275327 is a reply to message #275324] Fri, 19 October 2007 08:36 Go to previous message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was not the CAPS it was the use of code tags that was missing.

Thanks for the feedback, I thought count(*) would be sufficient, of course it is not as "leadinprice" could be null so result is not the same, I should think about that.

Regard
Michel
Previous Topic: Conflict Resolution
Next Topic: Help needed regarding Materialized view
Goto Forum:
  


Current Time: Wed Jul 23 23:48:33 CDT 2014

Total time taken to generate the page: 0.07427 seconds