INSERT INTO blackhole()

INSERT INTO BLACKHOLE() SELECT ...

CACHE SELECT

CACHE SELECT

sql
INSERT INTO BLACKHOLE() 
SELECT * FROM remote_catalog.db.large_table 
WHERE dt = '2023-10-01';

INSERT INTO BLACKHOLE()

db

sql
CREATE RESOURCE GROUP high_priority_group
TO (db='important_db')
WITH (
    'cpu_weight' = '10',  -- Higher weight = more CPU share
    'mem_limit' = '50%'
);

sql
SELECT /*+ SET_VAR(resource_group = 'high_priority_group') */ * 
FROM critical_table 
JOIN other_table ON ...;

sql
CREATE RESOURCE GROUP app_group
TO (user='reporting_service_user')
WITH ('cpu_weight' = '20');

exclusive_cpu_cores

cpu_weight

exclusive_cpu_cores

concurrency_limit

cpu_weight

cpu_weight

cpu_weight

cpu_weight

cpu_core_limit

default_group

exclusive_cpu_cores

exclusive_cpu_cores

sql
    CREATE RESOURCE GROUP immediate_group
    TO (db='critical_db')
    WITH (
        "exclusive_cpu_cores" = "4", -- Adjust based on your BE core count
        "mem_limit" = "20%"
    );

cpu_weight

exclusive_cpu_cores

cpu_weight

exclusive_cpu_cores

db

catalog.database

default_catalog

sql
-- Use just 'starrocks' if that is the name of your database
CREATE RESOURCE GROUP high_priority_contextual
TO (db='starrocks')
WITH (
    'cpu_weight' = '10',
    'mem_limit' = '20%'
);

SHOW DATABASES;

_statistics

starrocks

db='sales_db'

TO (db='db1,db2,db3')

MV rewrite fail for curated_channel_enriched: stale partitions refreshType=FULL

SUCCESS

VALID

refreshType=FULL

refreshType=FULL

PARTITION BY

sql
ALTER MATERIALIZED VIEW curated_channel_enriched 
SET ("mv_rewrite_staleness_second" = "3600"); -- Allow data up to 1 hour old

loose

sql
ALTER MATERIALIZED VIEW curated_channel_enriched 
SET ("query_rewrite_consistency" = "loose");

force_external_table_query_rewrite

sql
-- See detailed reasons for rewrite failure
TRACE REASON MV <your_query>;

-- Check the exact version recorded in the MV vs Base Table
SHOW MATERIALIZED VIEWS LIKE 'curated_channel_enriched'\G

query_rewrite_consistency

query_rewrite_consistency

LOOSE

query_rewrite_consistency = 'loose'

CHECKED

mv_rewrite_staleness_second

Last Refresh Time + N seconds

mv_rewrite_staleness_second = '3600'

checked

loose

staleness_second

curated_channel_enriched

mv_rewrite_staleness_second

300

3600

LOOSE

is_active

false

query_rewrite_consistency

loose

query_rewrite_consistency = 'loose'

is_active = false

sql
    -- Look for the 'is_active' column
    SHOW MATERIALIZED VIEWS LIKE 'curated_channel_enriched';

sql
    -- If you see the external table name in the scan node instead of the MV name,
    -- it has fallen back to the base tables.
    EXPLAIN SELECT ...;

sql
    ALTER MATERIALIZED VIEW curated_channel_enriched ACTIVE;

ACTIVE

query_rewrite_consistency = 'loose'

exclusive_cpu_cores

exclusive_cpu_cores = 96

exclusive_cpu_cores cannot exceed the minimum number of CPU cores available on the backends minus one [13]

exclusive_cpu_cores

(min_be_cpu_cores - 1)

[13]

r8g.4xlarge

sql
-- This will reserve 8 cores ON EACH of your 12 nodes.
-- Total reservation = 12 * 8 = 96 cores cluster-wide.
ALTER RESOURCE GROUP your_group_name 
SET ("exclusive_cpu_cores" = "8");

exclusive_cpu_cores

enable_resource_group_cpu_borrowing

cpu_weight

exclusive_cpu_cores

100

200

SHOW BACKENDS;

CPUCores

default_wg

default_wg

name

resource_group_running_queries

{name="default_wg"}

resource_group_running_queries

{name="your_group_name"}

resource_group_running_queries

{name!="default_wg"}

sum(resource_group_inuse_cpu_cores{name!="default_wg"})

resource_group_inuse_cpu_cores{name="default_wg"}

sum(resource_group_running_queries{name!="default_wg"})

resource_group_running_queries{name="default_wg"}

sum(resource_group_mem_inuse_bytes{name!="default_wg"})

resource_group_mem_inuse_bytes{name="default_wg"}

$resource_group

label_values(resource_group_total_queries, name)

resource_group_inuse_cpu_cores

name

default_wg

starrocks_fe_query_resource_group...

resource_group_...

query_resource_group

cpu_weight

exclusive_cpu_cores

0

ALTER

exclusive_cpu_percent

0

sql
ALTER RESOURCE GROUP high_priority_contextual SET (
    'cpu_weight' = '0',
    'cpu_weight_percent' = '0',
    'exclusive_cpu_cores' = '0',
    'exclusive_cpu_percent' = '50'
);

cpu_weight

cpu_weight_percent

exclusive_cpu_cores

exclusive_cpu_percent

exclusive_cpu_percent

cpu_weight_percent

exclusive_cpu_cores

exclusive_cpu_percent

mem_limit

'resource_group' = 'some_group'

user

role

source_ip

default_wg

resource_group

'resource_group' = 'heavy_tasks'

INSERT OVERWRITE

default_mv_wg

mv_db

sql
    CREATE RESOURCE GROUP fast_queries
    TO (db = 'mv_db') -- All queries against tables/MVs in this DB use this RG
    WITH (...);

plan_cpu_cost_range

sql
    CREATE RESOURCE GROUP small_queries
    TO (plan_cpu_cost_range = '(0, 1000]')
    WITH (...);

sql
EXPLAIN VERBOSE <your_query>;

ResourceGroup

SCAN [your_mv_name]

sql
ALTER RESOURCE GROUP your_group_name SET (
    'concurrency_limit' = '20' -- Adjust based on your cluster size/CPU
);

sql
-- Set these as global variables
SET GLOBAL query_queue_concurrency_limit = 100;
SET GLOBAL query_queue_mem_used_pct_limit = 0.9; -- Queue if memory > 90%

IOTaskWaitTime

scanner_thread_pool

scanner_thread_pool_thread_num

max_hdfs_scanner_num

Query Queue

IOTaskWaitTime

IOTaskWaitTime

max_hdfs_scanner_num

pip_scan_com

exclusive_cpu_percent

exclusive_cpu_cores

pip_scan_<rg_id>

pip_con_scan_<rg_id>

pip_exec_<rg_id>

sql
ALTER RESOURCE GROUP high_priority_contextual SET (
    'cpu_weight' = '0',
    'cpu_weight_percent' = '0',
    'exclusive_cpu_cores' = '0',
    'exclusive_cpu_percent' = '50' -- Reserves 50% of CPU & dedicated scan threads
);

sql
    -- Run this to disable borrowing cluster-wide
    UPDATE information_schema.be_configs SET VALUE = "false" WHERE NAME = "enable_resource_group_cpu_borrowing";

sql
    SELECT * FROM information_schema.be_threads WHERE name LIKE '%pip_scan%';

pip_scan_<your_rg_id>

pip_scan_com

db='your_db'

app_user

etl_user

exclusive_cpu_cores

sql
CREATE RESOURCE GROUP app_link_group
TO (user='app_user')
WITH (
    'exclusive_cpu_percent' = '40', -- Reserves 40% of all BE cores exclusively
    'mem_limit' = '30%',
    'concurrency_limit' = '100'
);

cpu_weight

sql
CREATE RESOURCE GROUP etl_group
TO (user='etl_user')
WITH (
    'cpu_weight' = '10', -- Soft limit: weight-based sharing
    'mem_limit' = '40%',
    'concurrency_limit' = '10' -- Limit ETL concurrency to protect the app
);

sql
    TO (user='common_user', source_ip='10.0.1.0/24') -- Routes ETL traffic by IP

sql
    TO (plan_cpu_cost_range = '(10000.0, 99999999.0]') -- Routes heavy queries to ETL group

sql
-- In the ETL job session
SET resource_group = 'etl_group';
SELECT * FROM your_mv ...

exclusive_cpu_percent

pip_exec

pip_scan

pip_scan_com

ORDER BY

(col_a, col_b, col_c)

col_c

SELECT * FROM information_schema.be_metrics WHERE name LIKE '%cache_hit%';

ORDER BY

EXPLAIN ANALYZE

PushDownPredicates

RowsRead

(col_a, col_b, col_c)

col_c

user_id

event_time

region

category

user_id

event_time

ORDER BY

sql
CREATE MATERIALIZED VIEW mv_user_behavior
-- Partitioning by time allows for incremental refreshes and partition pruning
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 64
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
-- The Sort Key: Mandatory for <1s range/point lookups
ORDER BY (user_id, event_time)
AS 
SELECT user_id, event_time, region, category, revenue
FROM iceberg_catalog.db.raw_events;

region

category

sql
CREATE MATERIALIZED VIEW mv_regional_analysis
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(region) BUCKETS 64
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
-- New Sort Key for a different combination of columns
ORDER BY (region, category, event_time)
AS 
SELECT region, category, event_time, user_id, revenue
FROM iceberg_catalog.db.raw_events;

mv_regional_analysis

region

user_id

user_id = 150

revenue

category

high_revenue

sql
-- Speeds up "category = 'electronics'" if it's not the first sort column
CREATE INDEX idx_category ON mv_user_behavior(category) USING BITMAP;

-- For extremely high-cardinality IDs, use Bloom Filter
ALTER MATERIALIZED VIEW mv_user_behavior SET ("bloom_filter_columns" = "session_id");

ORDER BY

enable_storage_cache = true

OPTIMIZE

encode_sort_key

encode_sort_key

ORDER BY

sql
CREATE MATERIALIZED VIEW mv_multisort
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
-- Define a composite sort key
ORDER BY (composite_key)
AS
SELECT 
    *,
    -- Encodes 4 columns into one binary sortable key
    encode_sort_key(col1, col2, col3, col4) as composite_key
FROM iceberg_catalog.db.large_table;

encode_sort_key

state

category

gender

user_id

order_id

WHERE id = '...'

sql
-- Add indexes to a single "Master MV" instead of creating new ones
CREATE INDEX idx_cat ON mv_master(category) USING BITMAP;
ALTER MATERIALIZED VIEW mv_master SET ("bloom_filter_columns" = "user_id, device_id");

order_id

event_time

encode_sort_key

encode_sort_key

encode_sort_key

ORDER BY

>

<

MATCH

sql
-- Adding an inverted index to an existing MV
CREATE INDEX idx_col_1 ON my_mv(col_1) USING INVERTED;
CREATE INDEX idx_col_2 ON my_mv(col_2) USING INVERTED;

ORDER BY (a, b, c, d)

encode_sort_key

ORDER BY (time, category, user)

time

user

encode_sort_key(col1, col2, col3)

sql
CREATE MATERIALIZED VIEW mv_optimized
ORDER BY (composite_key)
AS SELECT 
  *, 
  encode_sort_key(col1, col2, col3, col4) as composite_key
FROM base_table;

city

status

order_id

user_id

ORDER BY

event_time

enable_storage_cache = true

sql
CREATE MATERIALIZED VIEW mv_optimized
ORDER BY (composite_key)
AS SELECT 
  *, 
  encode_sort_key(col1, col2, col3, col4) as composite_key
FROM base_table;

ORDER BY col3

encode_sort_key

encode_sort_key

encode_sort_key

encode_sort_key

ORDER BY (encode_sort_key(col1, col2, col3))

ORDER BY (col1, col2, col3)

col3

col1

col2

ORDER BY col3

col3

encode_sort_key

ORDER BY

sql
CREATE MATERIALIZED VIEW mv_optimized
-- Native multi-column sort key
ORDER BY (col1, col2, col3, col4)
AS SELECT col1, col2, col3, col4, revenue FROM base_table;

encode_sort_key

timestamp

tenant_id

ORDER BY

sql
CREATE MATERIALIZED VIEW mv_final
PARTITION BY ...
DISTRIBUTED BY ...
ORDER BY (primary_col) -- The most common filter/sort
AS SELECT col1, col2, col3, col4, col5, col6, col7, col8 FROM base_table;

-- Add inverted indexes to handle the other 7 "perspectives"
CREATE INDEX idx_col2 ON mv_final(col2) USING INVERTED;
CREATE INDEX idx_col3 ON mv_final(col3) USING INVERTED;
-- ... repeat for other columns

SELECT * FROM base_table WHERE col3 = 'XYZ' ORDER BY col3

col3

encode_sort_key

ORDER BY

ORDER BY

SELECT * FROM table WHERE col3 = 'A' ORDER BY col3

col3 = 'A'

ORDER BY ... LIMIT N

ORDER BY

ORDER BY

SHOW DATA

sql
    SHOW DATA;

sql
    SHOW DATA FROM database_name.mv_name;

Size

ReplicaCount

RowCount

information_schema

sql
SELECT 
    TABLE_SCHEMA AS database_name,
    TABLE_NAME AS mv_name,
    DATA_LENGTH AS size_bytes,
    format_bytes(DATA_LENGTH) AS human_readable_size,
    TABLE_ROWS AS row_count
FROM information_schema.tables 
WHERE TABLE_TYPE = 'VIEW' -- Note: ASYNC MVs are often listed as VIEW or BASE TABLE depending on version
   OR TABLE_NAME IN (SELECT TABLE_NAME FROM information_schema.materialized_views);

SHOW DATA

SHOW DATA

information_schema.tables

SHOW DATA

SHOW DATA

[db_name.]table_name

default_catalog

SHOW DATA

sql
SET CATALOG default_catalog;
SHOW DATA FROM starrocks_db.your_mv_name;

information_schema

sql
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    format_bytes(DATA_LENGTH) as size,
    TABLE_ROWS
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_db_name' 
  AND TABLE_NAME = 'your_mv_name';

SHOW DATA FROM default_catalog.starrocks

default_catalog

starrocks

SHOW DATA FROM default_catalog.starrocks.table_name

db.table

SHOW DATA

SHOW DATA;

SHOW DATA FROM db_name.table_name;

BIGINT

FLOAT

DOUBLE

BIGINT

INT

SMALLINT

ORDER BY

'parser'='none'

>=

<=

IN

==

BIGINT

INT

WHERE age > 20 AND status = 1 ORDER BY salary

FLOAT

DOUBLE

FLOAT

DOUBLE

ORDER BY

sql
    -- Base table is sorted by id
    CREATE MATERIALIZED VIEW mv_sort_by_price AS
    SELECT price, id, user_id
    FROM base_table
    ORDER BY price; -- This creates a second physical copy sorted by price

ORDER BY price

FLOAT

DECIMAL

DECIMAL

=

IN

USING GIN

TINYINT

SMALLINT

INT

BIGINT

LARGEINT

DECIMAL

BOOLEAN

sql
-- For an existing table
ALTER TABLE table_name ADD INDEX index_name (numerical_column) USING BITMAP;

-- At table creation
CREATE TABLE example (
    id BIGINT,
    data_value INT,
    INDEX idx_val (data_value) USING BITMAP
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id);

USING BITMAP

USING GIN

WHERE col = 100

WHERE col > 10 AND col < 50

WHERE col IN (1, 2, 3)

FLOAT

DOUBLE

DECIMAL

ORDER BY

ORDER BY

sql
    CREATE TABLE events (
        event_time DATETIME,
        tenant_id INT,
        severity INT,
        message STRING
    ) ENGINE=OLAP
    DUPLICATE KEY(event_time)
    ORDER BY (tenant_id, event_time, severity); -- Physical multi-column sort

timestamp

user_id

sql
    CREATE TABLE users (
        user_id BIGINT,
        city_id INT,
        last_login DATETIME
    ) PRIMARY KEY (user_id)
    ORDER BY (city_id, last_login);

(A, B)

(C, D)

ORDER BY

sql
CREATE MATERIALIZED VIEW sort_by_severity AS
SELECT severity, event_time, tenant_id
FROM events
ORDER BY severity, event_time; -- This creates a 2nd physical sort order

INT

BIGINT

DECIMAL

sql
ALTER TABLE events ADD INDEX idx_severity (severity) USING BITMAP;

encode_sort_key

encode_sort_key()

VARBINARY

ORDER BY (col1, col2...)

ORDER BY

SELECT * FROM table ORDER BY A, B, C

sql
-- Original table sorted by (id)
CREATE TABLE base_data ( ... ) 
ENGINE=OLAP DUPLICATE KEY(id) 
ORDER BY (id);

-- Support "Sort by Metric A, Metric B" instantly
CREATE MATERIALIZED VIEW mv_sort_metrics AS
SELECT metric_a, metric_b, id, user_id
FROM base_data
ORDER BY metric_a, metric_b;

encode_sort_key

encode_sort_key()

VARBINARY

sql
CREATE TABLE high_scale_data (
    col1 BIGINT,
    col2 FLOAT,
    composite_key VARBINARY(128) AS (encode_sort_key(col1, col2))
) ENGINE=OLAP 
ORDER BY (composite_key);

query_mem_limit

spill_local_storage_dir

sql
    SET enable_spill = true;
    SET spill_mode = 'auto';

ORDER BY ... LIMIT 1000

(BE_count * 1000)

set enable_sort_aggregate=true;

ORDER BY

USING GIN

WHERE col MATCH 'token'

WHERE col MATCH_ANY 'token1 token2'

word -> row_id

WHERE col = 'exact_token_value'

WHERE col IN ('token1', 'token2')

"error_token_404"

WHERE col = 'token'

WHERE col LIKE '%token%'

LIKE

INDEX idx (col) USING GIN

PROPERTIES("bloom_filter_columns"="col")

INDEX idx (col) USING NGRAMBF

user_id

order_id

5

WHERE category = 'Movies'

WHERE category_id = 5

ORDER BY

WHERE category = 'Movies' AND status = 'Released'

ORDER BY

0:OlapScanNode
     TABLE: mv_2
     PREAGGREGATION: ON
"     PREDICATES: 149: category = 'Technology & Computing'"
     partitions=1/1
     rollup: mv_2
     tabletRatio=2048/2048
     tabletList=3402037,3402038,3402039,3402040,3402041,3402042,3402043,3402044,3402045,3402046 ...
     cardinality=1684737
     avgRowSize=211.50655
     MaterializedView: true

tabletRatio=2048/2048

tabletRatio=2048/2048

category = '...'

1/2048

category

ORDER BY

category

category

ORDER BY

sql
CREATE MATERIALIZED VIEW mv_2 
REFRESH ASYNC
DISTRIBUTED BY HASH(...) -- keep your existing distribution
ORDER BY (category, ...) -- PUT CATEGORY FIRST
AS 
SELECT category, ... 
FROM base_table;

tabletRatio

2048/2048

sql
-- Run this on the Materialized View (or the base table if it's a sync MV)
ALTER TABLE mv_2 ADD INDEX idx_category (category) USING BITMAP;

category

DISTRIBUTED BY HASH(some_other_id) BUCKETS 2048

DISTRIBUTED BY HASH(category) BUCKETS 2048

tabletRatio

2048/2048

1/2048

category

category

NodeCount * 10-20

SET enable_profile = true;

SHOW PROFILE FOR <query_id>;

PushdownPredicates

RowsRead

RawRowsRead

RawRowsRead

mv_2

SHOW INDEX

sql
SHOW INDEX FROM mv_2;

SHOW CREATE TABLE

sql
SHOW CREATE TABLE mv_2;

ORDER BY

INDEX

INDEX

PROPERTIES("bloom_filter_columns"...)

DESC ALL

sql
DESC base_table_name ALL;

EXPLAIN

tabletRatio=2048/2048

SHOW INDEX

category

ORDER BY

SHOW CREATE TABLE

sql
    ALTER TABLE mv_2 ADD INDEX idx_category (category) USING BITMAP;

CACHE SELECT

CACHE SELECT

sql
    CACHE SELECT * FROM iceberg_catalog.your_db.your_table;

sql
    CACHE SELECT column1, column2
    FROM iceberg_catalog.your_db.your_table
    WHERE event_date >= '2024-01-01';

sql
SUBMIT TASK warmup_iceberg_task 
SCHEDULE EVERY (INTERVAL 1 DAY)
AS 
CACHE SELECT * FROM iceberg_catalog.your_db.your_table;

be.conf

starlet_use_star_cache = true

enable_iceberg_metadata_disk_cache = true

fe.conf

CACHE SELECT

CACHE SELECT

Segment V2

r8g.8xlarge

r8g.4xlarge

r8g.4xlarge

r8g.8xlarge

r8g.4xlarge

r8g.8xlarge

16xlarge

r8g

r8g.4xlarge

exclusive_cpu_cores

sql
CREATE RESOURCE GROUP reserved_20_group
PROPERTIES (
    "exclusive_cpu_cores" = "3", -- 20% of a 16-core node
    "mem_limit" = "20%"          -- Memory is usually set as a %
);

cpu_weight

cpu_core_limit

group_weight / total_weights_of_all_active_groups

sql
CREATE RESOURCE GROUP flexible_20_group
PROPERTIES (
    "cpu_weight" = "20", 
    "mem_limit" = "20%"
);

-- Ensure other groups have weights that sum to 80
-- e.g., Altering the default group
ALTER RESOURCE GROUP default_rg 
PROPERTIES (
    "cpu_weight" = "80"
);

exclusive_cpu_cores

cpu_weight

mem_limit

"0.2"

"20%"

cpu_weight

cpu_weight should range from 0 to 14

cpu_weight

default_rg

default_rg

sql
-- 1. Update your group to a valid weight (e.g., 2)
ALTER RESOURCE GROUP rg PROPERTIES (
    "cpu_weight" = "2",
    "mem_limit" = "20%"
);

-- 2. Update the default group to 8 to ensure the 20/80 ratio
ALTER RESOURCE GROUP default_rg PROPERTIES (
    "cpu_weight" = "8"
);

sql
ALTER RESOURCE GROUP rg PROPERTIES (
    "cpu_weight" = "2.8",
    "mem_limit" = "20%"
);

cpu_weight

exclusive_cpu_cores

exclusive_cpu_cores

3

exclusive_cpu_cores

min_be_cpu_cores - 1

PROPERTIES

sql
ALTER RESOURCE GROUP rg PROPERTIES (
    "cpu_weight" = "2",
    "mem_limit" = "20%"
);

ALTER RESOURCE GROUP ... ADD

sql
ALTER RESOURCE GROUP your_group_name 
ADD 
    (user='alice'),
    (user='bob'),
    (user='charlie');

sql
ALTER RESOURCE GROUP your_group_name 
ADD (role='analytics_team');

sql
SHOW RESOURCE GROUP your_group_name;

SHOW

sql
-- Replace 12345 with the actual Classifier ID found in SHOW command
ALTER RESOURCE GROUP your_group_name DROP (12345);

SET resource_group = 'group_name';

enable_datacache_sharing = true

enable_trace_historical_node = true

sql
    -- Manually trigger a cache warmup for the MV
    CACHE SELECT * FROM your_mv_name;

lake_enable_balance_tablets_between_workers

true

stop_cn.sh -g

stop_cn.sh -g

sql
    SET GLOBAL enable_datacache_sharing = true;
    -- Also requires FE config: enable_trace_historical_node = true

sql
-- This forces the responsible nodes to pull the data from S3 into their EBS cache
CACHE SELECT * FROM your_important_mv;

replication_num