Re: Views on views

On Sat, 14 Sep 2002, arnoud.otte_at_dsslib.com wrote:

> I tend to agree with Daniel. Performance drops rapidly with
> views-on-views

My statement (not unlike Daniel's) to people has been that the performance can get fragil. But to make a blanket statement that there is no reason to code a view on top of another view is just incorrect. Warnings are fine, but there are many times that a well-constructed view becomes something one uses in many different new views as well as straight queries.

> , but being open to new experiences, please share some of the
> learnings you have had on getting performance out of views-on-views.

Learnings? Hm... Its just stored sql. I guess the 2 large pieces learned were the two hints

1. no_merge(view)
2. index(view.table index-name)

The fragility comes when Oracle merges the SQL from some piece of SQL and the view being referenced.

--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.