DELETE

Purpose

Use this statement to delete rows in a table.

Prerequisite

You need to have the system privilege DELETE ANY TABLE, object privilege DELETE on the table or its schema, or the table belongs to the current user or one of the user roles.

Syntax

delete::=

Delete Statement

Delete Statement

Usage Notes

  • Internally, the rows are not immediately deleted but marked as deleted. When a certain threshold is reached (default is 25% of the rows), these rows are finally dropped. Therefore DELETE statements can have varying execution times. The current percentage of marked rows can be found in system tables EXA_*_TABLES (for example, EXA_ALL_TABLES) in the column DELETE_PERCENTAGE.
  • If a row is marked as deleted, it is not possible to recover the data.
  • The PREFERRING clause defines a Skyline preference term. For more information, refer to the Skyline section.

Example

DELETE FROM staff WHERE name='SMITH';
DELETE FROM staff;