2

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.

Ivan Koshelev
  • 3,830
  • 2
  • 30
  • 50
  • "_since we ideally want ordered ID_" -- and why do we want that? Generally, your indexes should more or less correspond to your query search criteria. If you have a query that says `WHERE A=? AND ID=?` (although I don't see why you'd have such a query), then your index should contain both columns. – mustaccio Aug 07 '14 at 17:54
  • "and why do we want that?" for joins where our table is also filtered by WHERE. As far as I understand, if we are joining our table to some other by ID and also filtering WHERE A=10, it will be faster to join ordered set of IDs (1,3,5) then unordered one (3,5,1). – Ivan Koshelev Aug 07 '14 at 18:02
  • This is absolutely not the same as the linked question. – Markus Winand Aug 08 '14 at 05:53

1 Answers1

2

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.

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • Thank you, Markus. I also really liked the site. AFAIU from the given articles - it's impractical to add PK as the last column of an index to speed up joins, as most joins will be hash-joins, while having indexes with PK as the last column will benefit only sort-merge join. And sort-merge join is only possible if you have two exact indexes for the join predicates on the joined tables (and they are both usable and used by the time the join part of the query is executed). Conclusion - this only makes sense if you have a join on two very large tables (and an Optimizer Hint may be needed). – Ivan Koshelev Aug 08 '14 at 18:45