Help with creating fast updating materialised view [message #275298] |
Fri, 19 October 2007 05:43 |
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 #275304 is a reply to message #275300] |
Fri, 19 October 2007 06:35 |
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 #275312 is a reply to message #275309] |
Fri, 19 October 2007 07:27 |
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 #275324 is a reply to message #275318] |
Fri, 19 October 2007 08:23 |
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 |
|
Michel Cadot
Messages: 68651 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
|
|
|