INSERT: Table vs. View

• Quoting Byron Pearce to All dated 11-10-94 ***
  > 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.
  

In our experience, a proper benchmark demonstrates that any operation on a view (select, insert, delete, update) is ALWAYS slower than the same operation on a base table. For simple operations, the difference is often negligible (not many people notice an extra 1/10 second.)

Note the keyword SIMPLE operations. For complex operations, a view will often perform VERY badly. Joins across views and updates using view columns can multiply performance problems, even to the point of unfeasibility. The Oracle 7 database does an extraordinary job of reorganizing operations to minimize the impacts, but there are many situations where the combination of base SQL syntax and view syntax will be combined to create a behemoth query with only poor execution paths.

In other words, the "always use a view" policy endorsed by less experienced and less practical DBA's is a BAD policy, that is guaranteed to negatively affect performance. The degree to which it does so is a function of the complexity of the database operations.

As always, proper tests and proper benchmarks are the only way to determine precisely what the impacts mean to you. Received on Sun Nov 13 1994 - 18:36:30 CET