B-tree Index

  • B-tree stands for Balanced Tree in RDBMS. It is commonly used in database and filesystem.The B-tree enables the database to find a leaf node quickly. It is a good example of a data structure for external memory. Telephone directory, library are the best example of B-tree index.


Why do we use B-tree?

  • keeps keys in sorted order for sequential traversing.
  • uses a hierarchical index to minimize the number of disk reads.
  • uses partially full blocks to speed insertions and deletions.
  • keeps the index balanced with a recursive algorithm.

Syntax to Create, Rename, Drop B-tree Index

 Example of B-tree Index

Rename Index

Drop Index

 Why do we not use B-tree?
  • B-trees are powerful not just because they allow any file item to be immediately located using any attribute as a key, but because they work even when the file is very dynamic, with items constantly being inserted, changed, or deleted. But imagine a parts file where new items are infrequently added or changed (say, once every few months) and rarely deleted. It may be tempting to use a B-tree to allow instant lookup of a part number for a given description, but a B-tree is probably overkilled in this situation since the data file being indexed rarely changes.Whenever a data file is relatively static and unchanging, it may be better to use an indexing method simpler than B-trees, such as a minimum file of pointers that is completely rebuilt any time the target data file is changed.
  • If your data entry programs are immediately and correctly updating your B-trees the moment a data file item is created, changed, or deleted, then your B-trees are always up-to-date, and would only have to be rebuilt after a disastrous crash. But if you’re not immediately updating your B-trees and instead periodically rebuilding them from scratch, then the B-trees are not any more valuable than the simpler type of index files presented here.

Leave a Reply

Your email address will not be published. Required fields are marked *