DELETE_VECTORS

Holds information on deleted rows to speed up the delete process.

Holds information on deleted rows to speed up the delete process.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node storing the deleted rows.
SCHEMA_NAME VARCHAR The name of the schema where the deleted rows are located.
PROJECTION_NAME VARCHAR The name of the projection where the deleted rows are located.
DV_OID INTEGER The unique numeric ID (OID) that identifies this delete vector.
STORAGE_OID INTEGER The unique numeric ID (OID) that identifies the storage container that holds the delete vector.
SAL_STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
DELETED_ROW_COUNT INTEGER The number of rows deleted.
USED_BYTES INTEGER The number of bytes used to store the deletion.
START_EPOCH INTEGER The start epoch of the data in the delete vector.
END_EPOCH INTEGER The end epoch of the data in the delete vector.

Examples

After you delete data from a Vertica table, that data is marked for deletion. To see the data that is marked for deletion, query the DELETE_VECTORS system table.

Run PURGE to remove the delete vectors from ROS containers.

=> SELECT * FROM test1;
 number
--------
      3
     12
     33
     87
     43
     99
(6 rows)
=> DELETE FROM test1 WHERE number > 50;
 OUTPUT
--------
      2
(1 row)
=> SELECT * FROM test1;
 number
--------
     43
      3
     12
     33
(4 rows)
=> SELECT node_name, projection_name, deleted_row_count FROM DELETE_VECTORS;
    node_name     | projection_name | deleted_row_count
------------------+-----------------+-------------------
 v_vmart_node0002 | test1_b1        |                 1
 v_vmart_node0001 | test1_b1        |                 1
 v_vmart_node0001 | test1_b0        |                 1
 v_vmart_node0003 | test1_b0        |                 1
(4 rows)
=> SELECT PURGE();
...
(Table: public.test1) (Projection: public.test1_b0)
(Table: public.test1) (Projection: public.test1_b1)
...
(4 rows)

After the ancient history mark (AHM) advances:

=> SELECT * FROM DELETE_VECTORS;
 (No rows)

See also