Re: How can you tune this SQL ?

From: Tapio Luukkanen <vtl_at_hemuli.tte.vtt.fi>
Date: 1997/06/25
Message-ID: <waiuz2onp7.fsf@morko.tte.vtt.fi>#1/1

Hello Alberto,

You have an interesting problem.

> ...
> 2) You are right that only fully-qualified rows are actually accessed
> from the table.
>
> 3) BEFORE returning the first row, Oracle must discard (during the
> index scan) these rows that not matches the AND NOT predicate. As
> the most important goal is to minimise the time taken to return
> the first row(s), this SQL would not be acceptable.
>
> 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 ? */

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

Tapio Luukkanen

PS. I would have replied by email but there was no reasonable return-address in the article headers. Is this a configuration error or are you just trying to avoid spam ? Received on Wed Jun 25 1997 - 00:00:00 CDT