Home » SQL & PL/SQL » SQL & PL/SQL » Set Numeric Precision when using MViews.
Set Numeric Precision when using MViews. [message #189962] Mon, 28 August 2006 13:58 Go to next message
sjs30101
Messages: 4
Registered: August 2006
Junior Member
I'm using materialized views and want to set the numeric precision on the columns that are averaged but have not been able. The following is a simple MView for example;

CREATE MATERIALIZED VIEW mv_OPIS_DSL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT ZIP,
START_DATE,
Avg(RETAIL),
Count(RETAIL),
Avg(MARGIN),
Count(MARGIN),
WEEK_NO
FROM OPIS_DSL
GROUP BY ZIP,
START_DATE,
WEEK_NO


The two 'average' columns precision varies greatly and would like to set to some fixed value if possible.

Please help.
Re: Set Numeric Precision when using MViews. [message #189989 is a reply to message #189962] Mon, 28 August 2006 23:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
One way is to pre-build the table, and then create the MV
ON PREBUILT TABLE
WITH REDUCED PRECISION


There was another example in this forum this week where the CAST() function was used to set the data type of a column with accuracy when creating a regular view. Do a search.

Ross Leishman
Re: Set Numeric Precision when using MViews. [message #190073 is a reply to message #189989] Tue, 29 August 2006 04:01 Go to previous messageGo to next message
sjs30101
Messages: 4
Registered: August 2006
Junior Member
Ross, thanks for the reply.

I tried using the 'Prebuilt' option but did not work with Fast Refresh On Commit. When I tried CAST, I received an error saying the MView could not be 'created' with On Commit.

Thanks,

Steve
Re: Set Numeric Precision when using MViews. [message #190080 is a reply to message #190073] Tue, 29 August 2006 04:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sjs30101 wrote on Tue, 29 August 2006 11:01

When I tried CAST, I received an error saying the MView could not be 'created' with On Commit.
An error? Why don't you share the creation statement + the error message?

MHE
Re: Set Numeric Precision when using MViews. [message #190131 is a reply to message #190080] Tue, 29 August 2006 06:02 Go to previous messageGo to next message
sjs30101
Messages: 4
Registered: August 2006
Junior Member
I used CAST as follows;

CREATE MATERIALIZED VIEW mv_OPIS_DSL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT ZIP,
START_DATE,
Cast(Avg(RETAIL) as Number(5,3)),
Count(RETAIL),
Cast(Avg(MARGIN) as Number(5,3)),
Count(MARGIN),
WEEK_NO
FROM OPIS_DSL
GROUP BY ZIP,
START_DATE,
WEEK_NO

and received this error;

ORA-12054: Can not set the ON COMMIT REFRESH attribute for the materialized view.

Thanks for the assistance.
Re: Set Numeric Precision when using MViews. [message #190258 is a reply to message #190131] Tue, 29 August 2006 22:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've done quite a bit of work with FAST REFRESH, but not with ON COMMIT. You'll have to be my guinea pig.

Try creating it ON PREBUILT TABLE WITH REDUCED PRECISION, but with REFRESH FAST ON DEMAND rather than ON COMMIT. I have done this lots (in 10g though) and I know it works. Now COMPLETE REFRESH the MV, and then ALTER it to FAST REFRESH ON COMMIT.

If you can't even create it with REFRESH FAST ON DEMAND, then you have another problem. Run it through DBMS_MVIEW.EXPLAIN_MVIEW to find out why.


Ross Leishman
Re: Set Numeric Precision when using MViews. [message #190660 is a reply to message #190258] Thu, 31 August 2006 10:25 Go to previous messageGo to next message
sjs30101
Messages: 4
Registered: August 2006
Junior Member
Hi Ross;

Great solution (it worked of course)!

My sincere thanks for your time and patience.

Steve
Re: Set Numeric Precision when using MViews. [message #190718 is a reply to message #190660] Thu, 31 August 2006 23:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
sjs30101 wrote on Fri, 01 September 2006 01:25

Hi Ross;

Great solution (it worked of course)!

My sincere thanks for your time and patience.

Steve


Yeah, the ones that don't work are still pretty good, but less practical Wink
Previous Topic: update statement
Next Topic: flat files......
Goto Forum:
  


Current Time: Mon Dec 05 08:39:58 CST 2016

Total time taken to generate the page: 0.22692 seconds