Search code examples
sqldatabaseoracle-databaseindexingrdbms

Should I add primary key column as the last column of indexes in Oracle RDBMS?

I'm optimizing my Oracle database for certain queries with indexes and a question just hit me.

If I have a table like this (ID is primary key):

|ID |A
--------
|3  |10
|2  |20
|5  |10
|4  |20
|1  |10

And I add an index for column A, I should get something like this

|A  |ID
--------
|10 |3
|10 |5
|10 |1
|20 |2
|20 |4

Since I only created index for column A, I would expect ID to be unordered for each value of A in that index. And that should be inefficient, since we ideally want ordered ID like

|A  |ID
--------
|10 |1
|10 |3
|10 |5
|20 |2
|20 |4

Should I explicitly specify this by creating index for (A,ID) ? The main benefit of that would be easier joins and sub-filters on the result. I.E. 

WHEN a = 10 AND id > 3

Since this looks quite obvious, and I don't remember any book recommending that, intuition tells me that RDBMSs already do this by default for all indexes, but I would like to know for sure. Could someone with more knowledge on this subject please comment?

Update. The linked question does not concern joins at all and does not answer the question given. Markus Winand comment below answers it. The answer is - it does make sense if you are going for WHERE (for example, if you making pagination without ROWNUM, as described here link), but will not benefit joins unless they are very specific sort-merge joins, which make sense only on very large tables and need some additional tweaks to be possible.

Solution

  • Indexing order by clauses is indeed possible and sometimes a good way to prevent sorting:

    http://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by

    However, for joins are a little bit more complex. Have a look at these:

    It would really help if you'd show us the query you try to optimize and also the execution plan.