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.
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.
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.
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 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.
Leaf pages contain key/RID combinations that point to actual user data rows.
Get new content delivered directly to your inbox.