Re: INSERT: Table vs. View

sjohnson_at_math.scarolina.edu (L. Scott Johnson) writes:

>bpearc1_at_abacus.tis.tandy.com (Byron Pearce) writes:
 

>>PLATFORM: DEC/RISC Ultrix 4.4 RDBMS V6.0.37.3.3
 

>>We have a situation at our site that I was hoping someone could
>>shed some light on for me. We have a table (owned by Schema A)
>>and a view (owned by Schema B) which is a view of that table.
>>In our application program, we are doing an insert through the
>>view.
 

>>I was wondering: is there a performance difference between inserting
>>to a view and inserting directly to the table? Oracle said that they
>>could not answer this question.
 

>If it's a straight view, which this is, performance will only be
>degraded by one additional database access, contained completely on
>the server side, per insert statement.
 

>If it is a pure view (contains all columns and no where clause), you'd be
>better off with a synonym. Still one database access, but more likely to be
>only one disk block access as well - and you have a cleaner logic structure.

I agree. Furthermore, there is one more thing to notice. A view can cause performance problems when used in an outer join situation. If you outer join to the view Oracle6 will not be able to use the indexes on the table. We experienced the problem in the Oracle Case tool with 60000 rows in SDD_elements. Oracle7 does not have the problem.

--
Teijo Doornkamp (doornkampt_at_vertis.nl)