3

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: Query plan

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" ;
Yervand Aghababyan
  • 1,100
  • 1
  • 18
  • 39
  • What does the query plan look like? – tobiasbayer Nov 08 '11 at 13:37
  • is the image i added the query plan? :) (i'm new to oracle terminology, i'm a mysql guy :D ) – Yervand Aghababyan Nov 08 '11 at 13:42
  • So you're taking a table with millions of rows, ordering them, and then looking at the first 15 -- which is all you're interested in. You should look at Oracle's analytical functions. See, for example, http://stackoverflow.com/questions/5636507/selecting-top-n-rows-without-rownum – Jim Hudson Nov 08 '11 at 14:06
  • That explain plan appears to be for a table without any indexes. Or at least, for a table without an index on CREATED. Also, what version of the database are you using? Oracle's optimizer gete better with each release. – APC Nov 08 '11 at 14:23
  • there are definitely indexes on the created column (i updated the question with their code). one ASC the other one DESC. but i agree, they are not being used. I tried providing an index hint in the query but it didn't work. – Yervand Aghababyan Nov 08 '11 at 14:46

2 Answers2

9

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • +1, although the index `(created,deleted)` will only be used if CREATED is not nullable (unlikely from the OP question) or if the query is modified to include the predicate `where created is not null`. – Vincent Malgrat Nov 08 '11 at 14:28
  • thanks a lot. based on your response i figured the trick was to add the id column to the index. so it's now (created, id). I didn't add the deleted column there cause i want it to be used for other queries as well and the performance benefit of having the deleted column in it was insignificant. – Yervand Aghababyan Nov 08 '11 at 15:31
  • 1
    @VincentMalgrat - fair point. I'm afraid I assumed that all records would have CREATED populated (otherwise what is the point of such a column?). – APC Nov 08 '11 at 15:58
-1

try to look here

http://www.dba-oracle.com/oracle_tips_null_idx.htm.

Also (though I doubt it will help here), there are native queries in hibernate in case the generated queries are too slow.

John Donn
  • 1,718
  • 2
  • 19
  • 45