Re: How can you tune this SQL ?

Alberto, Rivera wrote:
>
> In article <33AA5B71.59CC_at_iol.ie>, Chrysalis says...
> >
> >Alberto, Rivera wrote:
> >>
> >> I have a problem trying to improve the performance of a program.
> >> Can you find a better SQL ?
> >
> >snip
>
> 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:
>
> 1) There is no problem with the ORDER BY clause, because Oracle is
> using the same index to evaluate and get the rows sorted.
>
> 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.
> (snip)

Alberto,
OK.
1) Remember that the index scan is a serial scan of index entries, so we are agreed that *data* pages are not accessed unnecessarily. 2) The serial scan of the index is fast, so that if you are actually returning 1000 data rows (as in your example), the overhead of scanning 1000 index entries before reaching the first may be acceptable. 3) However, if you are only going to return a very small number of rows (like one) per execution, then this overhead may well be too high. In this case, you might be justified in a table redesign in which you concatenate your two key columns into a single column and use that as the primary key. The problem then disappears. 4) Don't waste time trying to re-write the statement with ORs or UNIONs: you already have the best predicate you can use with the existing table definition
(and the existing optimiser).
5) The Index Sequential Processor (ISP) was an IBM term for the traditional method of accessing indexed files in pre-relational days. It was frequently used by COBOL programmers. I guess I'm showing my age :-)
6) If you want to continue this thread, I suggest you mail me with a usable mailid.

Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards" Received on Wed Jun 25 1997 - 00:00:00 CDT