nerdexam
Oracle

1Z0-052 · Question #128

Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)

The correct answer is A. The corresponding indexes for the table are also truncated. D. The highwater mark (HWM) is set to point to the first usable data block in the table segment. E. No undo or very little undo data is generated during the execution of the TRUNCATE command. TRUNCATE is a DDL operation that resets the HWM, truncates associated indexes, and generates minimal undo, but it does not fire DELETE triggers and does not cascade to child tables.

Managing Data and Concurrency

Question

Which three descriptions are correct about the effects of the TRUNCATE command on a table? (Choose three.)

Options

  • AThe corresponding indexes for the table are also truncated.
  • BDelete triggers on the table are fired during the execution of the TRUNCATE command.
  • CThe child table is truncated when the TRUNCATE command is applied on the parent table.
  • DThe highwater mark (HWM) is set to point to the first usable data block in the table segment.
  • ENo undo or very little undo data is generated during the execution of the TRUNCATE command.

How the community answered

(25 responses)
  • A
    92% (23)
  • B
    4% (1)
  • C
    4% (1)

Why each option

TRUNCATE is a DDL operation that resets the HWM, truncates associated indexes, and generates minimal undo, but it does not fire DELETE triggers and does not cascade to child tables.

AThe corresponding indexes for the table are also truncated.Correct

When a table is truncated, Oracle also truncates all associated indexes on that table, resetting their segment storage and high water marks along with the table itself.

BDelete triggers on the table are fired during the execution of the TRUNCATE command.

TRUNCATE is a DDL operation and does not execute row-by-row processing, so DELETE triggers are never fired; only DML DELETE statements activate those triggers.

CThe child table is truncated when the TRUNCATE command is applied on the parent table.

TRUNCATE does not cascade to child tables; if a parent table has a foreign key relationship, truncating the parent will fail unless the FK constraint is disabled or the child table is empty.

DThe highwater mark (HWM) is set to point to the first usable data block in the table segment.Correct

TRUNCATE resets the high water mark to the first usable data block in the segment, making the space immediately available for reuse and allowing full scans to skip the previously allocated but now empty blocks.

ENo undo or very little undo data is generated during the execution of the TRUNCATE command.Correct

Because TRUNCATE is a DDL statement processed as a direct path operation, it generates no or negligible undo data, which is why it cannot be rolled back with a ROLLBACK statement unlike a DELETE.

Concept tested: Oracle TRUNCATE command behavior and DDL characteristics

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html

Topics

#TRUNCATE#high water mark#indexes#undo generation

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice