Re: selecting top performers

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 10 Aug 2005 06:14:29 -0700
Message-ID: <1123679669.591244.234670@f14g2000cwb.googlegroups.com>

I don't see anything wrong with using rownum to pull out the rows you want, whether its the top N, middle N or last N. Its always going to be faster than processing it row by row in a cursor.

> I direct the OP to
> http://www.orafaq.com/faq/Server_Utilities/SQL/faq24.htm

I tried out the rank() method and it used more CPU than the rownum method:-

select object_id from

    ( select object_id from all_objects order by object_id desc)     where rownum <= 10

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.03       0.02          0          3          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2      3.03       2.99          0     157603          0
      10

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      3.06       3.02          0     157606          0
      10

select object_id

    from (select object_id, rank() over (order by object_id desc) therank

    from all_objects)
   where therank <= 10

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.02       0.03          0          3          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2      3.70       3.62          0     157603          0
      10

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      3.72       3.65          0     157606          0
      10

Which suggests the rownum method is better ... Received on Wed Aug 10 2005 - 08:14:29 CDT