> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.
# Query Directives
> Directives allow you to influence the query planner's behavior and optimize query execution performance.
Query directives provide fine-grained control over how Ditto's query planner executes your DQL statements. You can use directives to force specific index usage, control index scan behavior, and tune query performance.
## Directive Syntax
Directives can be specified in three ways:
### 1. Comment-Style Directives
Include directives as a special comment at the beginning of your query:
```sql DQL theme={null}
/*+ {"#prefer_order": true, "#max_scans": 10} */
SELECT * FROM cars WHERE color = 'blue' ORDER BY year
```
```sql DQL theme={null}
--+ {"#prefer_order": true}
SELECT * FROM cars WHERE color = 'blue' ORDER BY year
```
### 2. USE DIRECTIVES Clause
Specify collection-level directives using the `USE DIRECTIVES` clause:
```sql DQL theme={null}
SELECT * FROM cars USE DIRECTIVES '{"#index": ["ix_color", "ix_year"]}'
WHERE color = 'blue'
```
### 3. USE INDEX Clause
A shorthand for specifying index directives:
```sql DQL theme={null}
SELECT * FROM cars USE INDEX 'ix_color'
WHERE color = 'blue'
```
## Directive Scope
Directives can be **global** (affecting the entire statement) or **per-collection** (affecting a specific collection instance):
* **Comment-style directives** can specify both global and per-collection directives
* **USE DIRECTIVES** can only specify collection-level directives
* **USE INDEX** can only specify the index directive for a collection
## Global Directives
Global directives affect the entire query statement.
### #disable\_index\_scan
**Type**: `boolean` (default: `false`)
Prevents the planner from considering index scans for all collections in the statement.
```sql DQL theme={null}
/*+ {"#disable_index_scan": true} */
SELECT * FROM cars WHERE color = 'blue'
```
### #prefer\_order
**Type**: `boolean` (default: `false`)
Indicates whether ordering should be prioritized when considering indices. Useful when you want the planner to prefer indices that can satisfy both filtering and ordering requirements.
```sql DQL theme={null}
/*+ {"#prefer_order": true} */
SELECT * FROM cars WHERE color = 'blue' ORDER BY year DESC
```
### #intersects
**Type**: `integer` (default: `0`)
Indicates the maximum number of indices to consider when generating an intersect scan (combining multiple index scans with AND logic).
```sql DQL theme={null}
/*+ {"#intersects": 3} */
SELECT * FROM cars WHERE color = 'blue' AND year > 2020 AND mileage < 50000
```
### #max\_scans
**Type**: `integer` (default: `5`)
The overall maximum number of index scans permitted when planning a statement.
```sql DQL theme={null}
/*+ {"#max_scans": 10} */
SELECT * FROM cars WHERE color IN ('blue', 'red', 'green')
```
### #max\_inlist
**Type**: `integer` (default varies)
Controls the maximum number of items in an `IN` list that will be pushed down to index scans.
```sql DQL theme={null}
/*+ {"#max_inlist": 50} */
SELECT * FROM cars WHERE color IN ('blue', 'red', 'green', 'black', 'white')
```
### #max\_serial\_ranges
**Type**: `integer` (default varies)
Controls the maximum number of distinct value ranges for a single index that will be scanned serially.
```sql DQL theme={null}
/*+ {"#max_serial_ranges": 20} */
SELECT * FROM cars WHERE year BETWEEN 2010 AND 2020
```
### #profile
**Type**: `boolean` (default: `false`)
Directs the Query engine to append the `system:completed_requests` entry to the result set as the final result. Only works for `SELECT` statements.
```sql DQL theme={null}
/*+ {"#profile": true} */
SELECT * FROM cars WHERE color = 'blue'
```
Using `#profile` is equivalent to using the [PROFILE](/dql/profile) statement.
### #disable\_special
**Type**: `boolean` (default: `false`)
Disables special-case query optimizations such as aggregate short-circuits.
This is an internal directive intended for diagnostic use. Only use this directive when advised by Ditto support.
```sql DQL theme={null}
/*+ {"#disable_special": true} */
SELECT COUNT(*) FROM cars
```
### #disable\_intersect\_eo
**Type**: `boolean` (default: `false`)
Disables intersect early-out optimization, forcing the planner to evaluate all intersect scan children.
This is an internal directive intended for diagnostic use. Only use this directive when advised by Ditto support.
```sql DQL theme={null}
/*+ {"#disable_intersect_eo": true} */
SELECT * FROM cars WHERE color = 'blue' AND year > 2020
```
### #reprepare
**Type**: `boolean` (default: `false`)
Disables caching of the statement plan in the shared statement cache, forcing the statement to always be prepared.
```sql DQL theme={null}
/*+ {"#reprepare": true} */
SELECT * FROM cars WHERE color = 'blue'
```
## Per-Collection Directives
Per-collection directives apply to a specific collection instance in your query. In comment-style directives, use the collection alias as the field name.
### #prefer\_order (per-collection)
Same as the global directive but overrides the value for a specific collection.
```sql DQL theme={null}
/*+ {"cars": {"#prefer_order": true}} */
SELECT * FROM cars WHERE color = 'blue' ORDER BY year
```
### #intersects (per-collection)
Same as the global directive but overrides the value for a specific collection.
```sql DQL theme={null}
/*+ {"cars": {"#intersects": 2}} */
SELECT * FROM cars WHERE color = 'blue' AND year > 2020
```
### #index
**Type**: `string`, `array of strings`, or `null`
Specifies the index to use or list of possible indices to pick from when planning index access.
* **String value**: Forces a specific index (e.g., `"ix_color"`)
* **Array of strings**: Provides a list of candidate indices for the planner to choose from (e.g., `["ix1", "ix2", "ix3"]`)
* **Empty string or `null`**: Indicates no index scan should be generated and a collection scan should be used
* **Empty array `[]`**: Indicates the planner can choose from all available indices (overrides `#disable_index_scan` for this collection)
```sql DQL theme={null}
-- Force a specific index
/*+ {"cars": {"#index": "ix_color"}} */
SELECT * FROM cars WHERE color = 'blue'
-- Provide a list of candidate indices
/*+ {"cars": {"#index": ["ix_color", "ix_year", "ix_mileage"]}} */
SELECT * FROM cars WHERE color = 'blue' AND year > 2020
-- Force a collection scan (no index)
/*+ {"cars": {"#index": null}} */
SELECT * FROM cars WHERE color = 'blue'
-- Allow all indices (override global #disable_index_scan)
/*+ {"#disable_index_scan": true, "cars": {"#index": []}} */
SELECT * FROM cars WHERE color = 'blue'
```
## Using Collection Aliases
When using comment-style directives with collection aliases, specify the alias (not the collection name) in the directive:
```sql DQL theme={null}
/*+ {"c": {"#index": "ix_color"}} */
SELECT * FROM cars c WHERE c.color = 'blue'
```
## Combining Directives
You can combine multiple directives in a single query:
```sql DQL theme={null}
/*+ {
"#prefer_order": true,
"#max_scans": 10,
"cars": {"#index": ["ix_color", "ix_year"]}
} */
SELECT * FROM cars WHERE color = 'blue' AND year > 2020 ORDER BY year DESC
```
## USE DIRECTIVES Examples
The `USE DIRECTIVES` clause is limited to collection-level directives:
```sql DQL theme={null}
-- Specify candidate indices
SELECT * FROM cars USE DIRECTIVES '{"#index": ["ix_color", "ix_year"]}'
WHERE color = 'blue' AND year > 2020
-- Force collection scan
SELECT * FROM cars USE DIRECTIVES '{"#index": ""}'
WHERE color = 'blue'
-- Prefer ordering
SELECT * FROM cars USE DIRECTIVES '{"#prefer_order": true}'
WHERE color = 'blue' ORDER BY year
```
## USE INDEX Examples
The `USE INDEX` clause is a shorthand for the `#index` directive:
```sql DQL theme={null}
-- Force a specific index
SELECT * FROM cars USE INDEX 'ix_color'
WHERE color = 'blue'
-- Force collection scan
SELECT * FROM cars USE INDEX ''
WHERE color = 'blue'
```
## Directive Behavior
* Any elements in the directives JSON that aren't understood are silently ignored
* The JSON must be syntactically correct, or a parser error will be reported
* If a specified index doesn't exist or isn't applicable to the statement, the directive is ignored and regular planning takes place using all available candidate indices
## Default Directives
You can set default directives for all queries in your Ditto instance using the `DQL_DEFAULT_DIRECTIVES` system parameter. These directives apply to every statement automatically, while individual statement directives can override them.
**Setting Default Directives:**
```sql DQL theme={null}
ALTER SYSTEM SET DQL_DEFAULT_DIRECTIVES = {"#reprepare": true, "#max_serial_ranges": 2}
```
**Use Cases:**
* **Development**: Set `#reprepare` to `true` during development to always use fresh query plans
* **Production**: Once statements and indices are finalized, unset `#reprepare` to benefit from the statement cache
* **Global Tuning**: Apply performance tuning directives across all queries without modifying each statement
**Example:**
```sql DQL theme={null}
-- Set default directives during development
ALTER SYSTEM SET DQL_DEFAULT_DIRECTIVES = {"#reprepare": true}
-- Queries will automatically use the default directive
SELECT * FROM cars WHERE color = 'blue'
-- Individual statement directives override defaults
/*+ {"#reprepare": false} */
SELECT * FROM cars WHERE color = 'blue'
```
Default directives are merged with statement-level directives, with statement-level directives taking precedence when there's a conflict.
## See Also
* [EXPLAIN](/dql/explain) - View query execution plans
* [PROFILE](/dql/profile) - Profile query performance
* [Indexing](/dql/indexing) - Creating and managing indices
* [Access Paths](/dql/access-paths) - Understanding query access paths