1

I am wondering how Oracle [or any other database] manages and maintains B-Tree indexing for columns with dynamically changing data.

Suppose I have a table with the following columns:

Now if I have indexing on (Status) [which does not make much sense :P], I am wondering how Oracle maintains B-Tree structure for index as described in Oracle docs for B-Tree indexing when value in the indexed column keeps changing.

https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721

For eg. initially, the B-Tree index is organised for a given snapshot of of table and value of Status changes for some records. How does Oracle manage these updates and maintains B-Tree structure for the new snapshot of table with updated Status values. Now the sorted sequence of records would change as values of indexed columns has changed.

How would Oracle internally manage and maintain B-Tree structure in such scenario?

Thanks in Advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dpk
  • 11
  • 3
  • This is an good question but unfortunately not a good question for this site. A full answer would have to cover many features, like partitions, simple and advanced compression, inmemory, 90/10 and 50/50 block splits, etc. Most of the internals aren't documented but if you search for "Oracle index block splits" you can find people who have debugged and traced Oracle to try to decipher how it works. – Jon Heller May 21 '17 at 01:36
  • a [B-tree simulator](http://www.cs.usfca.edu/~galles/visualization/BPlusTree.html) – miracle173 May 21 '17 at 22:18
  • an update of an index is a deletion followed by an insert – miracle173 May 21 '17 at 22:22
  • At the [Oracle Support Site](https://support.oracle.com) (you will have access if you own a database license) you can find articles that describe some details, e.g. "How Btree Indexes Are Maintained (Doc ID 30405.1)", But maybe you should start with [Wiki](https://en.wikipedia.org/wiki/B-tree) – miracle173 May 21 '17 at 22:35

0 Answers0