Vertica Analytics Platform Version 9.2.x Documentation

DELETE_VECTORS

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.

STORAGE_TYPE

VARCHAR

The type of storage containing the delete vector (WOS or ROS).

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.

IS_SORTED
BOOLEAN Whether the storage container's data is sorted (WOS containers only).

Example

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