plsql - get first row - which one is better?

Question

LV_id number;
Cursor CR_test Is
  select t.id
  from table1 t
  where t.foo = p_foo
  order by t.creation_date; 

Open CR_test;
Fetch CR_test
 Into LV_id;
Close CR_test;

or this one :

select x.id
from(select t.id
     from table1 t
     where t.foo=p_foo
     order by t.creation_date) x
where rownum = 1

Both above make similar result but i need known about which one is more efficient!

Answer 1

This is Tom Kyte's mantra:

You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java Stored Procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

http://tkyte.blogspot.com/2006/10/slow-by-slow.html

Answer 2

Easiest way to find out in this case is to test your queries.

Make sure to test this yourself, indexes and data in your table may produce different results with your table.

Without any index, it looks like there is a better approach using analytic function DENSE_RANK:

SELECT MIN(id) KEEP (DENSE_RANK FIRST ORDER BY creation_date)
INTO lv_id
FROM table1
WHERE foo = p_foo;

---

I used the following code to test the time consumed by your queries (execute this block several times, results may vary):

DECLARE
  p_foo  table1.foo%TYPE := 'A';
  lv_id  table1.id%TYPE;
  t      TIMESTAMP := SYSTIMESTAMP;
BEGIN
  FOR i IN 1 .. 100 LOOP
    -- Query here
  END LOOP;
  dbms_output.put_line(SYSTIMESTAMP - t);
END;

---

Results:

1. Using cursor, fetching first row:
   2.241 s

2. Using query with ROWNUM:
   1.483 s

3. Using DENSE_RANK:
   1.168 s