Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 203

Using internal system tables, the DBMS finds a pointer to the root page.
After reading the root page, the DBMS knows that it needs to read the second
leaf page to find the pointers that relate to key value 12.
One random read brings one page from the disk drive. If the system table
pages needed to locate the root page are already in the database buffer pool, the
SELECT may take three random reads: the root page, a leaf page, and a table
page. However, with current hardware, the nonleaf pages are likely to stay in the
database buffer pool, or at least in the read cache, because they are frequently
referenced. Therefore, it is reasonable to assume that only two random reads take
place; the synchronous I/O time for this is about 2 × 10 ms = 20 ms. The CPU
time is much less than this with current processors, so the elapsed time for the
SELECT is likely to be about 20 ms also.
When the DBMS needs to read an index slice , it reads the first index row
as described above. The first index row has a pointer to the next index row. The
DBMS follows this pointer chain until it comes to an index row that does not
satisfy the matching predicates that define the slice. All index rows are chained
together according to the index key.
In SQL 11.2, the SELECT statement is looking for customers who have an
LNAME starting with, say, the character string 'JO' and a CITY starting with,
say, the character string 'LO'. In this case the DBMS first tries to find a row in
the index (LNAME, CITY) with the key value JO padded to the maximum index
length with character “A”s as in JOAA AA.

When it finds the position for this key value, it reads the following index
rows using the chain, until it finds an index row whose key value does not start
with 'JO'.

...

SQL 11.2

DECLARE CURSOR112 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME BETWEEN :LNAME1 AND :LNAME2

AND
CITY BETWEEN :CITY1 AND :CITY2

ORDER BY FNAME

WHAT HAPPENS WHEN A ROW IS INSERTED?

If a table has a clustering index, the DBMS tries to place an inserted row in
the table page to which it belongs, according to the clustering index key value
(the home page). If the row does not fit in the home page, or the home page is
locked, the DBMS will check the pages close to the home page. In the worst
case, the new table row is placed in the last page of the table. Existing table rows
are usually left where they are, depending on the DBMS and the type of table