0

I am theoretically working on Oracle11g DB and have a particular table with only 2 columns. One of the columns is an person identificator and the other columns is a city identificator, both of them form the primary key.
However, there are way more different values for the personID than for the cityID.

Is it more efficient to make the primary key (personID, cityID) or (cityID, personID)?

Praveen
  • 8,945
  • 4
  • 31
  • 49
vandermies
  • 183
  • 3
  • 14
  • You can always benchmark it. Here is a thread you might be interested in though: [Column order in Index](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671539468597). – sstan Dec 09 '15 at 13:12

1 Answers1

2

There is no concept of "efficiency" in setting primary keys. I mean, you could dive into the structure of the index structure to determine whether duplicates of values affect the index size, but this would be a very, very, very marginal optimization.

In general, the question is which indexes work best for queries. So, if you are more commonly using one of the fields for where, order by, and group by clauses, then that would dictate which goes first. You might find that you want a non-clustered index on both columns.

As a note: clustered indexes are not particularly efficient for inserts. They require physically locating new records near old records, so inserts go in the middle of previous pages (causing page splits and partially filled pages). This is additional overhead on inserts, that secondary indexes do not require. (Secondary indexes do require updating as well, but the original data does not need to be moved around.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • If city has less unique values than person then if you put city first and use compress option then size of your index will be less. Sometimes significantly less. But it could affect negativly performance for queries selecting given person from all cities. – Rusty Dec 09 '15 at 16:47
  • @Rusty . . . I totally agree. I hope the answer conveyed the idea: performance of indexes is based on the queries being run, not on arcane properties of the index. – Gordon Linoff Dec 09 '15 at 23:23
  • @Gordon, yes. I was no trying to say anything against that. Totally support your answer. Just add another point of view - space, not just performance. – Rusty Dec 10 '15 at 10:19