A hint instructs Cirro how to execute a query.

Supported Hints

Hint Description See Also
COMPARE_WRITERS (n) Sets the number of compare log writers (when not single-threaded). Default value is 2. COMPARE_WRITERS
FORCE_BATCHSIZE (N) 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 number of network round trips and helps the database maximize its performance. If the batch size is too large, you may encounter memory issues. Force Batchsize
FORCE_DATATYPE 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 federation. This hint overrides the Cirro optimizer’s choice of data type in data movements. FORCE_DATATYPE
FORCE_FETCHSIZE(N) 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 too low, the number of network round trips needed to retieve the data increases. If the fetch size is too large, you may encounter memory issues. Force Fetchsize
Oracle Fetch Size Docs
FORCE_ROWCOUNT Allows the user to override the rowcount used by the query optimizer, with the goal of altering data movement behaviors. The Force Rowcount hint can be applied at the query, table, or join level FORCE_ROWCOUNT
FORCE_RUNMODE Used only for COMPARE without SAVE. Forces the optimizer to move query data to and then execute the query on a user-specified system rather than a system selected by the optimizer. FORCE_RUNMODE
FORCE_SPLIT_COLUMN Forces the optimizer to split data retrieval using a specified column. This parallelizes the query and data movement. FORCE_SPLIT_COLUMN
FORCE_SORT ({TRUE | FALSE}) Used only for SYNC TO. Use the sorted or unsorted compare engine. FORCE_SORT
FORCE_TRANSFERTHREADS Used only for COMPARE or SYNC. The number of transfer threads of execution that will be used to move data from place to place in that part of the query structure. FORCE_TRANSFERTHREADS will cause the compare engine to use N difference loggers. COMPARE
SYNC statement
SOURCE_HINT(?) Allows for the addition of native hints for the source system in question.  

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.