Re: SQL select via logicals

[Vernon Hamberg <hambergv@xxxxxxxxxxxxxx>]

Paul

At 04:52 PM 11/30/1997 -0500, you wrote:
>>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
>
>
>
>This really burns me.  We have some physicals in the millions of records,
>with 60 (yes, Sixty) or more logicals.  And the @#^&&*$% optimizer thinks
>it can do it faster by building its own before it searches all of the
>available keys.
>
>GIVE US AN OPTION!  Let us decide to force the optimizer to evaluate all
>available access paths or not.

Not a problem—the option is there as a parameter on OPNQRYF or STRQMQRY and
as an OPTION in embedded SQL. Also in Interactive SQL (F13?).

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN  55401-2499
(612) 371-1111 x480


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