Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Optimization/ Hint-based Optimization/ Rows Hints
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Rows Hints

Description

Specifies the number of rows in an intermediate result set. Both absolute values and relative values are supported.

Syntax

1
 
rows( [@queryblock] table_list #|+|-|* const)

Parameters

  • For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint takes effect in the current query block.
  • #, +, -, and * are operators used for hinting the estimation. # indicates that the original estimation is used without any calculation. +, -, and * indicate that the original estimation is calculated using these operators. The minimum calculation result is 1. table_list specifies the tables to be joined. The values are the same as those of table_list in Join Operation Hints.
  • const can be any non-negative number and supports scientific notation.

For example:

rows(t1 #5): The result set of t1 is five rows.

rows(t1 t2 t3 *1000): The result set of joined t1, t2, and t3 is multiplied by 1000.

Suggestion

  • The hint using * for two tables is recommended. This hint will be triggered if the two tables appear on two sides of a join. For example, if the hint is rows(t1 t2 * 3), the join result of (t1 t3 t4) and (t2 t5 t6) will be multiplied by 3 because t1 and t2 appear on both sides of the join.
  • rows hints can be specified for the result sets of a single table, multiple tables, function tables, and subquery scan tables.

Example

Hint the query plan in Examples as follows:

1
2
 
explain
select /*+ rows(store_sales store_returns *50) */ i_product_name product_name ...

Multiply the result set of joined store_sales and store_returns by 50. The optimized plan is as follows.

The estimation value after the hint in row 11 is 360, and the original value is rounded off to 7.

Parent topic: Hint-based Optimization