Re: Views - performance

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:00:15 CDT