Home » RDBMS Server » Performance Tuning » Materialized View Vs Table functions (Oracle 9i)
Materialized View Vs Table functions [message #383609] Thu, 29 January 2009 05:26 Go to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
hi,

I need one clarification, which is giving better performance ? whether Materialized view or Table functions ?

Both will return some set of rows like 100,1000,10,000.
In our project, we are using Mviews to give some set of records, instead of using select query, its developed before 3 years, they are telling the reason why they have created Mview is it will stored in cache, it will give better performance than "Normal select query", ok, i am able to understand this point.

Now i have red that Table function also can able to return, some set of records like a " normal select query",
can we use it instead of Materialize view. ???

But which one will give better performance.

Thanks
Sundaravel N
Re: Materialized View Vs Table functions [message #383614 is a reply to message #383609] Thu, 29 January 2009 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Table function is just the same as your query in your case but less performant.

Regards
Michel
Re: Materialized View Vs Table functions [message #383620 is a reply to message #383609] Thu, 29 January 2009 05:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The big advantage of Mviews is that they let you perform complex queries ahead of time, and provide you with a materialized set of results that you can query from a single source, rather than having to go to all the individual tables every time. The blocks in the Mview can be cached in just the same way as blocks from a table.

The TABLE function lets you use a collection as a datasource for a quuery. It's pretty performant, but the problem is that yo uwill have to go and populate the collection , which will take time if the data source is a complex query.
Previous Topic: working with tkprof utility
Next Topic: explain plan
Goto Forum:
  


Current Time: Tue Dec 06 16:01:13 CST 2016

Total time taken to generate the page: 1.11817 seconds