Re: Get first record

In article <hHWK7.23$Mi4.739_at_nsw.nnrp.telstra.net>, sweidanz_at_yahoo.com says...
>
>Folks,
>I have got a table with 100,000 records.
>batch_no, batch_date, transactional columns...(like, acct_no, amount,
>etc....)
>
>batch_no and batch_date are unique every day.
>
>Lets say we know the batch_no and we need to get the batch_date for that
>batch. I would write a query like:
>
>SELECT DISTINCT batch_date
>FROM table
>WHERE batch_no= 1234
>or
>SELECT MAX(batch_date)
>FROM table
>WHERE batch_no= 1234
>
>and i am expecting to get one record out of this query.
>
>But this query is going to do a FULL SCAN for the table (assuming no index
>is available) and then do a SORT to get the distinct value.
>
>Is there a more efficient way to avoid the SORT operation as i only want
>the first record and the batch_date will always be the same for that
>batch_no.
>
>Another way i thought to write the query is:
>SELECT batch_date
>FROM table
>WHERE batch_no= 1234 and rownum<2
>

if batch_date is the same for all batch_no - this is your query.

It'll full scan UNTIL it finds the first record with batch_no = 1234 and then stop. It you get lucky and this data is on the first block -- very fast. If you are unlucky -- and the first block with this data is the last block, it'll be as long as it takes to full scan the table...

>But i am not sure what Oracle does here? Is it a FULL SCAN and then
>immediately do another scan and stop after the first record or it keeps
>going?
>In the execution plan there is something(which i haven't seen before)
>COUNT (STOPKEY).
>Is this query more efficient?
>
>thanks alot,
>ZS

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp