Re[2]: SQL select via logicals

[Srinivas.Rao@xxxxxxxxxx (by way of David Gibbs <david@xxxxxxxxxxxx>)]

        Couple of things with SQL either interactive or embedded,

                SQL optimizer never uses existing access paths if they
                have select / omit on them.

                If the view(LF) already has select/omit and if you do order-by
                the optimizer always builds new access path unless you have
one
                or more views(LF) with the access path on the condition
columns,
                in your case an access path on 'type' field.


                srinivas.rao@ipaper.com





______________________________ Reply Separator
_________________________________
Subject: Re: SQL select via logicals
Author:  midrange.com!midrange-l-owner@mcs.com at INTERNET
Date:    12/3/97 1:52 PM


>Thanks for the test. I hope I didn't come across too testy (so to speak)
>when I threw down that gauntlet. Must be this cold.

Not at all!  Like I said, I was happy to do it... this sort of thing is always
an education...

[The question is this:  If I issue and SQL request against a logical]
[and the logical is built with select/omit criteria, will the records]
[returned be filtered by the s/o criteria?]

I ran some tests:
>>
>>OK.  Here's the set-up:
>>Physical file MASTER keyed by PEXC, PNUM, PSUF.  Record count 13,390.
>>Logical file BILLPRT keyed by ZIP, BTEXC, BTNUM, BTSUF, PEXC, PNUM, PSUF
>>                   SELECT TYPE NE 'C'
>>
>>If I run this statement: select count(*) from master
>>I get this answer: 13,390
>>
>>If I run this statement: select count(*) from billprt
>>I get this answer: 13,322
>>
>>If I run this statement: select count(*) from billprt where type = 'C'
>>I get this answer: 0
>>
>>In all these interactive statements, I get the following message in debug:
>>Arrival sequence access was used for file xxxxxx
>>
>>I ran this one: select * from billprt order by pexc,pnum,psuf
>>Got this count: 13,322 (I scrolled to the end of the list)
>>Got this message:    Access path built for file BILLPRT.
>>
>>On my machine at V4R1, SQL used the logical I specified, and it obeyed
>>the select criteria in use.  If there's another set of circumstances you'd
>>like me to test with, I'd be very happy to do it: I would have been
>>shocked to the core if I did a SELECT from the logical and got all the
>>records, under any circumstances!
>
>Looks like it had to create a new access path, since the ORDER BY you used
>is deep into the logical's key.
>
>What results do you get if you specify the WHERE clause with the physical,
>setting the same criteria as the S/O above?
>
>select * from master where type <> 'C' order by pexc,pnum,psuf

Just what'd you'd expect: 13,322 records (same as the S/O logical)

The optimiser timed out, and built an access path even though the
physical file if already keyed the same as the ORDER BY.

Buck Calabro
Commsoft

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---