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

In article <3359724e.5276966_at_news.netvigator.com>,

   budlo_at_netvigator.com (Bud Lo) wrote:

>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?

The condition "and rownum = 1" will fetch exactly one row. You can verify this by running an explain plan for the statement. The plan will include a count operation with the stopkey option.

This is what "Oracle7 Server Tuning", page A – 20, says about stopkey: "STOPKEY : A count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause."

You can verify the performance easily by running the queries in SQL*Plus with the setting "SET TIMING ON". For a table of about 26.000 rows, a full table scan count requires about 1.5 seconds, while adding the condition "and rownum <10" requires about 0.02 seconds. (on a test-system)

Ofcourse, this timing method is not perfect, since it also includes some system overhead, but the timing shows a difference of 98.67%, so I think it's save to conclude it's faster.

As a last remark, I want to add that using an order by clause might degrade performance, since ordering is performed after assigning a rownum.

Regards, Stefan.

Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.