Database Administration
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.  
Required Permissions
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. 
Remove Records Using TRUNCATE
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﻿. 
Remove All Table Records Using TRUNCATE 
In this example, the TRUNCATE command removes all segments contained in the students table. This operation frees up the disk storage after execution. 
SQL
TRUNCATE TABLE students;
TRUNCATE TABLE students;
﻿
Remove Data Using TRUNCATE with a Segment Group Filter
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.  
SQL
TRUNCATE TABLE students WHERE segment_group_id = 123456789;
TRUNCATE TABLE students WHERE segment_group_id = 123456789;
﻿
Remove Data Using TRUNCATE with a Time Range 
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. 
SQL
WITH inputs AS (
    SELECT 'public' AS theschema,
        'orders' AS thetable,
        '2019-01-01 00:00:00.000000000' AS starttime,
        '2021-01-02 23:00:00.000000000' AS endtime
)
SELECT 'TRUNCATE TABLE ' || i.theschema || '.' || i.thetable || ' WHERE segment_group_id IN (' || string_agg(sg.id, ',') || ')' AS trunc_sql
FROM inputs AS i
    CROSS JOIN sys.segment_groups AS sg
    JOIN sys.tables AS t ON t.id = sg.table_id
WHERE 
  (
    (t.name = i.theschema || '_' || i.thetable) 
    OR (t.name = i.thetable AND t.schema = i.theschema)
  )
  AND TO_TIMESTAMP(sg.begin_time / 1000000000) BETWEEN i.starttime AND i.endtime
  AND TO_TIMESTAMP(sg.end_time / 1000000000) BETWEEN i.starttime AND i.endtime
GROUP BY i.theschema,
    i.thetable;
WITH inputs AS (
    SELECT 'public' AS theschema,
        'orders' AS thetable,
        '2019-01-01 00:00:00.000000000' AS starttime,
        '2021-01-02 23:00:00.000000000' AS endtime
)
SELECT 'TRUNCATE TABLE ' || i.theschema || '.' || i.thetable || ' WHERE segment_group_id IN (' || string_agg(sg.id, ',') || ')' AS trunc_sql
FROM inputs AS i
    CROSS JOIN sys.segment_groups AS sg
    JOIN sys.tables AS t ON t.id = sg.table_id
WHERE 
  (
    (t.name = i.theschema || '_' || i.thetable) 
    OR (t.name = i.thetable AND t.schema = i.theschema)
  )
  AND TO_TIMESTAMP(sg.begin_time / 1000000000) BETWEEN i.starttime AND i.endtime
  AND TO_TIMESTAMP(sg.end_time / 1000000000) BETWEEN i.starttime AND i.endtime
GROUP BY i.theschema,
    i.thetable;
﻿
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
SQL
TRUNCATE TABLE public.orders WHERE segment_group_id IN (8070450568302397570)
TRUNCATE TABLE public.orders WHERE segment_group_id IN (8070450568302397570)
﻿
To remove the data, run the generated TRUNCATE command. 
Remove Records Using DELETE
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﻿. 
Remove Data Using DELETE with a Filter
In this example, the DELETE command removes all rows from the products table that have a value less than 1 in the rating column. 
SQL
DELETE FROM products WHERE rating < 1; 
DELETE FROM products WHERE rating < 1; 
﻿
Remove Data Using Joined Tables
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'.  
SQL
DELETE FROM point_table p
    JOIN us_state_table s ON ST_INTERSECTS(s.polygon_wkt, p.point_wkt)
WHERE s.state LIKE 'TX';
DELETE FROM point_table p
    JOIN us_state_table s ON ST_INTERSECTS(s.polygon_wkt, p.point_wkt)
WHERE s.state LIKE 'TX';
﻿
Data Durability Considerations
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.
Related Links
﻿Key Concepts﻿﻿
﻿Load Data﻿﻿
﻿Query Ocient﻿﻿
Updated 22 May 2024
Did this page help you?
CREATE TABLE SQL Statement Examples
Install an Ocient System