How can you tune this SQL ?

From: <Alberto>
Date: 1997/06/19
Message-ID: <5ocuut$5tm@drn.zippo.com>#1/1

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 ? Received on Thu Jun 19 1997 - 00:00:00 CDT