Re: Select Statement

"eye1h" <eye1h_at_yahoo.com> wrote in message news:1145626367.886473.81090_at_i40g2000cwc.googlegroups.com...
> Good Morning Gentleman,
>
> Requirment:
> Need to check whether there Is a record satisfying given criteria
> (transnum = :Itransnum;) in a 10 million row table.
> (lets assume this records can exist as the first row or last row). All
> I want is to find out if this row exists. But my problem is evne if
> this is the first row Oracle still continues going through 10 million
> records. Am i writing the select correct?
>
> SELECT COUNT(*)
> INTO :Icount
> FROM archive_tbl
> WHERE transnum = :Itransnum;
>
> The above willl go through all the records... which is not optimal. I
> want to get out when a single row is found. The following also DOES NOT
> do the trick....it scannes the entire rows...
>
> SELECT COUNT(*)
> INTO :Icount
> FROM archive_tbl
> WHERE transnum = :Itransnum and rownum=1;
>
> Any better way of writing this? Your help is very much appreciated.
>
> Cheers
> Ivan
>

How about:

SELECT 1
INTO :Icount
FROM archive_tbl
WHERE transnum = :Itransnum and rownum < 2;

-- 
Terry Dykstra