Re: Views and Indexes -- MIDRANGE-L

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