Skip to main content

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 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:
DQL
/*+ {"#prefer_order": true, "#max_scans": 10} */
SELECT * FROM cars WHERE color = 'blue' ORDER BY year
DQL
--+ {"#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:
DQL
SELECT * FROM cars USE DIRECTIVES '{"#index": ["ix_color", "ix_year"]}'
WHERE color = 'blue'
USE DIRECTIVES Syntax Diagram

​
3. USE INDEX Clause

A shorthand for specifying index directives:
DQL
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.
DQL
/*+ {"#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.
DQL
/*+ {"#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).
DQL
/*+ {"#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.
DQL
/*+ {"#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.
DQL
/*+ {"#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.
DQL
/*+ {"#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.
DQL
/*+ {"#profile": true} */
SELECT * FROM cars WHERE color = 'blue'
Using #profile is equivalent to using the 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.
DQL
/*+ {"#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.
DQL
/*+ {"#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.
DQL
/*+ {"#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.
DQL
/*+ {"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.
DQL
/*+ {"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)
DQL
-- 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:
DQL
/*+ {"c": {"#index": "ix_color"}} */
SELECT * FROM cars c WHERE c.color = 'blue'

​
Combining Directives

You can combine multiple directives in a single query:
DQL
/*+ {
  "#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:
DQL
-- 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:
DQL
-- 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:
DQL
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:
DQL
-- 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