Re: Views and Indexes

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

And here comes some more mud for you, Paul!

Things are still a little confused here, I think.

First off, AFAIK, SQL standards do not have a combined view/index - in 
fact, they probably separate them on purpose - what purpose I'm not sure 
of, but they seem to. So getting that on the 400 is pretty unlikely, 
although the 400 has had something like that all along.

Although an SQL index is like a keyed logical , it is different - it cannot 
have columns specified, only the keys. A keyed logical can also have the 
column subsetting that make them like views. Now I don't know the internal 
structure of logicals - whether the non-key data is held in the logical or 
if there are pointers to the rows of the physical and the columns within 
those rows.--I suspect the latter

Now, although an SQL view is like a non-keyed logical, it's not exactly the 
same. I believe a non-keyed logical still has an access path, it's just in 
arrival sequence. We don't often think of it that way, but if you look at 
the access path section of DSPFD for one of these, it will say "arrival". 
Well, I just looked at a view, and you are probably right here - a view 
also has an "arrival" access path. Sometime, in another life, I'm going to 
look at the internals in STRSST.

OTOH, a view has, as far as I can tell, absolutely nothing like our idea of 
an access path. It appears only to be a container for a SELECT statement - 
the result set is not preserved anywhere. There is no maintenance of an 
access path, as there is for non-SQL logicals. The SELECT has to be 
executed and reoptimized every time.

For all the good things that views offer, and there are many, I believe we 
must be careful about getting "view-happy", as you might say it. What I 
mean is, views built on views on views. If, because of record selection 
(WHERE clauses and JOINs and GROUP BYs), a nested view needs a temporary 
result set built, the linkage to any indexes is completely severed. Then a 
temporary index may need to be built, and that can take a lot of time. This 
is also applicable to common-table-expressions and 
nested-table-expressions. Just be careful not to get sucked into the 
maelstrom of nested views and their siblings.

JMHO
Vern

At 05:58 PM 7/26/2004, you wrote:
> Hi Eric,
>
> It's as good a solution as any :-)
>
> But I think that a combined view/index would be the ideal solution. And it
> should be possible since it is already there in DDS.
>
> An SQL index is a keyed logical. i.e. there is an access path entry for
> every row.
> An SQL view is a non-keyed logical. i.e. there is no access path.
>
> If you define a DDS logical with Sequence (a key) and select/omit logic,
> then the access path only contains the rows that meet the selection
> criteria. i.e. selection criteria is checked when the row is
> inserted/updated. I want SQL to allow me do this!
> If you specify DYNSLT, then the access path contains all rows and the
> selection criteria is checked when the access path is read (which is what
> SQL Select does using a view and an index).
>
> Clear as mud :-)
>
> Paul