Which two statements are true regarding B-tree index?

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.

Correct Answer: AD
Explanation/Reference:
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 maximumof 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 ina 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 -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 ofcreation. 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

Download Printable PDF. VALID exam to help you PASS.