title: oracle order by runs terribly slow
body: <p>i'm doing a query like this one and it takes 6 seconds to complete :</p>
  
  <pre><code>select * 
  from ( select aaa."ID" 
        from "aaa"  
        where aaa."DELETED" is null 
        order by aaa."CREATED" desc ) 
  where rownum &lt;= 15;
  </code></pre>
  
  <p>I've got about 1.6 million records in my table and I've tried adding a separate index to deleted column and to the created column, I tried adding an index containing both created and deleted colunms and I've tried to create the same index in different order. Nothing seems to help. What can I do to speed this up?</p>
  
  <p>I can't much change the query cause it's generated by hibernate</p>
  
  <p>Edit:
  even without <code>aaa."DELETED" is null</code> the query is running very slow.</p>
  
  <p>Edit 2:
  <img src="https://i.stack.imgur.com/A1LlP.png" alt="Query plan"></p>
  
  <p>Edit 3:
  adding my index definition. i honestly don't know what most of these numbers mean, i'm using sqldeveloper for creating indexes. Didn't even know there's so much configuration options for each index, i'll now look into the documentation.</p>
  
  <pre><code>CREATE INDEX "aaa"."aaa_CREATED_ASC" ON "aaa"."aaa"
    (
      "CREATED"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
    (
      INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "SYSTEM" ;
  CREATE INDEX "aaa"."aaa_CREATED_DESC" ON "aaa"."aaa"
    (
      "CREATED" DESC
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
    (
      INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "SYSTEM" ;
  </code></pre>