CODEWITHSUNDEEP
performanceoracle-databaseoracle10g
Jayan
Jayan

Reputation: 18458

oracle index for string column - does format of data affects quality of index?

We have following type of "Unique ID" column for many tables in the database (Oracle). It is a string with following format

<randomnumber>-<ascendingnumber>-<machinename>

So we have some thing like this

U1234-12345-NBBJD
U1234-12346-NBBJD
U1234-12347-NBBJD
U1234-12348-NBBJD
U1234-12349-NBBJD

The UID value is unique, we have unique index on them. Does the following format is more efficient than above for index scans?

NBBJD-U1234-12345
NBBJD-U1234-12346
NBBJD-U1234-12347
NBBJD-U1234-12348
NBBJD-U1234-12349

Upvotes: 0

Views: 1284

Answers (1)

anon
anon

Reputation:

There isn't really a good answer for this, as it depends upon how you query the data. If you are typically trying to look up specific records by UNIQUE_ID then it doesn't matter. However, if you are looking up records by machinename then you would probably want that at the front of your column.

Better yet, make each of these values a separate column, and have a truly synthetic primary key based off of a sequence or GUID. That way the ordering of the data isn't an issue at all. You'll thank yourself later on.

Upvotes: 2

Related Questions