Re: Q: To check a record's existence in a table, FAST!

Thanks Chrysalis for your help.

I agree that the cursor solution is the most efficient, Since the existing code is using count(*) to check record existence, which do appear a lot, ideally, i want a solution that don't need cursor, but with efficiency close to using cursor and minimal code changes.
Since adopting the rownum solution requires minimal amount of change to existing code, the testing is minimal. If i use the cursor solution, the cursor management overhead (for programmer) like declare,open,fetch,close have to be catered, and the apparent source code size will increase, too.

For the "select 1 into recFound from dual where exists (subquery)", i need to initialize the recFound to,say, 0 every time before the select statement, so the typical structure of code would looks like:

recFound := 0;
select 1 into recFound from dual where exists (subquery); if recFound = 0 then

   ...
end if;

I prefer the rownum solution because it won't have the trouble mentioned above (just add the "and rownum=1" condition to existing code will do). But i still don't know whether the rownum method

(1) fetch exactly one row, if exists or
(2) it actually fetch all rows and return only the 1st fetched row
(The row order and row content  is immaterial to me)

If it's case (2), it is useless to me and i will adopt the "where exists" solution.

Any answer or better alternative?

Thanks,
Bud

On Sat, 19 Apr 1997 18:51:21 +0000, Chrysalis <cellis_at_iol.ie> wrote:

>1) I don't understand your objection to using a cursor. It is *always*
>faster than a "select ... into ..." if you only do a single fetch.
>2) However, the solution with "select ... [into ...] from dual where
>exists (subquery)" is perfectly efficient, since you don't actually need
>the row: just to know that (at least) one exists.
>3) Rownum is of such restricted use that I generally avoid it, since
>there is nearly always a better solution.
>
>Chrysalis.
>
>Bud Lo wrote:
>>
>> On Sat, 19 Apr 1997 13:05:48 +0000, Chrysalis <cellis_at_iol.ie> wrote:
>>
>> >*Never* select count(*) unless you really want to count all the rows!
>> >Your first solution (select null from dual where xists (subquery) will
>> >do, since an <exists> subquery stops when the first row is found.
>> >
>> >However, I notice you are using select .. into ...
>> >Does this mean you are using PL/SQL or SQL*Forms or what?
>> >
>> > Many questions in this NG are concerned with returning the first row
>> >(or first n rows) and people seem to want a solution which works in
>> >SQL*Plus, but SQL*Plus is not the best vehicle for this, since you have
>> >no direct control over the number of rows fetched (rownum is almost
>> >useless unless you don't care about sequence).
>> > Almost any other interface (e.g. PL/SQL, Forms, etc) allow you to
>> >declare and open a cursor and then fetch exactly the number of rows you
>> >want.
>> >
>>
>> i forgot to mention that i don't want to use cursor to achieve that.
>> As you mention, if the sequence is immaterial, is the rownum
>> solution works as i required?? That is, it stops when the
>> 1st record is found, if any, and returns.
>>
>> Thanks,
>> Bud
Received on Sun Apr 20 1997 - 00:00:00 CDT