Remove Records from an Ocient System
supports multiple options for removing records from a database. Here, you can find the different methods for removing data along with their advantages and drawbacks to help you determine the method that best fits your needs.
All options for removing data are destructive and cannot be undone after execution. Therefore, be cautious when removing any Ocient records from your database.
To remove data, a user must have the DELETE privilege for the specified table. For more information on privileges, see the Data Control Language (DCL) Command Reference command reference.
The TRUNCATE command is a lightweight and efficient command for removing some or all records from an existing table. The command removes data by its segment group, but it always leaves the table and its schema intact.
This operation frees up the disk storage of the cleared data, making it useful for removing large data sets. In contrast, a DELETE command does not free up disk storage.
You can filter with the WHERE clause of the TRUNCATE command to identify specific segment groups to remove. Then, apply some filtering to remove specific segment groups by time range; however, you cannot filter based on the values of other table rows.
During loading, rows experience a short delay before the Ocient System stores them in a page for querying. For this reason, the TRUNCATE command cannot remove rows for a short period during the loading process.
For more details about the TRUNCATE command, see TRUNCATE TABLE.
In this example, the TRUNCATE command removes all segments contained in the students table. This operation frees up the disk storage after execution.
In this example, the TRUNCATE command uses a filter to remove all segments contained in the segment group 123456789.
To see the inventory of all segment groups that are present on your cluster, you can query the sys.segment_groups system table.
Add a time filter to a TRUNCATE command by matching segment groups that fall within a specific time range.
This example uses a query to generate a SQL statement that specifies the segment groups that contain data with a between 2019-01-01 and 2021-01-02.
Time filtering for segment groups is based on the column specified as the TimeKey for each table. The time granularity for segment groups is based on the time bucket column constraint. For details, see the Column Constraint subsection of CREATE TABLE.
This example generates a string of the SQL command that performs the TRUNCATE operation.
The example contains:
- The WITH clause assigns aliases to the database and table where you want to remove data. To use this example for your own query, replace the orders identifier with your own table name and the public identifier with your schema name.
- The WITH clause also defines a time range for filtering. To use your own filter, replace the starttime and endtime with your own values.
- The remainder of the query selects every segment group that matches the specified table and schema and has TimeKey columns within the specified time range.
After executing the example command, the system generates an output string of the TRUNCATE command that is customized to remove any segment groups that match your filter criteria.
Output
To remove the data, run the generated TRUNCATE command.
The DELETE command removes specific individual rows from a table based on the WHERE filter clause.
Unlike TRUNCATE, the DELETE command does not restore disk space of deleted rows without further system operations. In general, DELETE is a good option when you need to remove small row sets, not entire tables.
If a DELETE command has no WHERE filter, it removes all rows in the table.
The DELETE command can support concurrent operations to remove rows on the same table; however, this might impact performance.
For details about the DELETE command, see DELETE FROM TABLE.
In this example, the DELETE command removes all rows from the products table that have a value less than 1 in the rating column.
You can use DELETE with a JOIN statement to help filter rows for removal based on a condition that applies to the second table.
The command removes rows only from the first table and not the second joined table.
This example deletes rows after performing a JOIN operation on the geospatial values in two tables:
- point_table contains a column of geospatial point values.
- us_state_table contains columns of all abbreviated U.S. state names and polygon values representing their boundaries.
The JOIN operation uses the ST_INTERSECTS function to evaluate if each point value in point_table is contained within any of the polygon values in the us_state_table.
The example deletes any rows in point_table that are within the boundaries of Texas, abbreviated as 'TX'.
To remove data, the data must be queryable in the system. For example, streaming data systems might experience a delay before rows can be deleted while they are in progress of the loading process to become fully durable.
In most cases, data should be loaded and queryable within a matter of seconds.
Key Concepts
Load Data
Query Ocient