Re: Using views inside a View

From: GreyBeard <Fuzzy.GreyBeard_at_gmail.com>
Date: Mon, 21 Feb 2005 21:03:41 GMT
Message-Id: <pan.2005.02.21.22.04.09.859902@gmail.com>

On Mon, 21 Feb 2005 21:23:23 +0100, Frank van Bortel wrote:

>> Please correct my interpretation of your statement:
>>
>> "Selecting from inappropriate views that do unnecessary work [relative
>> to what is needed by the select] can lead to bad execution plans.
>>
>> Therefore selecting from views is bad."
>>

> 
> No - I don't go that far; I'd like to change that to:
> therefore selecting from nested views should only be done
> with caution by knowledgeable developers.

>>
>> In some circumstances (such as publishing views to ad-hoc users) I can
>> concur that data sources can be used inappropriately.
>
> Meet Mr. Murphy! Mr. Murphy is an optimist.

I rate this on the same level as indexing - my feeling is that the continued existance of every [performance related] index should be rejustified every 6 months.

Back to this one - would it be fair to provide the following ROT to the OP's question:

"The technical issues that caused performance concerns related to nested views in [very old] versions of Oracle are, for the largest part, no longer relevant.

However, inappropriate use of views can have side effects such as unacceptable performance and resource issues. These side effects, such as unnecessary aggregation within views, could become significant in ad-hoc environments.

Suitable documentation for views as well as proper analysis, including explain plans, should be considered for all 'permanent' SQL statements."

FGB Received on Mon Feb 21 2005 - 15:03:41 CST