Select Statement

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 Received on Fri Apr 21 2006 - 08:32:47 CDT