Wide table engine data

Primary key queries

Primary key queries filter data by primary key columns, supporting exact-match lookups on the full primary key and range scans on a primary key prefix. The wide table engine is natively optimized for these queries, enabling millisecond-level lookups.

-- Create a table.
CREATE TABLE orders (
  user_id     VARCHAR NOT NULL,
  order_id    VARCHAR NOT NULL,
  amount      DOUBLE,
  status      VARCHAR,
  region      VARCHAR,
  create_time VARCHAR,
  PRIMARY KEY(user_id, order_id)
);

-- Exact-match lookup on the full primary key.
SELECT * FROM orders WHERE user_id = 'u001' AND order_id = 'o10001';

-- Range scan on a primary key prefix.
SELECT * FROM orders WHERE user_id = 'u001' AND order_id >= 'o10001';

-- Partial primary key scan.
SELECT * FROM orders WHERE user_id >= 'u001';

Non-primary key queries

Non-primary key queries filter data on non-primary key columns and perform a full table scan by default. You can create a secondary index or a search index based on the predicate type. The OLAP resource group automatically uses the index to accelerate the query without any changes to your SQL.

-- Create a table.
CREATE TABLE orders (
  user_id     VARCHAR NOT NULL,
  order_id    VARCHAR NOT NULL,
  amount      DOUBLE,
  status      VARCHAR,
  region      VARCHAR,
  create_time VARCHAR,
  PRIMARY KEY(user_id, order_id)
);

Non-primary key query with a secondary index

A secondary index is suited for structured filtering scenarios such as equality, range, and pattern matching queries.

-- Create a secondary index on the 'status' column.
-- Cover the 'amount' and 'region' columns to avoid table access.
CREATE INDEX idx_status ON orders(status) INCLUDE(amount, region);

-- Equality filter (hits the secondary index).
SELECT user_id, order_id, amount, region FROM orders WHERE status = 'SHIPPED';

-- Range filter (hits the secondary index).
SELECT user_id, order_id, amount FROM orders WHERE status >= 'S' AND status < 'T';

-- Pattern matching (hits the secondary index).
SELECT user_id, order_id, amount, region FROM orders WHERE status LIKE 'SH%';

Non-primary key query with a search index

A search index is suited for exact matches on string type fields, as well as for full-text search (MATCH) on text type fields that use tokenization.

-- Create a search index on the 'region' column (string type, supports equality and LIKE).
CREATE INDEX idx_search USING SEARCH ON orders(region(type=string, indexed=true));

-- Equality filter (hits the search index).
SELECT * FROM orders WHERE region = 'Beijing';

-- LIKE filter (hits the search index, supported for string type).
SELECT * FROM orders WHERE region LIKE 'Bei%';

-- MATCH full-text search (hits the search index).
SELECT * FROM orders WHERE region MATCH 'Beijing Shanghai';

Full-text search

After you create a search index on text fields in a wide table, you can use the MATCH syntax for full-text search on both regular and JSON fields. The OLAP resource group automatically pushes search predicates down to the inverted index for efficient retrieval.

The MATCH expression supports the following matching rules:

-- Create a table with text and JSON fields.
CREATE TABLE orders (
  user_id     VARCHAR NOT NULL,
  order_id    VARCHAR NOT NULL,
  amount      DOUBLE,
  status      VARCHAR,
  region      VARCHAR,
  create_time VARCHAR,
  user_json   VARCHAR,
  PRIMARY KEY(user_id, order_id)
);

-- Create a search index on the 'region' column (string type for exact matching and LIKE).
-- Enable Chinese tokenization and full-text search on the 'user_json' column.
CREATE INDEX search_idx USING SEARCH ON orders(
  region(type=string, indexed=true),
  user_json(type=text, analyzer=ik, indexed=true)
);

-- Contains 'Beijing' or 'Shanghai'.
SELECT * FROM orders WHERE region MATCH 'Beijing Shanghai';

-- Must contain 'Beijing' and must not contain 'Shanghai'.
SELECT * FROM orders WHERE region MATCH '+Beijing -Shanghai';

-- Contains the exact phrase 'Beijing Chaoyang'.
SELECT * FROM orders WHERE region MATCH '"Beijing Chaoyang"';

-- Must contain 'Beijing', and must also contain either 'Chaoyang' or 'Haidian'.
SELECT * FROM orders WHERE region MATCH '+Beijing +(Chaoyang Haidian)';

-- The JSON field contains 'Beijing' (use MATCH directly on the column; the search index covers the entire column content).
SELECT * FROM orders WHERE user_json MATCH 'Beijing';

Complex analytics

OLAP resource groups support complex analytical operations on wide table data, such as large-scale aggregations, multi-table joins, and window functions. After you create a columnar index for a wide table, analytical queries read from columnar storage, which significantly reduces I/O. The OLAP resource group automatically routes queries to the index without any changes to your SQL.

-- Create a table with 'region' as a primary key prefix for easy partitioning.
CREATE TABLE orders (
  region      VARCHAR NOT NULL,
  user_id     VARCHAR NOT NULL,
  order_id    VARCHAR NOT NULL,
  amount      DOUBLE,
  status      VARCHAR,
  create_time VARCHAR,
  PRIMARY KEY(region, user_id, order_id)
);

-- Create a columnar index, using 'region' for standard partitioning and hash-bucketing 'user_id' (128 buckets).
-- Note: Columns in PARTITION BY ENUMERABLE must be primary key columns.
CREATE INDEX columnar_idx
USING COLUMNAR
ON orders(*)
PARTITION BY ENUMERABLE (
    region,
    bucket(128, user_id)
)
WITH (
    `lindorm_columnar.user.index.database` = 'mydb',
    `lindorm_columnar.user.index.table` = 'orders'
);

-- Large-scale aggregate analysis (hits the columnar index).
SELECT
    region,
    COUNT(*)       AS order_cnt,
    SUM(amount)    AS gmv,
    AVG(amount)    AS aov
FROM orders
WHERE region IN ('Shanghai', 'Beijing')
GROUP BY region
ORDER BY gmv DESC;

-- UNION query
(SELECT user_id, order_id, amount, status, region FROM orders WHERE region = 'Beijing')
UNION ALL
(SELECT user_id, order_id, amount, status, region FROM orders WHERE status LIKE 'SH%');

Vector retrieval

If a vector index has been created for a wide table, you can use an OLAP resource group to retrieve data based on vector similarity.

Query format

SELECT /*+ _l_force_vector_index_(${PROPERTIES}) */ ${SELECT_LIST}[,_vector_score_(${EMBEDDING_COLUMN}, ${TARGET_VECTOR})]
FROM ${TABLE_NAME}
WHERE ${CONDITION}
ORDER BY ${DISTANCE_FUNCTION}(${EMBEDDING_COLUMN}, ${TARGET_VECTOR})
LIMIT ${K}

Syntax notes

1. Variables:

   Parameter	Description
   ${SELECT_LIST}	The columns to return from the query.
   _vector_score_(${EMBEDDING_COLUMN}, ${TARGET_VECTOR})	Returns the similarity score. The EMBEDDING_COLUMN and TARGET_VECTOR parameters must match the parameters used by the distance function.
   ${TABLE_NAME}	The target table.
   ${CONDITION}	The scalar filter conditions.
   ${DISTANCE_FUNCTION}	The similarity distance function. Supported functions are l2_distance, cosine_similarity, and innerproduct_distance.
   ${EMBEDDING_COLUMN}	The column on which the vector index is built.
   ${TARGET_VECTOR}	The target vector, expressed as a string representation of a floating-point array.
   ${PROPERTIES}	Parameters for vector retrieval, such as k=10 and lvector.min_score=0.

2. Distance functions: l2_distance, cosine_similarity, and innerproduct_distance.

3. Hint: Use /*+ _l_force_vector_index_(...) */ to query the vector index and configure vector query parameters. The following parameters are supported:

   Parameter	Required	Description
   k	Yes	The number of most similar results to return. Ensure that the k parameter is consistent with the LIMIT clause.
   lvector.min_score	No	The similarity threshold. The scores of returned vectors must be greater than this value. The scores of returned vectors are in the range of [0, 1]. The value range is [0, +inf]. The default value is 0.
   lvector.ef_search	No	Specifies the length of the dynamic list during an index query for the HNSW algorithm. Value range: [1, 1000]. The default value is 100.
   lvector.nprobe	No	The number of cluster units to probe. Adjust this value to meet your recall requirements. A larger value improves recall but reduces performance. Valid values: [1, method.parameters.nlist]. No default value. Important: This parameter applies only to the IVFPQ algorithm.
   lvector.reorder_factor	No	Performs reordering by using the original vectors. The distances calculated by the ivfpq algorithm are quantized, which results in some precision loss. Reordering with the original vectors is used to compensate for this. The number of vectors to reorder is k * reorder_factor. This process is typically used to improve recall precision but increases performance overhead. Value range: [1, 200]. The default value is 10. Important - This parameter applies only to the ivfpq algorithm. - If the value of k is small, set this parameter to 5. If k is greater than 100, set this parameter to 1.
   lvector.client_refactor	No	Specifies whether to skip re-ranking within each shard and instead re-rank at an upper layer to improve performance. Valid values are true (yes) and false (no, default).
   lvector.filter_type	No	The hybrid search mode. Valid values: - pre_filter: Filters structured data and then searches vector data. - post_filter: Searches vector data and then filters structured data.

Example wide table schema

-- The following wide table already exists, and a vector index has been created on the 'item_embedding' column.
+----------------+---------------------+------+-------+---------+---------------+---------+
| Field          | Type                | Null | Key   | Default | Extra         | Comment |
+----------------+---------------------+------+-------+---------+---------------+---------+
| user_id        | VARCHAR(1073741824) | No   | true  | NULL    | partition key |         |
| order_id       | VARCHAR(1073741824) | No   | true  | NULL    | partition key |         |
| amount         | DOUBLE              | Yes  | false | NULL    |               |         |
| status         | VARCHAR(1073741824) | Yes  | false | NULL    |               |         |
| region         | VARCHAR(1073741824) | Yes  | false | NULL    |               |         |
| create_time    | VARCHAR(1073741824) | Yes  | false | NULL    |               |         |
| item_embedding | VARCHAR(1073741824) | Yes  | false | NULL    |               |         |
+----------------+---------------------+------+-------+---------+---------------+---------+

Query data

-- Retrieve the top 10 most similar product orders based on the input feature vector.
SELECT /*+ _l_force_vector_index_(k=10, lvector.min_score=0) */
    user_id,
    order_id
FROM orders
WHERE region='Beijing'
ORDER BY l2_distance(item_embedding, '[0.077, 0.941, 0.917, 0.347, 0.331, 0.309, 0.127, 0.990]')
LIMIT 10;

-- Retrieve the top 10 most similar product orders based on the input feature vector and return their similarity scores.
SELECT /*+ _l_force_vector_index_(k=10, lvector.min_score=0) */
    user_id,
    order_id,
    _vector_score_(item_embedding, '[0.077, 0.941, 0.917, 0.347, 0.331, 0.309, 0.127, 0.990]')
FROM orders
WHERE region='Beijing'
ORDER BY l2_distance(item_embedding, '[0.077, 0.941, 0.917, 0.347, 0.331, 0.309, 0.127, 0.990]')
LIMIT 10;