2014-10-22

Oracle: JIRA: index for the pagination query

I found a select that executes with a cost over 16k, and I wondered, how could I speed this up.
(Sometimes it ran for 10.5 seconds!)

SELECT SQ_.ID FROM
  ( 
    SELECT ID FROM JIRAISSUE
    WHERE PROJECT IN(:1, :2)
    ORDER BY UPDATED DESC
  ) SQ_
WHERE ROWNUM <= 10;

It seems, only the IN-s contents are changing over the course, so we could fit an index for that. Lets see, what we have:

  • ORDER BY UPDATED DESC - this will be the leading column in descending order,
  • PROJECT - filter, this should be in,
  • ID - as we projected this column, if we put this in the index, it can be an index-only query.

CREATE INDEX IX_JIRAISSUE_UPDATED_PROJ_ID ON JIRAISSUE (UPDATED DESC, PROJECT ASC, ID ASC);

Results in an index full scan and a count-with-stopkey. Yaay, the cost is 3 (three), in a table with ~2 million rows.

No comments :

Post a Comment