Hints - Kinetica Docs

Hints can be added as comments within queries, and affect just the query in which they appear. They will override the corresponding client & server settings (when such settings exist). Kinetica supports both global & scoped hints:

Scoped Hints - affect a portion of the query
Global Hints - affect the entire query

Scoped Hints

Scoped hints may appear anywhere in the query. Scoped hints must be in a comment block in the form:

/*+ <HINT>[,<HINT>...] */

Scoped Hint Example

CREATE MATERIALIZED VIEW example.weather_zone AS
SELECT w.name AS event_name, w.type AS event_type, gz.name AS zone
FROM example.weather /*+ KI_HINT_DELTA_TABLE */ w
JOIN example.geo_zone gz ON STXY_INTERSECTS(lon, lat, zone)

KI_HINT_DELTA_TABLE

Designates the corresponding table as a delta table, within the context of a CREATE MATERIALIZED VIEW statement.This hint must be placed immediately after the FROM <table> clause to which it applies, but before any table alias specified for that table.

KI_HINT_GROUP_BY_PK

Create primary keys for this GROUP BY result set on the grouped-upon columns/expressions; often used to create a primary key on the result of a CREATE TABLE … AS that ends in a GROUP BY, and can also make materialized views containing grouping operations more performant.

If any of the grouped-on expressions are nullable, no primary key will be applied.

This hint may be placed either immediately after the SELECT keyword or immediately after the FROM <table> clause.

KI_HINT_MATERIALIZE

This can make some operations more performant, but can use more memory. It would normally be used on a multi-dimensional join or union result set to speed up operations done on that result set. This can also be used on a query with a CTE in the WITH clause; if different WHERE clauses are applied to the CTE, the CTE query may only have to be evaluated once.This hint must be placed immediately after the SELECT keyword, like the SELECT producing a join.

KI_HINT_MATERIALIZE includes the functionality of KI_HINT_NO_VIRTUAL_UNION.

KI_HINT_NO_VIRTUAL_UNION

This can make some operations more performant, but can use more memory. This would normally be used on a union query combining many tables or sub-queries.This scoped must be placed immediately after the SELECT keyword, like the first SELECT keyword in the union.

Global Hints

Global hints may appear anywhere in the query. Global hints must be in a comment block in the form:

/* <HINT>[,<HINT>...] */

Global Hint Example

CREATE TABLE example.taxi_trip_daily_totals AS
/* KI_HINT_GROUP_BY_PK, KI_HINT_INDEX(transaction_date) */
SELECT vendor_id, DATE(dropoff_datetime) AS transaction_date, COUNT(*) AS total_trips
FROM demo.nyctaxi
GROUP BY vendor_id, transaction_date

KI_HINT_BATCH_SIZE(n)

Use an ingest batch size of n records. Default: 10,000.Only applicable when issuing INSERT statements.

KI_HINT_CHUNK_SIZE(n)

Use chunk sizes of n records per chunk within result sets. Suffixes of K & M can be used to represent thousands or millions of records; e.g., 20K, 50M.

KI_HINT_COST_BASED_OPTIMIZATION

Cost-based optimizations are usually turned on by default. If they have been turned off by default using a global configuration setting (sql.cost_based_optimization), they can be turned on for an individual query using this hint.

KI_HINT_DICT_PROJECTION

Retain the dictionary encoding attributes of source columns in a projection result set.

KI_HINT_DISTRIBUTED_OPERATIONS

Reshard data when doing so would be the only way to process one or more operations in this query.

KI_HINT_DONT_COMBINE

Don’t combine joins and unions for this query.

KI_HINT_GROUP_BY_FORCE_REPLICATED

Make all result tables within a single query replicated; useful when meeting the input table requirements of JOIN, UNION, etc. in a query containing aggregated subqueries which generate differently-sharded result tables.

KI_HINT_GROUP_BY_PK

Create primary keys for all GROUP BY result sets on the grouped-upon columns/expressions within a given query; often used to create a primary key on the result of a CREATE TABLE … AS that ends in a GROUP BY, and can also make materialized views containing grouping operations more performant.

If any of the grouped-on expressions are nullable, no primary key will be applied.

KI_HINT_HAS_HEADER

Assume the first line of the source CSV file has a header row. Only used with CSV ingestion via INSERT INTO … SELECT … FROM FILE.

KI_HINT_IGNORE_EXISTING_PK

When inserting into or updating a table with a primary key, if the INSERT or UPDATE results in a primary key collision, reject the command with no error or warning. If the specified table does not have a primary key or the KI_HINT_UPDATE_ON_EXISTING_PK hint is used to switch to upsert mode for inserts or overwrite mode for updates, then this hint is ignored.

KI_HINT_INDEX(column_list)

Create an index on each of the comma-separated columns in the given column_list; often used with CREATE TABLE … AS to create an index on a persisted result set.

KI_HINT_JOIN_TABLE_CHUNK_SIZE(n)

Use chunk sizes of n records per chunk within joins. Suffixes of K & M can be used to represent thousands or millions of records; e.g., 20K, 50M.

KI_HINT_KEEP_TEMP_TABLES

Don’t erase temp tables created by this query.

KI_HINT_KEY_LOOKUP

Use distributed key lookup to retrieve records from a single table using a fast, indexed lookup.See SQL for details and examples.

KI_HINT_MAX_CONCURRENCY(n)

Run this query with the specified n concurrency, usually lower than the system or user group limit in order to avoid resource exhaustion.

KI_HINT_NO_COST_BASED_OPTIMIZATION

Don’t use the cost-based optimizer when calculating the query plan.

KI_HINT_NO_DICT_PROJECTION

Don’t retain dictionary encoding attributes of source columns in a projection result set.

KI_HINT_NO_DISTRIBUTED_OPERATIONS

Don’t reshard data when doing so would be the only way to process one or more operations in this query.

KI_HINT_NO_HEADER

Assume the source CSV file has no header row. Only used with CSV ingestion via INSERT INTO … SELECT … FROM FILE.

KI_HINT_NO_JOIN_COUNT

Optimize joins by not calculating intermediate set counts.

KI_HINT_NO_LATE_MATERIALIZATION

Force the materialization of intermediary result sets.

KI_HINT_NO_NATIVE_SEMI_JOINS

Don’t use semi-joins.

KI_HINT_NO_PARALLEL_EXECUTION

Execute all components of this query in series.

KI_HINT_NO_PLAN_CACHE

Don’t cache the query plan calculated for this query.

KI_HINT_NO_QUERY_RESULT_CACHING

Don’t use the query cache for processing this query.

KI_HINT_NO_RULE_BASED_OPTIMIZATION

Don’t use the rule-based optimizer when calculating the query plan.

KI_HINT_NO_SHADOW_CUBE

Don’t use the shadow cube for caching requests in the worker processes.

KI_HINT_NO_SSQ_OPTIMIZATION

Don’t optimize the query using Scalar Sub-Query (SSQ) substitution.

KI_HINT_NO_VALIDATE_CHANGE

Don’t fail an ALTER TABLE command when changing column types/sizes and the column data is too long/large. Truncate the data instead and allow the modification to succeed.

KI_HINT_NO_VIRTUAL_UNION

Don’t utilize Virtual Unions when performing this query.

KI_HINT_PROJECT_MATERIALIZED_VIEW

Force the materialization of a materialized view. Some materialized views containing JOIN clauses will be backed by a native join view. This is done to improve the performance of materialized view refreshes and reduce memory usage at the cost of reduced query performance. This hint will induce the reverse of this trade-off — increased query performance at the cost of reduced refresh performance and increased memory usage.

KI_HINT_REPL_ASYNC

Instruct the target database to treat this statement as one that should be run asynchronously and serially across all high availability clusters in its ring. See HA Asynchronous Operation Handling for details.