Performance Issues

[Chengwei Lee]

Hi guys & gals,

I've a query on performance issue that hopefully you'll could share with me your experiences & knowledge.

Lets say I were to create a view to aggregrate a few tables together, then perform a query on the view with a few conditions.

Suppose instead of creating the view, I still keep all the select & joins statements, then I append the same conditions to it, will this approach yield the same performance or would it be better or worse than the view method?

I was told that by using the view, the database would actually query all the data from all the tables involved, then only the database will use the conditions to filter the results further. And this actually is not as good as the 2nd method.

What do you guys think? Or do you've a better approach to handle such performance issues?

Many thanks for sharing & helping.

[Jeanne Boyarsky]

Chengwei,
That is correct. The database will create the view (do all the joins) before doing your query.

Unless you are using a materialized view, in which case the joins are already done.

[Chengwei Lee]

Hi Jeanne,

Originally posted by Jeanne Boyarsky:
Unless you are using a materialized view, in which case the joins are already done.

What do you mean by materialized view? I'm not exactly familiar with DBMS. So if I'm using materialized view, the performance issue would not exist?

Thank you so much for your help!

[Jeanne Boyarsky]

Chengwei,
A materialized view is actually something specific to Oracle. It's like a cached local copy of a query. This database journal article has a more detailed explanation.

If you use a materialized view, you would not be repeating the join and could skip right to the where clause. You need to see if this makes sense for your data though. If your data changes frequently, storing the results of the join would not help.

[Chengwei Lee]

Jeanne, thanks for the link.