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

Syntax

/*+ FORCE_ROWCOUNT(userRowcountValue) */

Arguments

  • userRowcountValue: 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.

    • The force rowcount hint allows the user to override the system-determined rowcount.

  • Successful use of this hint requires intimate knowledge of optimizer behavior for a particular query.

  • Forcing a rowcount may have no impact on resulting query optimizer behavior.

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

  • This hint can be combined with the FORCE_RUNMODE hint 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. Note how 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;