nerdexam
Oracle

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.

Managing Schema Objects

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)
  • A
    90% (19)
  • B
    5% (1)
  • C
    5% (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.

AThe leaf blocks in the index are doubly linked.Correct

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.

BThe leaf node stores a bitmap for each key value.

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.

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

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.

DThe deletion of a row from the table causes a logical deletion in index leaf block and the spaceCorrect

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

#B-tree index#leaf block structure#NULL values#index deletion

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice