Oracle 参数 APPROX_FOR_PERCENTILE 官方解释，作用，如何配置最优化建议

本站中文解释

APPROX_FOR_PERCENTILE参数用来设置估算特定百分位数据时 Oracle 是否应使用计算机近似运算方法。如果该参数设置为TRUE，则 Oracle 会使用近似方法估算某个特定百分位数；反之，Oracle 则不使用近似方法，直接将数据按照其顺序来计算百分位数。

正确设置APPROX_FOR_PERCENTILE参数需要根据应用程序对数据的精确性需求来确定，建议需要精确结果的场合需要将该参数设置为 FALSE；而当应用程序对数据的精确性要求可以接受一定的误差时可以将该参数设置为TRUE，以提高查询速度。

官方英文解释

APPROX_FOR_PERCENTILE converts exact percentile functions to their approximate percentile function counterparts.

Approximate percentile function queries are faster than their exact percentile function query counterparts, so they can be useful in situations where a tolerable amount of error is acceptable in order to obtain faster query results.

The values that can be set are:

• NONE:

  No queries are converted. This is the default value.

• PERCENTILE_CONT:

  Converts PERCENTILE_CONT queries to APPROX_PERCENTILE queries.

• PERCENTILE_CONT DETERMINISTIC:

  Converts PERCENTILE_CONT queries to APPROX_PERCENTILE DETERMINSTIC queries.

• PERCENTILE_DISC:

  Converts PERCENTILE_DISC queries to APPROX_PERCENTILE queries.

• PERCENTILE DISC DETERMINISTIC:

  Converts PERCENTILE_DISC queries to APPROX_PERCENTILE DETERMINISTIC queries.

• ALL:

  Converts both PERCENTILE_CONT queries and PERCENTILE_DISC queries to APPROX_PERCENTILE queries.

• ALL DETERMINISTIC:

  Converts both PERCENTILE_CONT and PERCENTILE_DISC queries to APPROX_PERCENTILE DETERMINISTIC queries.

See Also:

• “APPROX_FOR_AGGREGATION”

• “APPROX_FOR_COUNT_DISTINCT”

• Oracle Database SQL
  Language Reference for information about APPROX_MEDIAN aggregate functions

• Oracle Database SQL
  Language Reference for information about APPROX_PERCENTILE aggregate functions