IBM 000-8697 Manual Do Utilizador

IBM Informix OnLine Database Server Administrator’s Guide

 through

 illustrate the progressive creation of a

complete index. A complete index is represented by

displays a root page, four branch pages, and an unspecified number of leaf
pages.

The rules governing index creation, page splitting, and page merging are far
more complicated than the treatment provided in this manual. In addition,
this manual does not include topics such as index-traversing, latching, and
deadlock-avoidance strategies that OnLine employs during modifications.
This section provides general information only. For detailed information
regarding B+ tree operations, refer to the

anual.

When index pages become empty, either because the rows whose keys filled
an index page are deleted or because the index is dropped, the pages are
completely freed. Former index pages remain dedicated to the extent, but
they are marked as free on the extent bit map and are available for
reassignment to store data, blobs, or other index information.

When a user creates an index, OnLine creates a B+ tree for the specified table
if data exists in the table. If the table is empty, only the root node page is
created. In the following

SQL

example, a simple ascending index is created on

the lname column:

CREATE INDEX lastname ON customer (lname)

If you are creating an index on an empty table, the first page of the index is
allocated as part of the statement execution, but it remains empty until data
is inserted into the table. The first page created is called the root node but, in
the beginning, the root node functions like a leaf node. As data is inserted
into the table, the first index page fills with an entry for each key value. The
index key value includes the rowid.

The index key value is composed of two parts:

■

A byte part, which expresses the value of the specified index key

■

A rowid part, which contains one or more rowids to data rows that
share the same key value