1Z0-052 · Question #183
Which two statements are true regarding B-tree index? (Choose two.)
The correct answer is A. The leaf blocks in the index are doubly linked. D. The deletion of a row from the table causes a logical deletion in index leaf block and the space. Oracle B-tree indexes use a doubly linked leaf block structure for range scans and mark deleted row entries as logically deleted rather than immediately reclaiming space.
Question
Which two statements are true regarding B-tree index? (Choose two.)
Options
- AThe leaf blocks in the index are doubly linked.
- BThe leaf node stores a bitmap for each key value.
- CThe rows with NULL value in key columns also have entries in the index.
- DThe deletion of a row from the table causes a logical deletion in index leaf block and the space
How the community answered
(21 responses)- A90% (19)
- B5% (1)
- C5% (1)
Why each option
Oracle B-tree indexes use a doubly linked leaf block structure for range scans and mark deleted row entries as logically deleted rather than immediately reclaiming space.
B-tree index leaf blocks in Oracle are organized as a doubly linked list, meaning each leaf block contains pointers to both the previous and the next leaf block, enabling efficient full index scans and range scans in both ascending and descending order.
Storing a bitmap for each key value is the structure used by bitmap indexes, not B-tree indexes; B-tree leaf nodes store individual key values paired with their corresponding ROWIDs.
In Oracle B-tree indexes, rows where all indexed key columns contain NULL values are not stored in the index, which means queries using IS NULL on an indexed column cannot use a standard B-tree index for that condition.
When a row is deleted from a table, Oracle does not immediately remove the corresponding index entry; instead, the entry is marked as logically deleted. The space occupied by that entry can be reused by future inserts into the same block but is not immediately reclaimed or returned to free space.
Concept tested: Oracle B-tree index structure and leaf block behavior
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html
Topics
Community Discussion
No community discussion yet for this question.