A hint instructs Cirro how to execute a query.

Supported Hints

The following pages and posts are tagged with

COMPARE_ENGINE Page COMPARE_ENGINE is used to define the type of comparison processing used in a COMPARE statement.
COMPARE_QUEUESIZE Page Set the maximimum queue size for asynchronous compare engines.
COMPARE_WRITERS Page Sets the number of compare log writers (when not single-threaded). Default value is 2.
FORCE_BATCHSIZE Page Gives the agent used to write data a hint as to the number of rows that should be inserted into the database in each batch. Batching the inserts reduces the ...
FORCE_DATATYPE Page Forces the data type during data movement for a particular column to be one of the Cirro data types or a data type native to a specific database in the feder...
FORCE_FETCHSIZE Page Gives the JDBC driver used to read data a hint as to the number of rows that should be fetched from the database when more rows are needed. If this number is...
FORCE_ROWCOUNT Page Tells query optimizer how big the table is.
FORCE_RUNMODE Page Suggest to Cirro that a federated query is run on a specific system.
FORCE_SPLIT_COLUMN Page Forces the optimizer to split data retrieval using a specified column. This only takes effect when the query is executed in parallel.
FORCE_TRANSFERTHREADS Page FORCE_TRANSFERTHREADS allows you to define parallelization in a statement rather than at datasource level or via agent configuration.

SOURCE_HINT only takes effect for INSERT or SELECT statements.

Supported Hint Types

Cirro supports optimizer hints of these types:

Type Description
Column hint Specified after a single column reference in a SQL query, and only applies to that specific column reference.
Table hint Applies to a single table reference in a SQL query. Table references can be a specific table, view, or subquery reference.
Join hint Applies to a single join between two specified tables.
Query hint Applies only to the scope of the specific SQL query it is declared in. It will not be applied within subqueries defined in that SQL query. Other hint types can also be present as a query hint.

Restrictions and Limitations

SQL queries executed on Cirro datasources are optimized automatically by Cirro

  • Only use Optimizer Hints where query performance and completion times cannot be improved through other methods.

  • Hints can only be added in SQL clients that allow direct editing of SQL statements. Users who experience this issue can create Cirro Views to include hints.

  • If the Cirro query optimizer determines a hint cannot be included in query execution, it may be ignored.

Combining Hints within a Query

  • You can include multiple types of hint within single queries.

  • Follow the same syntax rules for each hint as if it were included on its own.