Suggest to Cirro that a federated query is run on a specific system.

Syntax

/*+ FORCE_RUNMODE('systemName') */

Arguments

  • systemName: The case-sensitive name of a federated system accessible to Cirro and appropriate to execute the query.

Hint Placement

SELECT
    /*+ FORCE_RUNMODE('systemName') */
    selectList
FROM
    fromClause;

Additional Information

  • The query upon which the hint is specified may not run in isolation.

    • For example, if the hint is used in a subquery, since the hint is only applicable to the scope of that subquery, there may be additional data movements from/to other systems before or after that statement is executed.
  • FORCE_RUNMODE does not apply to EXISTS(), IN(), ANY() or subqueries with results treated as a value.

Examples

Simple example

SELECT
    /*+ FORCE_RUNMODE('MYSQL1') */
    c.customer_id,
    c.first_name,
    c.last_name,
    v.visit_date,
    v.visit_url
FROM
    MYSQL1.inventory.inventory.customer c
    inner join HIVE1."default"."default".visits v
    on c.customer_id = v.customer_id

Three table example

SELECT
    /*+ FORCE_RUNMODE('MYSQL1') */
    c1.customer_id,
    c1.first_name,
    c1.last_name,
    c2.marital_status,
    v.visit_date,
    v.visit_url
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
    ON c1.customer_id = c2.customer_id
    INNER JOIN HIVE1."default"."default".visits v
    ON c1.customer_id = v.customer_id

Three table example with hinted subquery join

SELECT
    /*+ FORCE_RUNMODE('HIVE1') */
    c1.customer_id,
    c1.first_name,
    c1.last_name,
    c1.zip_code,
    v.visit_date,
    v.visit_url
FROM
    (SELECT /*+ FORCE_RUNMODE('MYSQL1') */
    c.customer_id, c.first_name, c.last_name, addr.zip_code
    FROM MYSQL1.inventory.inventory.customer c
    INNER JOIN MYSQL1.inventory.inventory.customer_address addr
    ON c.customer_id = addr.customer_id
    WHERE addr.is_primary = 1) c1
    INNER JOIN HIVE1."default"."default".visits v
    ON c1.customer_id = v.customer_id

FORCE_RUNMODE with UNION

SELECT
    /*+ FORCE_RUNMODE('HIVE1') */
    c.customer_id
FROM
    MYSQL1.inventory.inventory.customer c
UNION
SELECT
    /*+ FORCE_RUNMODE('MYSQL1') */
    c.customer_id
FROM
    ORACLE1.ORACLE.CIRRO.customer c

FORCE_RUNMODE with VIEW

CREATE VIEW usv_master_customer AS
SELECT
    /*+ FORCE_RUNMODE('ORACLE1') */
    c1.customer_id,
    c1.first_name,
    c1.last_name,
    c2.marital_status
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
    ON c1.customer_id = c2.customer_id;

SELECT
    *
FROM
    HUB.home.views.usv_master_customer;
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;

FORCE_RUNMODE used with FORCE_ROWCOUNT

SELECT
    /*+ FORCE_RUNMODE('MYSQL1') */
    *
FROM
    MYSQL1.inventory.inventory.customer c1,
    ORACLE1.ORACLE.CIRRO.cust c2 /*+ FORCE_ROWCOUNT(10000) */