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!)

?

1 2 3 4 5 6 7

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.

?

1	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.

Oracle: scan types

• Full Table Scan - Highest cost. This happens, if:
• No index exists.
• There is an index, but
• Without columns that the query is using, or
• Aggregate function is used, AND index allows nulls, or
• Multi-column index, and you're not using the first column in it.
• Resultset expected to be so big, that it is cheaper to read through the table than use the index. (Fewer large IO calls are cheaper than lot of tiny ones.)
• The table is too small. (Smaller than DB_FILE_MULTIBLOCK_READ_COUNT)
• Table parallelism too high. (Small partitions are easier to scan - see previous item)
• Table Access by rowid - Getting rowids through an index.
• An index returns with a set of rowid-s.
• There are columns specified that are not in the index.
• Table Access Sample - When using block sampling.
• Table Access Inmemory - Since 12cR1, on tables using inmemory parameter.
• Index Unique Scan - unique means, at most one rowid exists for a given index entry, so the search can stop after the first match.
• Unique Index exists and can be used, and
• Equality operator being in place.
• Index Range Scan (Descending) - ordered scan of values, even for ranges.
• Index leading columns with <,>,= operators, and
• AND combination of those (at least for leading columns).
• Can be forced by using BETWEEN.
• Descending: Order By has Desc parameter in it.
• Index Full Scan - Uses index order for Order By.
• Referencing an indexed column,
• Order By present, in a notnull column,
• All columns in Order By are indexed,
• Order of columns in Order By match the order of the leading index columns.
• Index Fast Full Scan - Full index scan, not accessing the table.
•  All columns required are in the index(es).
• Index Skip Scan
• Leading column in a composite index is not used,
• Leading column has only a few distinct values,
• Non-leading, referenced column has many distinct values.
• Hash Join (Index Join Scan)
• Hash join of multiple indexes returns all the required columns (without table access),
• Table access is more expensive than an index join.
• Table Access Cluster (Cluster Scan)
• Using an indexed cluster.
• Needs a simple Cluster used by all the tables.
• Table Access Hash (Hash Scan)
• In a hash cluster, rows are grouped into the same block by their hash value.
• Needs a Hash Cluster used by all the tables.

Leading column is the first column in a composite index.

CREATE INDEX ix_thisisanindex ON table42(leadingcolumn, notleading, stillnotleading);