Re: Getting the first matching record???

Z. Martinez wrote:
>
> I have a large table (about 4 million rows) that I need to search
> through. Although the query I'm using can result to multiple rows, I
> only need the first matching row. This query will be executed probably
> a hundred thousand times. So I really need to optimize it
>
> What is the most efficient way of getting the first matching record
> PL/SQL? and Pro*C?
>
> Thanks in advance.
>
> Please send your response to zlm101_at_psu.edu

There really is only one way to do this. The only reason it is not obvious is that many people seem to think that the program SQL*Plus is the same as the embedded sub-language SQL. The problem with SQL*Plus is that the only simple control you have over how many rows are returned is to use rownum <[=] n, and this is of no use with an ordered set.
However, in every other program language that can use SQL (including the Pro* interfaces and PL/SQL), the way to achieve what you want is to open a cursor, fetch one row (or two, or five ...) and close the cursor. (*Do not* use select .. into ...: this always does an extra fetch, which hurts if you are only returning one row!)

Hope this helps.

Chrysalis. Received on Wed Apr 09 1997 - 00:00:00 CDT