challenge: a doubly linked list and a search tree • Each node refers to the preceding and the following node. The database can read the index in any direction. • Insert new entries without moving large amounts of data
order. A database needs a second structure to find the entry: a balanced search tree - the B-tree. • The doubly linked list establishes the logical order between the leaf nodes. The root and branch nodes support quick searching among the leaf nodes.
tree depth is equal at every position; • The database maintains the index automatically. It applies every insert, delete and update to the index and keeps the tree in balance • The tree traversal is a very efficient operation. It works almost instantly - even on a huge data set. That is primarily because of the tree balance, which allows accessing all elements with the same number of steps, and secondly because of the logarithmic growth of the tree depth.
database must read the next leaf node to see if there are any more matching entries • The second problem is accessing the table. Even a single leaf node might contain many hits—often hundreds Slow indexes
EMPLOYEES_PK index. That index does not cover the LAST_NAME column • The next step is the fetch the table data operation • Once the LAST_NAME column is available, the database can evaluate the remaining part of the where clause • The statement's response time does not depend on the result set size but on the number of employees in the particular subsidiary.