Re: How can you tune this SQL ?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/20
Message-ID: <33ab0b8d.16175116@www.sigov.si>#1/1

On 19 Jun 1997 20:54:19 -0700, 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.

SELECT key1, key2, d1, d2, ....
  FROM table
  WHERE key1 > :k1
  OR (key1 = :k1 AND key2 >= :k2);

SELECT key1, key2, d1, d2, ....
  FROM table
  WHERE key1 > :k1
UNION ALL
SELECT key1, key2, d1, d2, ....
  FROM table
  WHERE key1 = :k1
  AND key2 >= :k2;

In both queries all the entries that will be scaned in the index will match the condition (no overhead scaning the index in the range where only key1 is satisfying the condition while key2 is not) and rows will be returned sorted in ascending order.

Regards,

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

Received on Fri Jun 20 1997 - 00:00:00 CDT