Tells query optimizer how big the table is.

Syntax

/*+ FORCE_ROWCOUNT(n) */

Arguments

  • n: A numeric of type BIGINT which specifies the forced rowcount. This number should be greater than or equal to zero.

Hint Placement

Hint placement for query-level

SELECT
    /*+ FORCE_ROWCOUNT(userRowcountValue) */
    *
FROM
    table1 t1
    INNER JOIN table2 t2
    ON t1.col = t2.col;

Hint placement for table and view rowcounts.

SELECT *
FROM
    table1 t1
    INNER JOIN table2 t2 /*+ FORCE_ROWCOUNT(userRowcountValue) */
    ON t1.col = t2.col;

SELECT *
FROM
    table1 t1
    INNER JOIN viewName1 v1 /*+ FORCE_ROWCOUNT(userRowcountValue) */
    ON c1.col = c2.col;

Hint placement for subquery rowcount.

SELECT *
FROM
    table1 t1
    INNER JOIN (select col1, col2 from table2) t2
    /*+ FORCE_ROWCOUNT(userRowcountValue) */
    ON c1.col = c2.col;

Additional Information

  • When the optimizer is analyzing the tables involved in a query, it determines the number of rows present in each table.

    • This rowcount is one component of the calculation on how to efficiently orchestrate data movements from one system to another during query execution.
  • FORCE_ROWCOUNT allows the user to override the system-determined rowcount, and

    • may have no impact on resulting query optimizer behavior.

    • does not truncate or LIMIT returned row counts; the forced rowcount value is used only to adjust data movement decisions.

    • can be combined with FORCE_RUNMODE to guarantee data movement direction.

Examples

Example of query-scoped rowcount hint. The result of the entire query is forced to appear to the query engine as 10,000 rows.

SELECT
    /*+ FORCE_ROWCOUNT(10000) */
    *
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
    ON c1.customer_id = c2.customer_id;

Example of table-scoped rowcount hint. The value of 10,000 rows is used for the c2 table by the optimizer.

SELECT
    *
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
    /*+ FORCE_ROWCOUNT(10000) */
    ON c1.customer_id = c2.customer_id;

Example of join-scoped rowcount hint. The result of the join between c1 and c2 is forced to appear as 10,000 rows to the query engine.

SELECT
    *
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN /*+ FORCE_ROWCOUNT(10000) */
    ORACLE1.ORACLE.CIRRO.cust c2
    ON c1.customer_id = c2.customer_id;

Example of subquery rowcount hint. The result of the subquery is forced to appear as 10,000 rows to the query engine. Note that the rowcount hint is INSIDE the subquery, not OUTSIDE after the c2 alias.

SELECT
    *
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN (
        SELECT
            /*+ FORCE_ROWCOUNT(10000) */
            customer_id,
            last_name
        FROM
            ORACLE1.ORACLE.CIRRO.cust
    ) c2
    ON c1.customer_id = c2.customer_id;

Example of view rowcount hint.

  • The result of the view is forced to appear as 10,000 rows to the query engine.

  • The rowcount hint is INSIDE the view, not OUTSIDE after the c2 alias.

CREATE VIEW HUB.home.views.oracle_cust as
SELECT
    /*+ FORCE_ROWCOUNT(10000) */
    customer_id,
    last_name
FROM
    ORACLE1.ORACLE.CIRRO.cust;

SELECT
    *
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN HUB.home.views.oracle_cust c2
    ON c1.customer_id = c2.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) */