Skip to Main Content
  • Questions

  • Performance when a View using other Views

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 07, 2001 - 8:56 pm UTC

Last updated: June 22, 2005 - 4:46 pm UTC

Version: 7.X, 8.x

Viewed 1000+ times

You Asked

Tom,

Could you elaborate potential performance issues when selecting from a view that uses other views. The views inside of the view could be very complex view and may have group by and order by.

Your answer is very appreciated.



and Tom said...

There is NO difference between selecting from a VIEW based on other VIEWS and doing the select straight out -- if the two queries are equivalent.

This is where the mis-conception with regards to views comes in. If a view contains aggregates, outer joins, and some other complex operations -- the ability to "merge" your request into the view definition becomes limited (if not impossible). Most of the times when I see people complain about the performance of queries against views versus a straight out query -- it is because the two queries they are comparing (the query against the view and their well performing query) are not only syntactically different -- but they are SEMANTICALLY different.

This:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1275680497791 <code>

is the classic example of what I mean. The person writing that question wanted to know why we didn't push the predicate down into the view (which would be faster). He compared it to a query directly against the tables where he DID push it down himself. The problem is (as demonstrated) that the queries actually return DIFFERENT results. That is, we could NOT push the predicates down into the view, we could not merge the views together simply because that would change the outcome.

What I see frequently is someone will define a set of views with outer joins and aggregates but the query against them needs NEITHER the outer join or aggregate to be present (and hence is an inappropriate view to be used in this case). It is due to this additional work (and the reduced number of plans open to the query) that the view is deemed "slow".

When properly constructed and used -- a view of a view of a view of a view is no slower (or faster) then the straight query itself.

That aside -- it is many times easier to write the query directly against the base tables themself for performance (you are asking the specific question against the base tables directly) then it is to write the query using "generic" views not necessarily designed for your question.

So, don't be afraid of views of views (of views) -- just make sure they are being used properly (and don't hesitate to NOT query the other views in there when tuning -- go against the base tables when need be).



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Performance on views

A reader, September 08, 2001 - 11:58 am UTC

Tom's column is my must read

Muhammad Bilal, September 08, 2001 - 3:27 pm UTC

Thanks Tom

Performances on Views

A reader, September 10, 2001 - 2:00 pm UTC


Yogesh, August 26, 2004 - 7:55 am UTC

If I create a view on 10G table with predicates like col1<>10 and date1 is null group by col1, col2.

When I select from this view I'll get only those rows, which satisfy the predicate criteria. Where oracle store this extracted data? As view don't have any storage itself !! or it will always fetch it from base table?

Secondly, Every time a new row is added in the base table, view needs to be refreshed. Can this cause the performance bottleneck?




Tom Kyte
August 26, 2004 - 10:08 am UTC

a view is nothing more than a stored query that is retrieved from the data dictionary and plopped into your query.

create view v as select * from dual;

select * from v;

is the same as:

select * from (select * from dual);


you just type less.


views are not "refreshed"

Yogesh, August 26, 2004 - 11:46 am UTC

So can we say that, when I select from view, oracle is selecting it from base table, but restricting to view predicates?

Tom Kyte
August 26, 2004 - 1:58 pm UTC

you can say that when you select from the view, the view text is actually inserted right into your query as if you typed it in.


select * from v;

is really:

select * from (TEXT OF THE QUERY THAT IS "V");



Regarding performance using views

Murali, December 02, 2004 - 7:07 am UTC

Hello Tom,
I have a view which does the following:
create or replace view a as select hd_id,
DECODE(arraydevice.SD_TOTAL, NULL, DECODE(v_sts_hostdevice.HD_TOTAL,NULL,0,v_sts_hostdevice.HD_TOTAL), to_number(NULL)) INTDISK_TOTAL,
'Host' ACCESSIBLE_BY,
(case when ((SELECT count(FS_ID) from S2 where S2.HD_ID=HD_ID) > 0 or (SELECT count(FS_ID) from V_STS_FS_SD S2 where S2.HD_ID=HD_ID) > 0) then 'Yes'
else 'No' end) USED_BY_FS
from table a,table b.....

Here S2 is a view having about 2 million records.
Please suggest me a better sql which I can continue with because this is taking a hell lot of time.


Tom Kyte
December 02, 2004 - 7:48 am UTC

whats up with the colorful language. it is so descriptive too -- tells me exactly the facts one needs.


......

that is about all anyone could say possibly about your query as it stands.

Views.

Alvin, June 22, 2005 - 4:06 am UTC

It's said that views data are not stored on tablespaces. Just the views definition (sql?) is stored in the data dict.

1. Does that mean upon startup the db will create all the views ?
2. If #1 is so. Then views will affect startup time ?

Tom Kyte
June 22, 2005 - 6:54 am UTC

1) no, it means when you run a query against a view, the view text is substituted into your query.

2) no

performance when loading data

Shaym, June 22, 2005 - 11:06 am UTC

Tom when
I am using .Net to extract ,load and save the data from business layer into the oracle 9i database.some how my
loading the data is taking 12 hours of time can you help me where exactly is the problem.

is it problem with the oracle database how we have
created or problem with the .net code which was coded.

is ther any way to eaxtract load the data from the Business layer into oracle table.we conot use exp and imp becoz database is different and the business layer is responsible for us to give the data.

waitng ur repsonse

Tom Kyte
June 22, 2005 - 4:46 pm UTC

use the 10046 level 12 trace and see what you see in a tkprof.

if you have instrumented your code (like we did the database) it would be helpful to see where your bottleneck is as well


probably, you are doing slow by slow procedural processing where a big juicy bulk operation is called for.

ur repsonse 4 shaym

ur, June 22, 2005 - 12:17 pm UTC

ur prblm iz dat uv not dzkribed ne dtl (wht iz "business layer" mean?) such dat ne1 kn mak a guess as 2 wer ur pblm might B xcpt sumwer in ur .net code.

HTH
ur

query with view in from clause is slow

shyam, October 12, 2005 - 9:24 am UTC

tom i have a query with view in from clause becoz of which my query performance is slow pl let me know how to increase the performance..

My query like select <tbl names> from (dimension tables t1 t2 t3)(view)
where (cond)

pl let me know if there are any hints to improve performance



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.