When connected to Cirro datasources, you can include optimizer hints in your SQL statements to control aspects of query execution.

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.

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

TitleTypeExcerpt
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.

Examples

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) */