0

I was reading about "Index contention" and found the below explanation:

Oracle b-tree indexes are “right-handed” and the right-hand leafs of the b-tree contain the highest key in the lowest tree level.

Index leaf node contention happens when rows are inserted based on a user generated key (i.e. a sequence) and because the sequence key is always the high order key, each insert of a low-level index tree node must propagate upwards to the high-key indicators in the b-tree index.

Are there any other disadvantages of oracle b-tree indexes being right handed?

And what could be the other reasons for index contention/insertion contention?

zer0Id0l
  • 1,374
  • 4
  • 22
  • 36

2 Answers2

2

Index contention, or hot spots, can occur when indexing a serially generated pk. In this case all new records are always inserted on the right most leaf of the index causing latches.

To avoid this issue oracle introduced the reversed key option. In the reverse key index entries are saved reversed (simply put - 123 will be stored as 321) and new inserted will spread across the index.

Generally speaking, hot spot will occur every time you insert a lot of data into the same leaf block.

Important notice - when using reversed key index you wont be able to use range scans so weigh your options.

haki
  • 9,389
  • 15
  • 62
  • 110
0

My question was specifically for oracle b-tree indexes and not about what "index contention" actually is. Although would like to add few more points to my question and hence answering it.

Index leaf node contention happens when rows are inserted based on a user generated key (i.e. a sequence) and because the sequence key is always the high order key, each insert of a low-level index tree node must propagate upwards to the high-key indicators in the b-tree index.

There are three techniques that are used to relieve this index contention issue:

  • Reverse key indexes
  • Sequences with the cache and noorder options
  • Using hash partitioned global indexes
  • Adjusting the index block size
zer0Id0l
  • 1,374
  • 4
  • 22
  • 36