Skip to Main Content
  • Questions

  • storing and indexing prefixed values

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jess.

Asked: August 26, 2016 - 6:13 pm UTC

Last updated: August 27, 2016 - 2:27 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hi Tom,

We've got a table that'll hold about 1M rows over the course of its lifetime.
We need to add a column that the business want to search for, so indexing it seems like a good idea.

Column is
- a 10-character string,
- mostly unique (so different value almost for every row), but occasional duplicates will be allowed (so no formal unique index)
- the values will always be prefixed with 'XYZ' followed by a 7-digit random number, meaning the leading edge of the values is not distributed.

Is that prefix likely to be an issue for the index in terms of record distribution? What's the best practice for cases like these in general?

Thanks in advance!

and Connor said...

That should not be a problem.

We dont split index keys by *part* of a column - the entire column value forms the key part of the index.

So 1234 and 5678, are really no different from XYZ1234 and XYZ5678.

Since you say the values will be random, I'm assuming they will arrive in random fashion, so dont expect the index "density" to be 100%, since as blocks split to hold new data, you could probably expect it to be around 70-80%, but that's still a perfectly good index for usage.

(I'm assuming here people will search by providing the entire key)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jess, August 27, 2016 - 9:25 am UTC

Hi Connor,
Thanks for the quick reply.

It was _because_ the whole value is indexed that I was unsure--I'd read somewhere that indexes on values that all start with the same set of characters are not very efficient.

Although the numeric part of the value will be random, the first 3 chars will always be the same... (And yes, the search against the table will be on full 10-char strings)

Thanks again!

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.