Which two statements are true regarding B-tree index?

Which two statements are true regarding B-tree index? (Choose two.)

Which two statements are true regarding B-tree index? (Choose two.)

A.
The leaf blocks in the index are doubly linked.

B.
The leaf node stores a bitmap for each key value.

C.
The rows with NULL value in key columns also have entries in the index.

D.
The deletion of a row from the table causes a logical deletion in index leaf block and the space
becomes available for the new leaf entry.

Explanation:
B-Tree Index
Structure of a B-tree Index
At the top of the index is the root, which contains entries that point to the next level in the index. At
the next level are branch blocks, which in turn point to blocks at the next level in the index. At the
lowest level are the leaf nodes, which contain the index entries that point to rows in the table. The
leaf blocks are doubly linked to facilitate the scanning of the index in an ascending as well as
descending order of key values.
Format of Index Leaf Entries
An index entry has the following components:
Entry header: Stores the number of columns and locking information
Key column length-value pairs: Define the size of a column in the key followed by the value for
the column (The number of such pairs is the maximum of the number of columns in the index.)
ROWID: Row ID of a row that contains the key values
B-Tree Index (continued)
Index Leaf Entry Characteristics
In a B-tree index on a nonpartitioned table:
Key values are repeated if there are multiple rows that have the same key value unless the index
is compressed
There is no index entry corresponding to a row that has all key columns that are NULL.
Therefore, a WHERE clause specifying NULL always results in a full table scan.
A restricted ROWID is used to point to the rows of the table because all rows belong to the same
segment
Effect of DML Operations on an Index
The Oracle server maintains all the indexes when DML operations are carried out on a table. Here
is an explanation of the effect of a DML command on an index:

Insert operations result in the insertion of an index entry in the appropriate block.
Deleting a row results only in a logical deletion of the index entry. The space used by the deleted
row is available for new sequential leaf entries.
Updates to the key columns result in a logical delete and an insert to the index. The PCTFREE
setting has no effect on the index except at the time of creation. A new entry may be added to an
index block even if it has less space than that specified by PCTFREE.

Types of Indexes
These are several types of index structures that are available depending on your needs. Two of
the most common are:
B-tree index
– Default index type; in the form of a balanced tree
Bitmap index:
– Has a bitmap for each distinct value indexed
– Each bit position represents a row that may or may not contain the indexed value.
– Best for low-cardinality columns



Leave a Reply 1

Your email address will not be published. Required fields are marked *