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.
