OraFAQ Forum: SQL & PL/SQL » materialized view issues

Home » SQL & PL/SQL » SQL & PL/SQL » materialized view issues

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

materialized view issues [message #199482]

Tue, 24 October 2006 14:12

lotusdeva
Messages: 201
Registered: March 2005

Senior Member

ok, I am having a weird problem. I have a materialized view that is created with the following options:

CREATE MATERIALIZED VIEW WEBSERVICEEMPLDATA_MV
NOCACHE
NOLOGGING
BUILD IMMEDIATE
REFRESH START WITH SYSDATE NEXT (SYSDATE + 1/300)
WITH PRIMARY KEY
AS
SELECT DISTINCT ....

The query is very complicated, as it contains lots of joins. It takes 1.5. minutes to create the view. It refreshes fine. But, while it refreshes, the data from the view is not available. The thing is that an online application uses this view - hence, the data has to be real time. The view refreshes every 5 minutes and the select in the view takes about 1.5 min to run, so for about 1.5 minutes there are 0 records in the view. I understand that we are forcing Oracle to completely recreate the view every 5 minutes. But why is there no data availble at all? Shouldn't there be some sort of snapshot - like, while it is being recreated, the users can still get some data?

thanx ...

Report message to a moderator

Re: materialized view issues [message #199509 is a reply to message #199482]

Tue, 24 October 2006 21:34

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Hmmm. Seems like Oracle is truncating the table for complete refresh. This is not unexpected, as its clearly the easiest way to get rid of all rows for a complete refresh.

The problem with TRUNCATE is that it is DDL, so it commits. If the refresh used DELETE then it would not need to commit until the refresh was complete.

I don't know if there is a way to force the refresh to DELETE instead of TRUNCATE. I have two thoughts:

1. You could Hash Cluster the MV - you can't truncate a hash-clustered table (although you can truncate the underlying cluster), so Oracle might be forced to DELETE.

This is pretty extreme, and raises all sorts of other problems of maintenance, storage, and FTS performance. Its really a stupid idea, I only mention it because its novel.

2. In 10g, you might be able to create a view over the MV that uses Flashback to see a table at an old point-in-time. Before each refresh, you would need to alter the view to set the flashback attributes, and then remove them after refresh is complete.

I'm not in love with this idea is either, but I'm struggling for anything else.

Oracle mentions 24-7 availability in its Data Warehousing manual. It says that EXCHANGE PARTITION is a good way to present new data to the warehouse with zero-downtime. I'm not sure how you could use this in combination with the MV though - its just food for thought.

Maybe the best solution is just to forget MVs, and use DBMS_JOB or DBMS_SCHEDULER to do the rebuild.
1. Create a view or synonym that points to the table. The users use the view/synonym not the table.
2. Use DBMS_JOB to build a new copy of the table with a new name.
3. Rebuild the view to point to the new table
4. Drop the old table.

Ross Leishman

Report message to a moderator

Re: materialized view issues [message #199659 is a reply to message #199482]

Wed, 25 October 2006 10:52

lotusdeva
Messages: 201
Registered: March 2005

Senior Member

Thank you for your ideas! I think #3 will work the best. Let me make sure I understand it though.

1.
So I would create regualr view, correct? Then use DBMS_JOB or DBMS_SCHEDULER to do the rebuild? When we create regualr view, doesnt it get refreshed automatically whenever there is a record that is commited to the database? If so, why do we need to use these packages?

2. I am not sure what you mean here. We would use DBMS_JOB to build a new copy of a view with a new name? You mention table. What table? Do you mean talbes (I have 8 of them) that the view is based on? Or do you mean use DBMS_JOB to build a new copy of view with a new name. So say original view was MY_VIEW. We would use the above package to create MY_VIEW_COPY from MY_VIEW?

3. Isnt this the same as #2?

4. Which table?

Sorry, I am not sure what you mean by the table here I guess. The query that underlyines the view is complicated as it joins like 8 tables...

Thank you!

Report message to a moderator

Re: materialized view issues [message #199660 is a reply to message #199659]

Wed, 25 October 2006 10:59

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

SQL> create table taj as select * from all_objects where 1 = 2;

Table created.

SQL> create or replace view taj_view as select * from taj;

View created.

SQL> create table taj_new as select  * from taj;

Table created.

SQL> create or replace view taj_view as select * from taj_new;

View created.

SQL> drop table taj purge;

Table dropped.

SQL>

Just an example for better option wait Ross post.

Report message to a moderator

Re: materialized view issues [message #199670 is a reply to message #199482]

Wed, 25 October 2006 13:01

lotusdeva
Messages: 201
Registered: March 2005

Senior Member

Thanks for the idea. Does regular view get automatically refreshed? I mean if the user inserts some records to the base table, what makes the view to refresh its data?

Report message to a moderator

Re: materialized view issues [message #199693 is a reply to message #199670]

Wed, 25 October 2006 21:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

In @user52's excellent example, all_objects is the base table. When new data is added to all_objects, it is not visible to the table taj, taj_new, or to the view taj_view.

When the summary process runs, it builds the table taj_new. But the view is still pointing to table taj, so none of the changes are yet visibile.

Then the view is rebuilt to point to taj_new instead of taj - changes made by the rebuild are now instantly visible.

On the next rebuild, we cannot create the table taj_new, because it is currently in use by the view. So you would need to use the old table name (taj) again, and then rebuild the view to point back to that table.

I hope this is clear now. If not, talk it over with some of your colleages and get them to read this thread.

Ross Leishman.

Report message to a moderator

Re: materialized view issues [message #199904 is a reply to message #199482]

Thu, 26 October 2006 12:37

lotusdeva
Messages: 201
Registered: March 2005

Senior Member

thank you for everyones input! The problem is that regular view is not going to work in my case. We have created MV to imroove perfomance. Regular view takes just as long to run as the select itself. So we would have to stick with MV. Is there any way I can force Oracle to DELETE rather then TRUNCATE during the refresh? Any other ideas? Thank you!

Report message to a moderator

Re: materialized view issues [message #199919 is a reply to message #199904]

Thu, 26 October 2006 21:14

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You haven't understood properly. Read it over again.

The view is NOT over the original base table. It is over the summarised table, which is EXACTLY the same as an MV, except it is refreshed manually by DBMS_JOB rather than by the MV refresh mechanism.

The ONLY difference in building it manually is that the old summary is available whilst the new summary is building, which is what you asked for in the first place.

Ross Leishman

Report message to a moderator

Previous Topic:	Tuning Help - Can this be sped up??
Next Topic:	related to page size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 02:27:09 CST 2024