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.
| Property | Description |
|---|---|
|
Parameter type |
String |
|
Syntax |
|
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Basic |
No |
|
Oracle RAC |
Different instances can use different values. |
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_CONTqueries toAPPROX_PERCENTILEqueries. -
PERCENTILE_CONT DETERMINISTIC:Converts
PERCENTILE_CONTqueries toAPPROX_PERCENTILE DETERMINSTICqueries. -
PERCENTILE_DISC:Converts
PERCENTILE_DISCqueries toAPPROX_PERCENTILEqueries. -
PERCENTILE DISC DETERMINISTIC:Converts
PERCENTILE_DISCqueries toAPPROX_PERCENTILE DETERMINISTICqueries. -
ALL:Converts both
PERCENTILE_CONTqueries andPERCENTILE_DISCqueries toAPPROX_PERCENTILEqueries. -
ALL DETERMINISTIC:Converts both
PERCENTILE_CONTandPERCENTILE_DISCqueries toAPPROX_PERCENTILE DETERMINISTICqueries.
See Also:
-
“APPROX_FOR_AGGREGATION”
-
“APPROX_FOR_COUNT_DISTINCT”
-
Oracle Database SQL
Language Reference for information aboutAPPROX_MEDIANaggregate functions -
Oracle Database SQL
Language Reference for information aboutAPPROX_PERCENTILEaggregate functions
