Re: Views and Indexes

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




----- Original Message -----
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Monday, July 26, 2004 7:28 PM
Subject: RE: Views and Indexes


> Paul,
>
> Is it possible for the compiler to associate an index with a view?
Perhaps
> a F-spec keyword INDEX() to associate a specific access path to an open
> "view".  I don't really know if it's possible, but it seems it could be
> managed.
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: Paul Tuohy [mailto:tuohyp@xxxxxxxxxxxxx]
> Sent: Friday, July 23, 2004 4:36 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Views and Indexes
>
>
> Joel,
>
> "Only SQL defined indexes are really accessible by traditional I/O" -
yeesh,
> I didn't put that very well!
>
> Traditional I/O in RPG usually (but not always) implies you are accessing
by
> key (record address type of K on the F spec). You cannot specify the K for
a
> view (since it does not have a key).
>
> You actually highlighted the "problem" with views - "the "where" and
"order
> by" capability of a view". The problem is you cannot specify an order by
for
> a view. When you can, life will be good :-).
>
> You cannot define an index over a view.
> When you can, life will be gooder :-)
>
> I agree thoroughly about the functionality of views - which makes it even
> more annoying that they are so difficult to use without embedded SQL.
Guess
> I want the best of both worlds :-)
>
> Paul
>
>
>
> ----- Original Message -----
> From: "Joel Cochran" <jrc@xxxxxxxxxx>
> To: <midrange-l@xxxxxxxxxxxx>
> Sent: Friday, July 23, 2004 6:06 PM
> Subject: Re: Views and Indexes
>
>
> >
> > Paul,
> >
> > I stand clarified :-)
> >
> > I didn't say that indexes and views were the same thing: I know what
> > capabilities a view has, but I was under the mistaken impression that
> > because of the "where" and "order by" capability of a view that it
> > represented the index as well.  I looked it up and sure enough a view is
> > a "virtual table" that the underlying DBMS must execute at run time.
> > This means that it has to find its own indexes.  So can you write an
> > index over a View?
> >
> > I still don't think a logical can compete with the functionality of a
> > view, but I'll admit I did under-represent them. "Only SQL
> > defined indexes are really accessible by traditional I/O" - what do you
> mean?
> >
> > Joel
> > http://www.rpgnext.com
> >