<a id="flink-sql-hints"></a>

# SQL Hints in Confluent Cloud for Apache Flink

Confluent Cloud for Apache Flink® supports SQL *hints*, which enable you to influence query
behavior without changing the query logic. Hints can override table options,
control state management, and optimize join operations.

## Dynamic table options

Dynamic table options enable you to specify or override
[table options](../statements/create-table.md#flink-sql-with-options) on a per-query basis.

### Syntax

To use dynamic table options, employ the following Oracle-style SQL hint
syntax:

```sql
table_path /*+ OPTIONS(key=val [, key=val]*) */

key:
    stringLiteral
val:
    stringLiteral
```

The dynamic options must be placed next to the table and not by any aliases,
for example:

```sql
SELECT * FROM t /*+ OPTIONS(...) */ AS alias;
```

### Description

Dynamic table options offer the following benefits:

- **Flexible configuration:** Specify table options on a per-statement basis,
  providing more flexibility than static options as stored in the table
  definition.
- **Query-specific adjustments:** Customize table behavior for individual
  queries without altering the permanent table definition.

### Examples

Here are some examples of using dynamic table options:

- Override
  [scan startup mode](../statements/create-table.md#flink-sql-create-table-with-scan-startup-mode)
  for a table:
  ```sql
  SELECT id, name
  FROM table /*+ OPTIONS('scan.startup.mode'='earliest-offset') */;
  ```
- Set options for multiple tables in a [join](joins.md#flink-sql-joins):
  ```sql
  SELECT *
  FROM table1 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t1
  JOIN table2 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t2
  ON t1.id = t2.id;
  ```
- Set the scan startup mode to use the latest offset:
  ```sql
  SELECT *
  FROM orders /*+ OPTIONS('scan.startup.mode'='latest-offset') */;
  ```
- Set the scan startup mode to use the specific offsets, for example, using the latest_offsets attribute from a previous statement:
  ```sql
  INSERT INTO customers_sink (customer_id, name, address, postcode, city, email)
      SELECT customer_id, name, address, postcode, city, email
      FROM customers_source
      /*+ OPTIONS(
          'scan.startup.mode' = 'specific-offsets',
          'scan.startup.specific-offsets'  = 'partition:0,offset:10;partition:1,offset:123'
      ) */;

  // Note: for a statement with multiple topics, use OPTIONS for each table
  SELECT *
  FROM table1 /*+ OPTIONS('scan.startup.mode'='specific-offsets', 'scan.startup.specific-offsets' = '...') */ t1
  JOIN table2 /*+ OPTIONS('scan.startup.mode'='specific-offsets', 'scan.startup.specific-offsets' = '...') */ t2
  ON t1.id = t2.id;
  ```

<a id="flink-sql-state-ttl-hints"></a>

## State TTL hints

For stateful computations such as Regular Joins and Group Aggregations, Confluent Cloud for Apache Flink
supports the STATE_TTL hint. This hint allows you to specify operator-level
Idle State Retention Time, enabling these operators to have a different TTL
from the pipeline-level configuration set by sql.state-ttl.

### Syntax

The syntax for using State TTL hints is as follows:

```sql
table_path /*+ STATE_TTL('table_name_or_alias'='ttl_value') */

ttl_value:
    stringLiteral (e.g., '6h', '2d', '10800s')
```

### Examples

Here are some examples of using State TTL hints in Confluent Cloud for Apache Flink for social media analytics:

- Set State TTL for a Regular Join of posts and users:
  ```sql
  SELECT /*+ STATE_TTL('posts'='6h', 'users'='2d') */ *
  FROM posts
  JOIN users ON posts.user_id = users.id;
  ```
- Use table aliases with State TTL hints for analyzing engagement:
  ```sql
  SELECT /*+ STATE_TTL('p'='4h', 'e'='12h') */ *
  FROM posts p
  JOIN engagement e ON p.post_id = e.post_id;
  ```
- Apply State TTL hints in a Group Aggregation for trending hashtags:
  ```sql
  SELECT /*+ STATE_TTL('hashtags' = '1h') */
         hashtag, COUNT(*) AS usage_count
  FROM hashtags
  GROUP BY hashtag;
  ```

### Important considerations

When using State TTL hints, keep the following in mind:

- You can use either the table name or table alias as the hint key.
- If you specify an alias for a table, you must use that alias in the STATE_TTL hint.
- For queries with multiple joins, the specified TTLs are applied in a bottom-up order.
- The STATE_TTL hint only affects the query block where it’s applied.
- If a hint key is duplicated, the last occurrence takes precedence.
- When multiple STATE_TTL hints are used with the same hint key, the first occurrence is applied.

## Multi-way join hints

For queries joining three or more tables on a common key, Confluent Cloud for Apache Flink supports
the `MULTI_JOIN` hint. This hint enables the optimizer to use a multi-way
join operator that reduces state by eliminating intermediate join results.

For more information, see [Multi-way join optimization](joins.md#flink-sql-multi-way-joins).

### Syntax

The syntax for using the Multi-way Join hint is:

```sql
SELECT /*+ MULTI_JOIN(table1, table2, table3 [, ...]) */ ...
```

### Examples

- Enable multi-way join for a three-table join:
  ```sql
  SELECT /*+ MULTI_JOIN(o, c, a) */ *
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  JOIN addresses a ON c.id = a.customer_id;
  ```

### Requirements

The optimizer applies multi-way join optimization when:

- Three or more tables are being joined
- All joins share a common join key (for co-location)
- Only `INNER JOIN` and `LEFT OUTER JOIN` are used

If requirements are not met, the query silently falls back to binary joins.

## Related content

- [Join Queries](joins.md#flink-sql-joins)
- [CREATE TABLE](../statements/create-table.md#flink-sql-create-table)
- [ALTER TABLE](../statements/alter-table.md#flink-sql-alter-table)
- [Table Options](../statements/create-table.md#flink-sql-with-options)

#### NOTE
This website includes content developed at the [Apache Software Foundation](https://www.apache.org/)
under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).