Re: When to use Updateable View ?

On Sat, 30 Sep 2006 17:12:45 -0700, krislioe wrote:

> Hi ,
>
> I have questions about updateable View :
>
> 1) when is it appropriate to use it (instead of the underlying
> table/tables) ?

A view should represent a business access to the data. For example the view might provide the complete purchase order information, rather than the 'purchase order header' and 'line items' normalized tables.

If the view does not relate back to the business, then it *may* not be a legitimate view. Standard rules apply - just because you can, doesn't mean you should [create a view].

(Then again, a view is nothing other than a stored & named select statement. One possible business purpose is to eliminate the time to recreate some complex select statement by storing it.)

The view should be updated when it is /in context/ to the update. In other words, if you selected the information based on the view, it makes sense to update the information based on the view rather than duplicating the select to get at the underlying tables.

>
> 2) what is the performance impact ?
>

Anywhere from /none/ to /significant/, depending on the complexity of the view, whether you are using 'Instead of' triggers, and whether the alternate involves simple accesses or table scans.

If you have specific views in mind, I recommend you get Tom Kyte's test harness (in any of his books - see http://www.apress.com) and get some preformance measures.

Remember, when comparing performance, to include the 'select the underlying table(s) into context' and possibly 'position to the correct row' to ensure the comparison is on the entire operation and not just the individual update statement.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.