ISAM or B-tree?

The B-tree and ISAM data structures share many of the same advantages over the other storage structures, but they differ in important respects.

When to Choose ISAM over B-tree

The ISAM storage structure has the following advantages over B-tree:

• ISAM is better for static tables (ones that have no updates on key fields, appends, or deletes) where no overflow chains exist.
• ISAM requires fewer disk operations to visit a data page than B-tree, because B-tree has an additional leaf level.
• ISAM is much better for small tables. B-tree requires a minimum of a free list header page, a root page, a leaf page, and a data page. ISAM requires only a root and a data page. B-trees for less than 10 to 15 pages are better stored as ISAM. B-tree tables take up more space than do ISAM tables; this is most noticeable when tables are small.
• ISAM requires no locking in the index pages, while B-tree incurs index locking; therefore concurrent performance in the index of a B-tree is not as good as concurrent performance in the index pages of an ISAM. However, concurrent usage in B-tree data pages is better than concurrent usage in ISAM data pages if the ISAM table has long overflow chains.

When to Choose B-tree over ISAM

The B-tree storage structure has the following advantages over ISAM:

• B-tree is essential in tables that are growing at a rate that quickly causes overflow in an ISAM structure (for example, situations where there are ever-increasing keys).
• B-tree is better when sorting on the key is required, because sequential access (for example, select * from emp) to data in B-tree is automatic; there is no need to add a sort clause to queries, if you are sorting on the primary key. Btree also eliminates sorting of the joining column when joining on key columns; sort-merge queries are more efficient if the tables joined are B-tree.