Plsql - get front 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 of the above give a similar result, but I need to know which one is more effective!

Answer 1

This is the Tom Kite mantra:

You should do this in one SQL statement, if at all possible.
If you cannot do this in a single SQL statement, then do it in PL / SQL.
If you cannot do this in PL / SQL, try the Java Stored Procedure.
If you cannot do this in Java, do it in an external C routine.
If you cannot do this in an external C routine, you may need to seriously consider why you need it ...

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

Answer 2

- .

, .

- , DENSE_RANK:

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

---

, ( , ):

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;

---

:

• , :
  2.241 s

• ROWNUM:
  1.483

• DENSE_RANK:
  1.168