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
+---


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].