Using INSERT, UPDATE, and DELETE

DML statements INSERT, UPDATE, and DELETE perform the same functions that they do in any ACID compliant database. INSERT is typically used to load individual rows into physical memory or load a table using an INSERT AS SELECT clause. UPDATE and DELETE are used to modify the data.

You can intermix INSERT, UPDATE, and DELETE. Vertica follows the SQL-92 transaction model. In other words, you do not have to explicitly start a transaction but you must use a COMMIT or ROLLBACK command (or COPY) to end a transaction. If you cancel a DML statement, Vertica rolls back the statement.

Vertica differs from traditional databases in two ways:

DELETE does not actually delete data from disk storage; it marks rows as deleted so they are available for historical queries.
UPDATE writes two rows: one with new data and one marked for deletion.

Unless you specify otherwise, INSERT, UPDATE and DELETE statements write data to the WOS. On overflow, the statements write data to the ROS.

Tip: For large INSERT or UPDATE operations, use the DIRECT hint. This hint forces Vertica to load all data directly to ROS. Loading large number of rows as single row inserts is not recommended for performance reasons. Instead, use COPY.