Introduction - Relational Database Index Design and the Optimizers - page 6

ž The index in total holds about 3,000,000 4 K pages, which requires 12 GB
of disk space.
ž The total size of the leaf pages is 2 , 900 , 000 × 4 K, which is almost 12 GB.
It is reasonable to assume that these will normally be read from a disk
drive (10 ms).
ž The size of the next level is 83 , 000 × 4 K, which is 332 megabytes (MB);
if the index is actively used , then these pages may stay in the read cache
(perhaps 64 GB in size) of the disk server, if not in the database buffer
pool (say 4 GB for index pages).
ž The upper levels , roughly 2500

× 4K = 10 MB, will almost certainly

remain in the database buffer pool.
Accessing any of these 100,000,000 index rows in this six-level index will then
take between 10 and 20 ms. This is true even if many index rows have been added
and the index is disorganized, but more about this in Chapter 11. Consequently,
it makes little sense to set arbitrary limits to the number of levels.

Myth 2: No More Than Six Indexes per Table

In its positive attitude toward indexes, the Oracle SQL Tuning Pocket Reference
(2) by Mark Gurry is an agreeable exception to the comments made earlier. As
the title implies, the topic focuses on helping the Oracle 9i optimizers, but it also
criticizes standards that set an upper limit for the number of indexes per table on
page 63:
I have visited sites which have a standard in place that no table can have more
than six indexes. This will often cause almost all SQL statements to run
beautifully, but a handful of statements to run badly, and indexes can't be
added because there are already six on the table.
...
My recommendation is to avoid rules stating a site will not have any more
than a certain number of indexes.
...
The bottom line is that all SQL statements must run acceptably. There is
ALWAYS a way to achieve this. If it requires 10 indexes on a table, then you
should put 10 indexes on the table.

Myth 3: Volatile Columns Should Not Be Indexed

Index rows are held in key sequence, so when one of the columns is updated,
the DBMS may have to move the corresponding row from its old position in the
index to its new position, to maintain this sequence. This new position may be
in the same leaf page, in which case only the one page is affected. However,
particularly if the modified key is the first or only column, the new index row
mayhavetobemovedtoa different leaf page; the DBMS must then update two
leaf pages. Twenty years ago , this might have required six random disk reads if