oracle order by runs terribly slow

Question

i'm doing a query like this one and it takes 6 seconds to complete :

select * 
from ( select aaa."ID" 
      from "aaa"  
      where aaa."DELETED" is null 
      order by aaa."CREATED" desc ) 
where rownum <= 15;

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?

I can't much change the query cause it's generated by hibernate

Edit: even without aaa."DELETED" is null the query is running very slow.

Edit 2:

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.

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" ;

Answer 1

You need to understand how Oracle accesses records in the database. A index read is one action, a table read is another action. So to retrieve one indexed record from a table is a minimum of two reads.

Your query uses three pieces of information:

• the DELETED column (restriction criterion)
• the CREATED column (sort criterion)
• the ID (result set)

Oracle doesn't index NULL values, so a single column index on DELETED won't help you. That's a full table scan, and no better than no index at all.

An index on CREATED on its own is better because the access path will become:

INDEX FULL SCAN DESCENDING

That is, it starts are the most recent dates in trhe index and works backwards. However, the query will still need to read the table to find the ID and the DELETED values. That may be a lot of table reads, depending on how often DELETED is null.

Now, a compound index on (CREATED, DELETED) in that order should be more useful, because Oracle will now index the NULLs in the DELETED column. Oracle can use the index to ensure it only looks up table records to get the ID values. That will be fifteen table reads.

Finally, you could build a compound index on (CREATED, DELETED, ID) and service the entire query from the index. That is the speediest option yet.

Then you just have to decide whether performance benefits justify the overhead of maintaining the index. For what it's worth, the cost of maintaining a compound index adds a small fraction to the cost of maintining a single column index.

---

Incidentally, horrible queries like this are one reason why it is a bad idea to use logical deletion. Physically delete your records, and use journally tables to retrieve the historical state of your table when you need it.