Supported Hint Types
Cirro supports optimizer hints of these types:
|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.|
Optimizer 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.
Supported Optimizer Hints
The following pages and posts are tagged with
|Force Column Data Type Hint||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 Rowcount Hint||Page||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 appli...|
|Force Runmode Hint||Page||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 Split Column Hint||Page||Forces the optimizer to split data retrieval using a specified column. This parallelizes the query and data movement.|
Combining Optimizer 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.
A data type hint with a runmode hint
SELECT /*+ FORCE_RUNMODE('ORACLE1') */ c1.customer_id, c1.first_name, c1.last_name /*+ FORCE_DATATYPE( 'varchar2(100)' ) */, c2.marital_status FROM MYSQL1.inventory.inventory.customer c1 INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2 ON c1.customer_id = c2.customer_id;
Data type hints with runmode hints.
SELECT /*+ FORCE_RUNMODE('HIVE1') */ c3.customer_id, c3.first_name, c3.last_name /*+ FORCE_DATATYPE( VARCHAR(100) ) */, c3.marital_status /*+ FORCE_DATATYPE( 'CHAR(1)' ) */, v.visit_date, v.visit_url FROM (SELECT /*+ FORCE_RUNMODE('ORACLE1') */ c1.customer_id, c1.first_name, c1.last_name /*+ FORCE_DATATYPE( 'VARCHAR2(100)' ) */, c2.marital_status FROM MYSQL1.inventory.inventory.customer c1 INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2 ON c1.customer_id = c2.customer_id) c3 INNER JOIN HIVE1."default"."default".visits v ON c3.customer_id = v.customer_id;
A rowcount with runmode
SELECT /*+ FORCE_RUNMODE('MYSQL1') */ * FROM MYSQL1.inventory.inventory.customer c1, ORACLE1.ORACLE.CIRRO.cust c2 /*+ FORCE_ROWCOUNT(10000) */