Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How can you tune this SQL ?

Re: How can you tune this SQL ?

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/06/20
Message-ID: <33AA5B71.59CC@iol.ie>#1/1

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)

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. Received on Fri Jun 20 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US