title: Oracle TOP optimizing
body: <p>I'm having trouble optimizing a statement. The corrisponding table (INTERVAL_TBL) contains about 11.000.000 rows which causes that statement to take round about 8 seconds on my test system. Even on a dedicated Oracle Server (24gb RAM, 17gb DB size) it takes about 4 - 5 seconds.</p>
  
  <pre><code>SELECT 
  ID, STATUS_ID, INTERVAL_ID, BEGIN_TS, END_TS, PT, DISPLAYTEXT, RSC
  FROM 
  (
  SELECT 
      INTERVAL_TBL.ID, INTERVAL_TBL.INTERVAL_ID, INTERVAL_TBL.STATUS_ID, INTERVAL_TBL.BEGIN_TS, INTERVAL_TBL.END_TS, INTERVAL_TBL.PT, ST_TBL.DISPLAYTEXT, ST_TBL.RSC,
      RANK() OVER (ORDER BY BEGIN_TS DESC) MY_RANK
  FROM INTERVAL_TBL
  INNER JOIN ST_TBL ON ST_TBL.STATUS_ID = INTERVAL_TBL.STATUS_ID
  WHERE ID = '&lt;id&gt;'
  )
  WHERE MY_RANK &lt;= 10
  </code></pre>
  
  <p>First of all I'd like to know if there is a way to optimize the Statement (Select most recent rows ordered by BEGIN).</p>
  
  <p>Second I'd like to know if someone can make suggestions for an Index based on the statement.</p>
  
  <p>EDIT:</p>
  
  <p>Explain Plan:</p>
  
  <hr>
  
  <pre><code>PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  
  | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
  |   0 | SELECT STATEMENT   |                  |   525K|    79M| 58469   (1)| 00:00:03 |
  |*  1 |  HASH JOIN         |                  |   525K|    79M| 58469   (1)| 00:00:03 |
  |   2 |   TABLE ACCESS FULL| ST_TBL           |    46 |  2438 |     3   (0)| 00:00:01 |
  |*  3 |   TABLE ACCESS FULL| INTERVAL_TBL     |   525K|    52M| 58464   (1)| 00:00:03 |
  
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("ST_TBL"."STATUS_ID"="INTERVAL_TBL"."STATUS_ID")
     3 - filter("INTERVAL_TBL"."ID"='aef830a6-275b-4713-90da-9135f3f91a32'")
  </code></pre>
  
  <ul>
  <li>Rows in INTERVAL_TBL: 10.673.122</li>
  <li>Rows in ST_TBL: 46</li>
  <li>Rows in joined subset: 10.673.122</li>
  <li>Rows in joined subset with filter on ID: 530.073</li>
  </ul>
  
  <hr>
  
  <p>Ideally it would get down to about some milliseconds. Thats what that statement takes in MS SQL Server with 10.000.000 rows.</p>