Re: How can you tune this SQL ?

From: Alberto Rivera <alberto-rivera_at_usa.net>
Date: 1997/07/07
Message-ID: <33C14605.233A@usa.net>#1/1

Hello Tapio,

Yes, this is an interesting problem.

Tapio Luukkanen wrote:
> ...
> >
> > 4) We must find another SQL which can get directly the first row using
> > the index and then start reading the table in the index sequence.
>
> Would splitting the select in two cases improve it, somewhat like:
>
> SELECT key1, key2, d1, d2, ....
> FROM table
> WHERE (key1 = :k1 AND key2 >= :k2)
> OR key1 > :k1;
> /* ORDER BY not needed because of implied index order... */
>
> or
> SELECT key1, key2, d1, d2, ....
> FROM table
> WHERE key1 = :k1 AND key2 >= :k2
> UNION ALL
> SELECT key1, key2, d1, d2, ....
> FROM table
> WHERE key1 > :k1
> /* ORDER BY possibly not needed with union all ? */
> ...

OK, as I understand, the optimizer will transform in this case the first select statement in the second select/union, in order to split the different cases and use the index for each of them.

If the select has ORDER BY, the optimizer will perform a SORT-MERGE to order the rows retrieved with both select/union, and the first row will not be retrieved until this task is finished. The optimizer does not "understand" that in

     WHERE key1 = :k1 AND key2 >= :k2
     WHERE key1 > :k1

:k1 is the same value.

If the select does not have ORDER BY, how can I be sure that Oracle will first retrieve the rows that matches "key1 = :k1 AND key2 >= :k2" and later the rest of the rows "key1 > :k1" ? Do you know some way to indicate this, preferable without using ORDER BY :-) ?

> ...
> Also, isn't there some hint you can give to the optimizer about
> returning the first rows as soon as possible ?

Yes, FIRST_ROWS. The optimizer will choose (within all the avaible accesses for each statement) the access method that can retrieve the first row fastest. In this case the only access method avaible is using the primary key index, and also full scan table :-( .

Thanks for your response.

Alberto Rivera

PS. Sorry about the mail-id. You can e-mail directly to me at

    alberto-rivera_at_usa.net if you want. Received on Mon Jul 07 1997 - 00:00:00 CDT