Re: Views and Indexes

On Fri, 2004-07-23 at 20:02, midrange-l-request@xxxxxxxxxxxx wrote:
> message: 3
> date: Fri, 23 Jul 2004 20:37:22 +0100
> from: "Paul Tuohy" <tuohyp@xxxxxxxxxxxxx>
> subject: Views and Indexes (was Re: MIDRANGE-L Digest, Vol 3, Issue
>       1075)
> 
> Hi Joel,
> 
> I think the clarity needs some clarification :-)
> 
> You are correct that views have columnar abilities (quite a lot of them)
> that are not available in DDS but views and indexes are two very seperate
> things.
> 
> A view does NOT incorporate an index. When using a view (using SQL select)
> it is the ORDER BY CLAUSE and WHERE clauses that are used to determine which
> index is used.
> 
> A logical file is not just an index. It can do sequence, selection,
> projection, union and join - just not as many options as SQL. It's one big
> advantage (as Rob pointed out) is that a logical file can define a combined
> view and index. This is of enormous value for traditional I/O. Only SQL
> defined indexes are really accessible by traditional I/O - which means you
> can't make use of all those cool features that wer defined in views (without
> using embedded SQL).
> 
> Paul Tuohy

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