Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View (10.2.0.3)
Materialized View [message #411696] Mon, 06 July 2009 06:33 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hello,

I have a user at a server with around 100 tablespaces.Each contain similar(in structure) tables containing millions of records.Now i need to create a table(could be materialized view too) that has the union of one table from each tablespace.I have two options
1)create a big table by union of all tables. If any change is done at a table then the big table is dropped and a new one is created at a new schema.
2)Create materialized view. Refresh is done only when changes occur.
Changes are very rare and refresh can be done at weekend.

Which could be better(In terms of space and size).

Thank You
Re: Materialized View [message #411716 is a reply to message #411696] Mon, 06 July 2009 07:16 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Materialised View Smile
Re: Materialized View [message #411830 is a reply to message #411716] Tue, 07 July 2009 01:28 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
But today ,I created a sample view by using union of such 2 tables.since the materialized view has union it couldn't be fast refreshed. I added 20K rows in one of the base table. Then complete refresh took 12 seconds where as inserting those 2 tables to a third new took nearly 3 secods. Also space taken by materialized view is same as that of the third table.

Then how can we justify that materialized view is better?
Re: Materialized View [message #411844 is a reply to message #411830] Tue, 07 July 2009 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I understood, there are 100 tables that are to be joined into a materialized view or another table. (BTW, why not an ordinary view?)

You have created a sample materialized view by using union of only 2 tables. What happens when you compare times necessary to complete this job by using 5, 10, 20 or 50 tables?
Re: Materialized View [message #411855 is a reply to message #411844] Tue, 07 July 2009 03:03 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Not ordinary view because,everytime i do run certain query in that view, everytime, it will perform union which is going to take a lot of time.
Now i can't test the same on 50 or 100 tables, because those tables are not populated yet. I have to implement the materialized joins as soon as thery are populated.
thank you
Re: Materialized View [message #411867 is a reply to message #411830] Tue, 07 July 2009 03:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
The materialized view should be thought of as a special kind of view, which physically exists inside the database, it can contain joins and or aggregates and exists to improve query execution time by pre-calculating expensive joins and aggregation operations prior to execution.
The Materialised View Refreshes Itself(based on the parameters you give while creating the view) when the parent tables are updated.While in case of your second idea,each time you refresh one of those 100 tables you have to go and manually create(update) the aggregate table.
Re: Materialized View [message #411872 is a reply to message #411855] Tue, 07 July 2009 04:02 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
The advantage I could think of using Materialized View is that you can set refresh option to commit and thus any changes to base tables will automatically reflected in Materalized View.

In other case you have to write a trigger to mantain this third table
Re: Materialized View [message #411927 is a reply to message #411830] Tue, 07 July 2009 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Also space taken by materialized view is same as that of the third table.

The materialized view will take up about as much space as all of the tables put together.
Re: Materialized View [message #411939 is a reply to message #411927] Tue, 07 July 2009 05:53 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The third table is (supposed to be) all of them put together:
maheshmhs
1) create a big table by union of all tables

Test was made on only two of them, so the result was the third one. I guess that that's what @bonker talks about.
Previous Topic: Deleting duplicate records.
Next Topic: DBMS_OUTPUT on UTL_FILE
Goto Forum:
  


Current Time: Tue Dec 06 02:31:56 CST 2016

Total time taken to generate the page: 0.11738 seconds