How to Understand DB2 B-Tree Index Quickly

Indexed Table gets data much faster. Indexing is needed since the Table scan takes a lot of time. Tablespace has multiple pages. In DB2, when you define an index on a Table, it follows the B-tree indexing mechanism.

B-Tree Index

Here to reach the table data it pass through four levels of pages. Each has address of its non-leaf page. The leaf page has address of table data.

DB2: How to Understand B-Tree Structure of Index

In the case of Table scan, it starts searching data in all the pages of Table space. You know that a Table space can have multiple pages. So Table scan takes a lot of time.

Root Page

The root page is the starting point of the search through an index. It is always at the top of the index structure and physically it is stored as the third page in the index space (after the header page and the space map page).

Non-Leaf page

Non-leaf pages are index pages that point to other index pages. When the number of levels in an index reaches 3, the middle level will be this type of page. Prior to the index reaching 3 levels, there are no non-leaf pages in the internal index structure (just a root page that points to leaf pages). Entries in non-leaf pages are made up of the key and RID of the highest key value contained in each page on the level below it.