Home » SQL & PL/SQL » SQL & PL/SQL » Usage of materialized views (WINXP)
Usage of materialized views [message #328240] Thu, 19 June 2008 08:24 Go to next message
mbhkr5
Messages: 20
Registered: October 2007
Junior Member
Hi All,

Can the changes done in materialized view be reflected in the main table?
If so can anyone give the syntax for the same?

Thanks in advance

Regards
Joshna
Re: Usage of materialized views [message #328250 is a reply to message #328240] Thu, 19 June 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can the changes done in materialized view be reflected in the main table?

By default, no.
If you need it is not an easy task read FOR UPDATE Clause and follow the link.

Regards
Michel
Re: Usage of materialized views [message #328346 is a reply to message #328240] Thu, 19 June 2008 16:49 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
The whole idea of a materialized view is a static view of data achieved through extensive calculations or complex, time consuming SQL. Even if you do change the underlying data, the view will not change until it's refreshed.

I would assume that if you want to, you could look at instead of triggers. I've never even tried to see if they work against materialized views.
Re: Usage of materialized views [message #328371 is a reply to message #328346] Thu, 19 June 2008 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rdebruyn

You should also read the link I posted and follow the link it contains. You will learn something about updatable mviews.

Regards
Michel
Re: Usage of materialized views [message #328395 is a reply to message #328371] Fri, 20 June 2008 01:11 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
Michel Cadot wrote on Fri, 20 June 2008 00:12


You should also read the link I posted and follow the link it contains. You will learn something about updatable mviews.




This is a pretty specific case about materialized views based on tables in a remote database. The example was:
CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE
AS SELECT * FROM sh.customers@remote cu
WHERE EXISTS
(SELECT * FROM sh.countries@remote co
WHERE co.country_id = cu.country_id);

Now, if a materialized view is a static copy of the data at the time it was created, what happens when the tables in the remote database change? If it constantly has to be refreshed, what's the use? Why not directly access the tables?
Re: Usage of materialized views [message #328403 is a reply to message #328395] Fri, 20 June 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is a pretty specific case about materialized views based on tables in a remote database.

Your idea of mview is specific, it is much more than that.

Quote:
Now, if a materialized view is a static copy of the data at the time it was created,

What is asked is the opposite, to be able to update mview as well as base table.

Quote:
Why not directly access the tables?

Low speed or asynchroneous network?
Not wanted remote updates during work hours?
...

Regards
Michel
Re: Usage of materialized views [message #328424 is a reply to message #328240] Fri, 20 June 2008 02:49 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sometimes situation may be like this:

You have a table B whick tracks the flow of queues from one application to the other.

Say requirement is something like that user wants to see the queue counts based on the application & location and timestamp ( like give me the count of the queues which has reached into the systems 1 hr ago, 2 hr ago)Something like this.

You are showing this count through a web page.Say its being filtered like this..
Based on location and application give me the count for the queue.
Say user has got the count 10 ( in last 1 hr),20 ( in between 2 -3 hrs).. based on the application and location.
But the moment he wants to see the detail on the queue he may not get the adject count details (with queue name, at this instant he may got less/more queue names as table has a volatile data).The queue may be move to a different system.
That means the table B has volatile information, its not static at that certain time.

Whats the best of doing so that customer wont get confused?
The best way to do it is to show static data and telling the user that this data is going to be refreshed after every 10 min.

I feel the best way of doing it is using MView.



Suggestion please...

Regards,
oli



[Updated on: Fri, 20 June 2008 03:00]

Report message to a moderator

Re: Usage of materialized views [message #328449 is a reply to message #328424] Fri, 20 June 2008 04:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any query on the table will show exactly the data that was in the table at the instant the query was executed - you can't get more accurate than that, surely?

Re: Usage of materialized views [message #328451 is a reply to message #328449] Fri, 20 June 2008 04:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Fri, 20 June 2008 04:08
Any query on the table will show exactly the data that was in the table at the instant the query was executed - you can't get more accurate than that, surely?




I said, "At one instant user gets The query count based on location for a particular application.this is a summarised data.the moment he wants to see the queue details (queue names with count) he may get different result.That means users may get volatile data the moment he clicks on the queue details as the queue has been moved to a different application/location.User may get confused here.

So what I suggests is that show user the 10/15 min old snapshot of data.Let the user know that in next 15 minutes its going to be changed and thts why I used mview.At this point data will be static as it is being queried using mview.At least data will not be volatile the moment he wants to see the queue details. Say, summary queue count is showing as 10 ( 60 min ago), 20 ( in between 1 -2 hrs)at a particular instant.The moment user wants to see the queue details for this count he may not got the correct details for the count as the queue has already been moved to a different application at that instant --Did you get what I mean? suggestion please...

[Updated on: Fri, 20 June 2008 05:10]

Report message to a moderator

Re: Usage of materialized views [message #328475 is a reply to message #328451] Fri, 20 June 2008 05:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have to disagree. Surely it is better to provide the user with more accurate information, even if it disagrees with information that they saw a few minutes ago.

If your data is changing slowly enough that 15 minute old data is still accurate enough to use, then the number of times that the data is different should, by definition, be fairly rare.
Re: Usage of materialized views [message #328482 is a reply to message #328475] Fri, 20 June 2008 05:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Fri, 20 June 2008 05:28
I'd have to disagree. Surely it is better to provide the user with more accurate information, even if it disagrees with information that they saw a few minutes ago.

If your data is changing slowly enough that 15 minute old data is still accurate enough to use, then the number of times that the data is different should, by definition, be fairly rare.


I even cant deny you.Thanks for your reply.But how or whats the best way to handle such situation?
I am using Materalized view with FAST refresh. But here problem is that log is maintained. If 20,000 or more 50,000 records has been inserted
it may be a overhead.If I use COMPLETE refresh, then at a single point of time it may return wrong records ( as snapshot will get truncated at a particular instant ).The tables being used is indexed table.

What would be the best feasible approach?
Regards,
Oli

[Updated on: Fri, 20 June 2008 05:45]

Report message to a moderator

Re: Usage of materialized views [message #328506 is a reply to message #328482] Fri, 20 June 2008 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you are going to insist on providing out of date information, then a fast refresh would be the best solution.

Re: Usage of materialized views [message #328509 is a reply to message #328506] Fri, 20 June 2008 06:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Fri, 20 June 2008 06:27
If you are going to insist on providing out of date information, then a fast refresh would be the best solution.





I did ask about how to handle such situation.Didnt get any reply to that.I did request you to provide some suggestion- the feasible way of handling such situation.

Thanks for the suggesion.But here again log is created. If more than 50,000+ of records inserted would it not be a overhead? And also, I cant go for Complete refresh(already mentioned why)

Please make a note that the table I am using is a indexed table.Its not that Data is not changing slowly.To provide user the most updated information is always the best approach.As I told you data's are being volatile here.Its moving from one application/location to other.

Here we are showing the users the count based on application and location.And these counts are based on some specific period like give me the number of queues that came to the particular application for the particular location 59 minutes ago, in beteen 1-2 hrs and so on.Initially, we are showing the queue count(summarized) based on application/location.
and then look for the details for all those queue counts.

Waiting for suggesion to handle such situation..
Regards,
Oli

[Updated on: Fri, 20 June 2008 06:44]

Report message to a moderator

Re: Usage of materialized views [message #328527 is a reply to message #328509] Fri, 20 June 2008 07:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If there is much data, and the data is volatile then you might want to consider the following scenario we use for our sales data:

There is one materialized view with the data for the current months, which gets refreshed once every day.

Then there is one materialized view with the data for the current day, which gets refreshed every 5 minutes.

All report queries use a "master" view that is created over those two materialized views to sum them together so they appear as one to the reporting tool.

Maybe you could use a similar approach, that you have one MV with the bulk of the data, and a smaller one with just the recent data that gets refreshed more quickly. You could even add a third "normal" view which selects the remaining data directly from the transaction tables, if that is feasible in your environment and add that also to she master view.
Re: Usage of materialized views [message #328535 is a reply to message #328527] Fri, 20 June 2008 07:21 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
ThomasG wrote on Fri, 20 June 2008 07:03
If there is much data, and the data is volatile then you might want to consider the following scenario we use for our sales data:

There is one materialized view with the data for the current months, which gets refreshed once every day.

Then there is one materialized view with the data for the current day, which gets refreshed every 5 minutes.

All report queries use a "master" view that is created over those two materialized views to sum them together so they appear as one to the reporting tool.

Maybe you could use a similar approach, that you have one MV with the bulk of the data, and a smaller one with just the recent data that gets refreshed more quickly. You could even add a third "normal" view which selects the remaining data directly from the transaction tables, if that is feasible in your environment and add that also to she master view.



Thanks for the suggestion.That sounds great. But here again, use of space comes into picture.Isnt it? Wont then we have to maintain logs for three views for the same base table??

I didnt understand when you asked me to use master view based on the two views you have mentioned.Can you please demonstrate with an example.

This is what I did:

CREATE MATERIALIZED VIEW MV_MQ_TABLE
   	REFRESH FAST START WITH SYSDATE 
	NEXT SYSDATE + 10/1440
      --- Here interval is 10 minutes
	WITH PRIMARY KEY 
	AS SELECT * FROM MQ_TABLE;





Yes,there is much data, and the data is volatile. So how I would handle this? Need your help!

[Updated on: Fri, 20 June 2008 07:22]

Report message to a moderator

Re: Usage of materialized views [message #328537 is a reply to message #328535] Fri, 20 June 2008 07:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Wont then we have to maintain logs for three views for the same base table??



Yes, but the update for the bulk of the data won't happen as often.

An example based on your MV :

(untested, since I don't have the tables)

CREATE MATERIALIZED VIEW MV_MQ_TABLE_1
   	REFRESH FAST START WITH trunc(SYSDATE) + 1
	NEXT trunc(SYSDATE) + 1
        --- Here refresh is  every day at 00:00
	WITH PRIMARY KEY 
	AS SELECT * FROM MQ_TABLE 
        where some_appropriate_date_column < trunc(sysdate);

CREATE MATERIALIZED VIEW MV_MQ_TABLE_2
   	REFRESH FAST START WITH SYSDATE 
	NEXT SYSDATE + 5/1440
        --- Here interval is 5 minutes
	WITH PRIMARY KEY 
	AS SELECT * FROM MQ_TABLE
        where some_appropriate_date_column >= trunc(sysdate);

CREATE VIEW VW_MQ_TABLE as 
  select * from VIEW MV_MQ_TABLE_1
union all
  select * from VIEW MV_MQ_TABLE_2;


Re: Usage of materialized views [message #328539 is a reply to message #328537] Fri, 20 June 2008 07:41 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Great.Thats really sounds great!

Thanks for your valuable suggesion Thomas.Thanks for spending more time on this issue.
Also, I appreciate Jrowbottom for giving his time on this issue.
Thanks & Best Regards,
Oli

[Updated on: Fri, 20 June 2008 08:13]

Report message to a moderator

Re: Usage of materialized views [message #328555 is a reply to message #328240] Fri, 20 June 2008 08:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

where some_appropriate_date_column < trunc(sysdate);
where some_appropriate_date_column > trunc(sysdate);



@ThomasG: some_appropriate_date_column is INS_TMS (timestamp). Then how I would modify the above conditions?
Re: Usage of materialized views [message #328557 is a reply to message #328555] Fri, 20 June 2008 08:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
They should work with timestamp also, without any modification except for using the correct column name of course.

But notice, I didn't use < and > but < and >=, otherwise records which have a time(stamp) of 00:00:00.00000 will not be transferred.
Re: Usage of materialized views [message #328560 is a reply to message #328557] Fri, 20 June 2008 08:24 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thank you very much!! Razz
Previous Topic: How to convert Oracle datas to MS Access file
Next Topic: Query Help
Goto Forum:
  


Current Time: Fri Dec 02 18:46:31 CST 2016

Total time taken to generate the page: 0.47888 seconds