Re: SQL select via logicals

        
Date:   11/26/97  5:52 PM

RE:     Re: SQL select via logicals

Walden

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

Vernon

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