Why are version 6 views so sluggish? How to improve?

• Quoting Keith W. Johnson to All dated 06-07-94 ***
  > I support an old version 6 database on a small VAX/VMS machine. I just
  > removed some common logic from several SQL*plus batch jobs and created
  > a view that performed the same logic. The original SQL was then modified to
  > include the view in a join. So... same logic, just implemented differently.
  

Quite -- one wonders -why- you did such a thing, but we'll leave that question aside for now...

> No indexes were changed. Only about 50 rows result from the logic in
> the view. That view is joined to another rather large table. I was hoping
> the query engine would evaluate the view and process it once and the >
results simply stored in a cache.

Well, yes, that probably is happening, but it's not the whole story. Basically, the engine resolves the view first, and then performs the join on the result. There aren't any indexes on the resolved view... Any way you perform a view, there will be a big performance hit. They aren't designed to save TIME.

> My run times increased by at least an order of magnitude. Why?

Not only is there overhead involved in resolving a view, but you are compounding the operations that the database must perform, and disabling the optimizer. Run EXPLAIN PLAN and take a look -- it's an ugly scene, especially if you do something as ill-advised as actually joining to a view. My guess is that the database first has to completely build a new temporary table using the view criteria, and then join that non-indexed temporary table ...

> If this database were upgraded to version 7 would it work properly?

It works PROPERLY now. It's just not a good idea to do things that way. The advantage of a version 7 database is that you have other options besides views to accomplish most of the same things. In general, the version 7 optimizer works better than the version 6 optimizer, but it isn't there to save you from yourself.

> Are there ways to tweak the version 6 database so the view was not such
> a pig?

DO NOT USE A VIEW. It is not at all appropriate, given the circumstances. Received on Thu Jun 09 1994 - 02:18:43 CEST