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

CREATE INDEX index_name ON
table_name(column_name[, column_name ...])
TABLESPACE tab_space;
 Example of B-tree Index
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
CREATE INDEX i_emp_name ON emp(ename);

Rename Index

ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX i_emp_name RENAME To new_emp_name;

Drop Index

DROP INDEX index_name;
DROP INDEX i_emp_name;
 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.

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.