• Subject: Re: SQL select via logicals
  • From: John Carr <74711.77@xxxxxxxxxxxxxx>
  • Date: Wed, 26 Nov 1997 22:39:00 -0500

Date:   11/26/97  5:52 PM

RE:     Re: SQL select via logicals


>At 01:18 PM 11/25/97 -0500, you wrote:
>> Am I losing my mind? (Don't answer that!) Anyway, if I am issuing a select
>> over a logical and that logical has select/omit but I do NOT specify that
>> select/omit in my where clause I thought that SQL would not use that select
>> criteria. I just saw an example that led me to believe that this was not the
>> case. That is to say, if I select * from lgl1 and lgl1 has select criteria
>> then the select will only have the records in the result set that match the
>> selection criteria. Am I nuts?

> Vernon wrote;
> Without looking at manuals, I've always assumed that if you specify a
> logical with select/omit criteria, that's all that'll come back. That's why
> I always recommend naming the physical in the SQL statement and let the
> system work out the details. Naming a logical seems to have no effect on
> what access paths actually get used. I think (_from_ reading about the
> optimizer), that the where and group by and order by stuff are used. Maybe
> that's the basis of what you thought, that naming a logical has no
> effect—on the optimizer.
> But, maybe, especially in the case of S/O logicals, the result set returned
> is a different animal than the action of the optimizer. Eh?


Yes and no I believe.   Yes, you will only see what the logical 'SEES'
(subset of fields(projection) and subset of records(Selection
-ie select/omit))

And it is better to do the SQL on the base physical and let the optimizer
find the index.  However the search order for the index (I believe) is 
still chronological.  Meaning it will look at indexes in the order of 
when they were made.  So if you have 20 logicals(with possible index 
candidates) the optimizer may not get to number 19 or 20 to find the
best index to use.  It might give up before then,  thinking
 "Hey, I'm wasteing too much time, I could have built an index by now" . 

In this case (where you know that logical number 20 would be the best
index to use) you can "help" the optimizer by specifing a logical in 
the SQL statement to look at first.

If anyone knows if this has changed recently(V4 or so) please post.

John Carr
Consulting / Software /  Classroom Education+---
| 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-2020 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].