Re: Views - performance

Thanks Dave

I thought maybe there was a general do or don't do rule of thumb. It is V 9i on a Unix system. I don't even know any more than that about the system.

There are many views that I am encountering and was considering re-writing some of them. I have managed to combine a few, but not sure if it is worth it. We are however, dealing with several hundred thousand records to around 1 to 1.3 million at a clip sometimes, so it is going to get more attention.

Thanks for the reply.

Jeff

On 6 Sep 2006 09:00:15 -0700, "fitzjarrell_at_cox.net" <fitzjarrell_at_cox.net> wrote:

>
>Jeff User wrote:
>> Hi everyone
>> Sorry if this is cross posted, I just found this group.
>>
>> I would like to know if using several views for a complicated query is
>> slower than placing all the code in one large query.
>>
>> I had heard that views were expensive in terms of performance, but I
>> find them used quite a bit.
>>
>>
>> Jeff
>
>The answer depends upon the code you're writing and how you choose to
>write it. Subquery factoring could help with this, creating views
>might be faster, using 'in-line' views might provide better
>performance, but you, and we, won't know until you actually profile
>your query and post the query plan:
>
>explain plan
>set statement_id = '...' for
><your in-doubt query here>;
>
>select *
>from table(dbms_xplan.display);
>
>We can, and will, make suggestions to help you but asking for 'blanket
>statements' isn't the best way to answer your question. There are too
>many variables at work, such as which release of Oracle (all four
>numbers), which operating system, how much RAM, the suspect query and
>the query plan. Given that information we can provide some insight;
>absent that your guess is as good as ours, and it would be merely
>guesswork at this point. You certainly wouldn't ask a mechanic to fix
>your automobile without giving him/her access to it.
>
>Provide more information on what you're doing and how you're trying to
>do it, along with the requested Oracle and operating system details.
>Then we can possibly provide a better answer to your question.
>
>
>David Fitzjarrell
Received on Wed Sep 06 2006 - 11:29:54 CDT