Re: How can you tune this SQL ?

From: <Alberto>
Date: 1997/06/24
Message-ID: <5opl8m$9f0@drn.zippo.com>

In article <33AA5B71.59CC_at_iol.ie>, Chrysalis says...
>
>Alberto, Rivera wrote:
>>
>> I have a problem trying to improve the performance of a program.
>> The program retrieves the data using a cursor like this:
>>
>> SELECT key1, key2, d1, d2, ....
>> FROM table
>> WHERE key1 || key2 >= :k1 || :k2
>> ORDER BY key1, key2
>>
>> where key1, key2, k1, k2 are char with fixed size (CHAR datatype)
>> and primary key is (key1, key2)
>>
>> The idea is that the cursor will retrieve all the rows starting from
>> one point from the key to the end.
>> I found that it could be optimized by doing the following:
>>
>> SELECT key1, key2, d1, d2, ....
>> FROM table
>> WHERE key1 >= :k1
>> AND NOT ( key1 = :k1 AND key2 < :k2 )
>> ORDER BY key1, key2
>>
>> But will only use the index for the first field, if this field is not
>> very selective there would not be much difference.
>>
>> Is any way to use the index to get the first row that matches
>> key1 = :k1 and key2 = :k2 and then retrieve the following rows ?
>> Note: select ... where key1 >= :k1 and key2 >= :k2 will not work !
>>
>> Thanks in advance for your help.
>>
>> Alberto Rivera.
>>
>> --------------------------
>> Example:
>>
>> Suppose we have a table with Class and No. as primary key, and we want
>> to get the rows starting from Class=B and No.=3
>>
>> Key1 Key2 Rows scanned to check if match the condition
>> Class No. SQL1 SQL2 SQL?
>> ------ ------- ----------------------------------------------
>> A 1 x
>> A 2 x
>> A 3 x
>> A 4 x
>> B 1 x x
>> B 2 x x
>> B 3 <---- x x x
>> B 4 x x x
>> B 5 x x x
>>
>> With the first SQL, Oracle will check all the table, with the second
>> SQL Oracle will check only the rows that matches the first field.
>> Can you find a better SQL ?
>
>Alberto,
>This looks like a classic OLTP type of query which would formerly
>have been implemented using an Index Sequential Processor (ISP).
>I presume that the most important goal is to minimise the time taken
>to return the first row(s).
>
>You are right that using concatenated fields precludes the use of an
>index, but your analysis of the second method is not quite correct.
>The first part of the key (key1) is used to locate the start point of
>the *index* scan. The AND NOT predicate (referencing key1 *and* key2) is
>then evaluated during the index scan. Only fully-qualified rows are
>actually accessed (by rowid) from the table.
>
>Therefore, your (second) construction of the WHERE clause is fine. The
>main concern is to avoid the use of ORDER BY, which generally requires
>the whole result set to be evaluated before the first row is returned.
>
>The most efficient way to do this in Oracle is to force the use of the
>index by using a hint, thus:
>
>SELECT /*+ index_asc (table_name index_name) */
> ...
>FROM table
>WHERE key1 >= :k1
>AND NOT (key1 = :k1 and key2 < :k2)
> -- ORDER BY key1, key2 -- not needed
>
>Note that a similar construction and the use of the INDEX_DESC hint
>also provides the facility for backward searching from a given row.
>
>Hope this helps.
>
>Chrysalis.

Chrysalis,
I had analysed the second SQL using Tkprof.

    SELECT key1, key2, d1, d2, ....
    FROM table
    WHERE key1 >= :k1
    AND NOT ( key1 = :k1 AND key2 < :k2 )     ORDER BY key1, key2

The explain plan looks like this:

Rows Execution Plan

-------  ---------------------------------------------------
      0   SELECT STATEMENT
   1000     TABLE ACCESS   (BY ROWID) OF 'table'
   2000        INDEX   (RANGE SCAN) OF 'index_primary_key' (UNIQUE)

Conclusions:

There is no problem with the ORDER BY clause, because Oracle is using the same index to evaluate and get the rows sorted.
You are right that only fully-qualified rows are actually accessed from the table.
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.
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.

Thanks for your help.

Alberto Rivera.

PD: What is an Index Sequential Processor (ISP) ?

Example:
Suppose we have a table with Class and No. as primary key, and we want to get the rows starting from Class=B and No.=1001

Class No.
------ ------

A           1
A           2       
A         ...
A        2000
B           1
B           2       
B         ...
B        2000

Using the second method the explain plan will be like this:

Rows Execution Plan

-------  ---------------------------------------------------
      0   SELECT STATEMENT
   1000     TABLE ACCESS   (BY ROWID) OF 'table'
   2000        INDEX   (RANGE SCAN) OF 'index_primary_key' (UNIQUE)

2000 rows evaluated during the index scan (those rows belonging to Class=B) but only 1000 rows retrieved from the table (those rows which No. >= 1001). Before accessing the first row (Class=B, No.=1001) Oracle must read from the index and discard 1000 rows. Received on Tue Jun 24 1997 - 00:00:00 CDT