Re: Views and Indexes -- MIDRANGE-L

With you, Joel

Even at V5R3, views do not have an ORDER BY clause. I just realized you did not say that, rather, that you need an ORDER BY in your SELECT over a view, to get sorted results.

There is some confusion, I think, because a common table expression (CTE) can have an ORDER BY since V5R2, and CTEs can be used in a view. So I tried something:

CREATE VIEW VERN/xxview AS

If you run the select from the CTE statement

SELECT distinct SCstDATE, SCstTIME FROM vern/ptf order by scstdate desc

by itself, it'll be in descending order by date. If you run

SELECT * FROM VERN/XXVIEW

it is NOT in that order. Maybe this is a bug.

If you run the whole CTE

with xx as (SELECT distinct SCstDATE, SCstTIME FROM vern/ptf order by scstdate desc) select scstdate, scsttime from xx

by itself, the result is NOT sorted. Again, is this a bug? I might ask IBM.

So, the returned order of a SELECT is unpredictable, and even more so when you add the possibility of SMP and parallel access methods. The SQL manuals say that the only way to guarantee a certain order is to include an ORDER BY, and it appears that temporary results don't count. This goes along with your statement about needing an ORDER BY in your SELECT.

Hmm!

Vern

At 12:06 PM 7/23/2004, you wrote:

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