SQL plsql - 获取第一行 - 哪个更好？

plsql - get first row - which one is better?

提问by user1262115

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!

以上两者都产生了相似的结果，但我需要知道哪个更有效！

回答by A.B.Cade

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…

如果可能，您应该在单个 SQL 语句中执行此操作。
如果您不能在单个 SQL 语句中完成，则在 PL/SQL 中完成。
如果您不能在 PL/SQL 中做到这一点，请尝试使用 Java 存储过程。
如果您不能在 Java 中完成，请在 C 外部过程中完成。
如果你不能在 C 外部例程中做到这一点，你可能需要认真思考为什么你需要这样做......

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

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

回答by Peter Lang

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:

没有任何索引，看起来使用解析函数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

1. 使用游标，获取第一行：
   2.241 秒

2. 使用查询ROWNUM：
   1.483 秒

3. 使用DENSE_RANK：
   1.168 秒