< Rocky> in starrock which way is better to clear table trun StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV> in starrock, which way is better to...

# questions-and-troubleshooting

Lý Thành

07/01/2026, 4:32 AM

@Rocky in starrock, which way is better to clear table: truncate or delete table

Rocky

07/01/2026, 4:32 AM

In StarRocks, TRUNCATE TABLE
is significantly better than

DELETE

for clearing all data from a table. Here is a breakdown of why

TRUNCATE

is preferred and when to use each: 1. Why
TRUNCATE TABLE
is Better * Performance:

TRUNCATE

is a metadata-level operation. It removes the data files directly and immediately, making it much faster than a

DELETE

statement, which must scan and process individual rows. * Resource Efficiency: Unlike

DELETE

TRUNCATE

does not trigger heavy compaction (background merge) processes. Compaction consumes significant CPU and I/O resources to physically remove marked records. * Immediate Space Recovery: Storage space is released immediately after a

TRUNCATE

operation. * No Impact on Queries: Because it doesn't create "tombstone" records or increase data versions, it does not degrade the performance of future queries on that table. 2. The Problems with
DELETE
for Clearing Tables * Soft Deletion:

DELETE

in StarRocks (especially for non-Primary Key models) often works by marking rows as deleted. These rows are only physically removed during the background compaction process. * High Overhead: Running a

DELETE

on a large table can lead to high system load, transaction timeouts, and can even cause the database to become sluggish or unresponsive if the data volume is massive. * Versioning: Every

DELETE

creates a new data version, which can lead to "too many versions" errors if done frequently. 3. Comparison Summary Feature | TRUNCATE TABLE
| DELETE FROM ...
Best For | Clearing an entire table or specific partitions. | Deleting specific rows based on a condition (

WHERE

WHERE

clauses). Complexity | Minimal system impact. | High CPU/IO impact for large datasets. Recommendations * To clear everything: Use

TRUNCATE TABLE <table_name>;

* To clear specific partitions: Use

TRUNCATE TABLE <table_name> PARTITION (<partition_names>);

* To remove the table entirely: Use

DROP TABLE <table_name> FORCE;

if you want to skip the recycle bin and release disk space immediately. * Only use
DELETE
when you need to remove a specific subset of rows (e.g.,

WHERE user_id = 123

). Avoid using

DELETE

without a

WHERE

clause. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md • faq/Others.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

Lý Thành

07/01/2026, 7:43 AM

@Rocky this correct UPDATE dmapp.dtm_customer_ld_summary AS b SET b.max_dpd_history = a.max_dpd_history FROM dwh_link.dwh_customer_ld_summary_upd AS a WHERE a.sys_run_date = b.sys_run_date AND a.ld_no = b.ld_no AND a.sys_run_date = DATE'2025-11-01' AND a.max_dpd_history <> b.max_dpd_history

Rocky

07/01/2026, 7:44 AM

Your query is not quite correct for StarRocks syntax. While StarRocks supports multi-table updates (starting from version 3.0), there are specific rules you must follow: 1. Key Corrections Needed * Column Prefix in SET: In StarRocks, the column being updated in the

SET

clause cannot have a table name or alias prefix. You must use

SET max_dpd_history = ...

instead of

SET b.max_dpd_history = ...

. * Target Table Alias: StarRocks typically expects the full table name in the

UPDATE

clause without an

AS

alias. You can reference the full table name in the

WHERE

clause to join it with the

FROM

table. * Primary Key Requirement: The target table (

dmapp.dtm_customer_ld_summary

) must be a Primary Key table. StarRocks does not support

UPDATE

on Duplicate, Aggregate, or Unique Key models. 2. Corrected Query Assuming your target table is a Primary Key table, the correct syntax is:

Copy code

sql
UPDATE dmapp.dtm_customer_ld_summary
SET max_dpd_history = a.max_dpd_history
FROM dwh_link.dwh_customer_ld_summary_upd AS a
WHERE a.sys_run_date = dmapp.dtm_customer_ld_summary.sys_run_date
  AND a.ld_no = dmapp.dtm_customer_ld_summary.ld_no
  AND a.sys_run_date = DATE'2025-11-01'
  AND a.max_dpd_history <> dmapp.dtm_customer_ld_summary.max_dpd_history;

3. Summary of Rules * Multi-table Joins: Supported via the

FROM

clause since v3.0. * Column referencing: In the

SET

clause, use

column = expression

. Do not use

table.column = expression

. * Join Logic: StarRocks converts the

FROM

and

WHERE

clauses into a join. If multiple rows in the source table (

) match a single row in the target table, the target row will be updated with a value from a random matching row. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • faq/Others.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • faq/Others.md

Lý Thành

07/01/2026, 8:29 AM

@Rocky to starrocks table: -- DMAPP.DTM_SOLVENCY_TBL definition CREATE TABLE "DMAPP"."DTM_SOLVENCY_TBL" ( "REPORT_DATE" DATE NOT NULL ENABLE, "CONTRACT_NO" VARCHAR2(30) COLLATE "USING_NLS_COMP", "ACCOUNT_NUMBER" VARCHAR2(35) COLLATE "USING_NLS_COMP", "LD_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LD_DI_NO" VARCHAR2(5) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "PRI_PAST_DUE" NUMBER, "INT_PAST_DUE" NUMBER, "PRI_OVER_DUE_DAYS" NUMBER, "INT_OVER_DUE_DAYS" NUMBER, "LD_OVER_DUE_DAYS" NUMBER, "LD_FINAL_STATUS" VARCHAR2(5) COLLATE "USING_NLS_COMP", "FINAL_DEBT_GROUP" VARCHAR2(5) COLLATE "USING_NLS_COMP", "PRI_DUE_AMT" NUMBER, "INT_DUE_AMT" NUMBER, "F_2_TO_7_DAYS_PRI" NUMBER, "F_2_TO_7_DAYS_INT" NUMBER, "F_8_TO_30_DAYS_PRI" NUMBER, "F_8_TO_30_DAYS_INT" NUMBER, "F_31_TO_180_DAYS_PRI" NUMBER, "F_31_TO_180_DAYS_INT" NUMBER, "F_181_TO_360_DAYS_PRI" NUMBER, "F_181_TO_360_DAYS_INT" NUMBER, "F_361_TO_365_DAYS_PRI" NUMBER, "F_361_TO_365_DAYS_INT" NUMBER, "F366_DAYS_PLUS_PRI" NUMBER, "F366_DAYS_PLUS_INT" NUMBER, "WRITE_OFF" NUMBER ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_DATA_16K" ; CREATE INDEX "DMAPP"."DTM_SOLVENCY_TBL_IDX1" ON "DMAPP"."DTM_SOLVENCY_TBL" ("REPORT_DATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_INDEX" ; CREATE INDEX "DMAPP"."DTM_SOLVENCY_TBL_IDX3" ON "DMAPP"."DTM_SOLVENCY_TBL" ("REPORT_DATE", "ACCOUNT_NUMBER") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_INDEX" ; CREATE INDEX "DMAPP"."DTM_SOLVENCY_TBL_IDX2" ON "DMAPP"."DTM_SOLVENCY_TBL" ("REPORT_DATE", "CONTRACT_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_INDEX" ; GRANT SELECT ON "DMAPP"."DTM_SOLVENCY_TBL" TO "DM_THN"; GRANT SELECT ON "DMAPP"."DTM_SOLVENCY_TBL" TO "RO_DMAPP";

Rocky

07/01/2026, 8:30 AM

To convert your Oracle DDL to StarRocks, you need to map Oracle's data types to StarRocks types and choose a table model (Duplicate, Primary Key, etc.). Given the indexes in your DDL,

REPORT_DATE

is a primary filter. Below is the optimized StarRocks DDL using the Duplicate Key model (best for general analytical queries) and Range Partitioning by date. StarRocks DDL

Copy code

sql
CREATE TABLE dmapp.dtm_solvency_tbl (
    report_date           DATE NOT NULL,
    contract_no           VARCHAR(120), -- Oracle VARCHAR2(30) * 4 for UTF-8 safety
    account_number        VARCHAR(140),
    ld_no                 VARCHAR(80) NOT NULL,
    ld_di_no              VARCHAR(20) NOT NULL,
    pri_past_due          DECIMAL(38, 2), -- Mapping Oracle NUMBER to DECIMAL
    int_past_due          DECIMAL(38, 2),
    pri_over_due_days     INT,            -- Use INT for day counts
    int_over_due_days     INT,
    ld_over_due_days      INT,
    ld_final_status       VARCHAR(20),
    final_debt_group      VARCHAR(20),
    pri_due_amt           DECIMAL(38, 2),
    int_due_amt           DECIMAL(38, 2),
    f_2_to_7_days_pri     DECIMAL(38, 2),
    f_2_to_7_days_int     DECIMAL(38, 2),
    f_8_to_30_days_pri    DECIMAL(38, 2),
    f_8_to_30_days_int    DECIMAL(38, 2),
    f_31_to_180_days_pri  DECIMAL(38, 2),
    f_31_to_180_days_int  DECIMAL(38, 2),
    f_181_to_360_days_pri DECIMAL(38, 2),
    f_181_to_360_days_int DECIMAL(38, 2),
    f_361_to_365_days_pri DECIMAL(38, 2),
    f_361_to_365_days_int DECIMAL(38, 2),
    f366_days_plus_pri    DECIMAL(38, 2),
    f366_days_plus_int    DECIMAL(38, 2),
    write_off             DECIMAL(38, 2)
) 
DUPLICATE KEY(report_date, contract_no, account_number) -- Sorting keys (from your Oracle indexes)
PARTITION BY RANGE(report_date) (
    START ("2023-01-01") END ("2026-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(ld_no) BUCKETS 16; -- Use a high-cardinality column for bucketing

Key Mapping Considerations: 1. Data Types: * VARCHAR2
→
VARCHAR
: In StarRocks,

VARCHAR

length is in bytes. Since Oracle's

VARCHAR2(30)

might contain multi-byte characters, I've used a multiplier (x4) to be safe. * NUMBER
→
DECIMAL
or
INT
: For currency/amounts, use

DECIMAL(38, 2)

. For days or counters, use

INT

BIGINT

to improve performance. 2. Table Model: * I used the Duplicate Key model. If you intend to perform row-level updates (as seen in your previous question), you should consider the Primary Key model instead, but that requires defining a set of columns as a unique primary key. 3. Indexing (Sorting Keys): * In StarRocks, the

DUPLICATE KEY(...)

columns define the physical sort order. I put

report_date

first because your Oracle indexes suggest it is the most common filter. This replaces the need for the secondary indexes you had in Oracle. 4. Partitioning: * Added

PARTITION BY RANGE

report_date

. This is critical for performance in StarRocks when dealing with time-series or reporting data, as it allows the engine to skip entire partitions of data. 5. Ignored Oracle Specifics: * Attributes like

PCTFREE

TABLESPACE

STORAGE

, and

LOGGING

are specific to Oracle's storage engine and are not used in StarRocks. To grant permissions:

Copy code

sql
GRANT SELECT ON TABLE dmapp.dtm_solvency_tbl TO ROLE 'dm_thn';
GRANT SELECT ON TABLE dmapp.dtm_solvency_tbl TO ROLE 'ro_dmapp';

References • table_design/data_types/VARCHAR.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/duplicate_key_table.md • table_design/data_types/DECIMAL.md

Lý Thành

07/01/2026, 9:27 AM

@Rocky DELETE from dmapp.DTM_CLZB_ACCOUNT_SCHEDULES a where a.sys_run_date is null; correct?

Rocky

07/01/2026, 9:28 AM

The query you provided is likely not correct for StarRocks for two main reasons: 1. Table Alias Syntax In StarRocks, the

DELETE

statement generally does not support aliasing the target table directly in the

FROM

clause (e.g.,

DELETE FROM table a

). You should reference the table name directly and use the column name without an alias. 2.
IS NULL
Support (Depends on Table Model) * Duplicate, Aggregate, and Unique Key Tables: These models only support a limited set of operators:

>=

<=

!=

IN

, and

NOT IN

. IS NULL
is not supported for these table types in a

DELETE

statement. * Primary Key Tables: These tables support "full

DELETE WHERE

semantics," meaning

IS NULL

is supported. How to fix it: If your table is a Primary Key Table: Remove the alias

and the qualified column reference:

Copy code

sql
DELETE FROM dmapp.DTM_CLZB_ACCOUNT_SCHEDULES 
WHERE sys_run_date IS NULL;

If your table is a Duplicate, Aggregate, or Unique Key Table: The

IS NULL

condition will fail. To handle this, you typically have two options: 1. Use a dummy value: If possible, design your schema to use a specific "magic date" (like

1900-01-01

) instead of

NULL

, which would allow you to run

DELETE ... WHERE sys_run_date = '1900-01-01'

. 2. Overwrite the data: Use

INSERT OVERWRITE

to filter out the null rows, which is more efficient for non-Primary Key tables. Summary of Requirements: * No Alias: Use

DELETE FROM table_name WHERE column_name...

. * PK Table Only: Use

IS NULL

only if the table is a Primary Key table. * Simple Conditions: For non-PK tables, conditions must be

column op literal

(no functions or complex expressions). References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • faq/operation_maintenance_faq.md • faq/Sql_faq.md References • table_design/table_types/duplicate_key_table.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

Lý Thành

07/01/2026, 9:30 AM

@Rocky Duplicate, Aggregate, and Unique Key Tables: These models only support a limited set of operators:

>=

<=

!=

IN

, and

NOT IN

. IS NULL
is not supported for these table types in a

DELETE

statement. why IS NULL
is not supported?

Rocky

07/01/2026, 9:30 AM

The reason

IS NULL

is not supported in

DELETE

statements for Duplicate, Aggregate, and Unique Key tables is due to how StarRocks handles deletions for these specific table models. 1. The Technical Reason: Metadata-Based Deletion Unlike the Primary Key table, which uses a "Delete Vector" to mark specific rows as deleted, the other models use Delete Predicates. * When you execute a

DELETE

on a Duplicate/Unique/Aggregate table, StarRocks doesn't find and mark individual rows. Instead, it stores your

WHERE

clause as a metadata record (a condition) attached to the data version. * During a query, the storage engine reads this condition and filters out matching rows on-the-fly (Merge-on-Read). * The underlying engine for these specific models was designed to handle a fixed set of simple binary comparison operators (

, etc.). IS NULL
was simply not implemented in the original logic that evaluates these metadata-based delete conditions. 2. Primary Key Tables are Different The Primary Key table (introduced later) uses a different architecture. It supports "Full

DELETE WHERE

semantics" (including

IS NULL

, subqueries, and joins) because it executes the

WHERE

clause as a standard

SELECT

first to find the specific primary keys, and then marks those keys as deleted in a bitmap. Summary Comparison Feature | Duplicate / Aggregate / Unique Key | Primary Key Table Delete Mechanism | Stores a "Condition" in metadata | Marks specific rows in a "Delete Vector" Condition Evaluation | During Query (Read-time) | During Delete (Write-time) Supported Operators |

>=

<=

!=

IN

NOT IN

| All (including

IS NULL

, functions, subqueries) Workaround for non-PK tables If you must delete rows where a column is null in a Duplicate/Aggregate/Unique table, you cannot use the

DELETE

statement directly. Instead, use INSERT OVERWRITE
:

Copy code

sql
-- This effectively "deletes" the NULL rows by overwriting the table 
-- with only the non-null data.
INSERT OVERWRITE dmapp.your_table 
SELECT * FROM dmapp.your_table 
WHERE sys_run_date IS NOT NULL;

References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md

3 Views

Open in Slack

Previous Next