Help Center > > Developer Guide> Setting GUC Parameters> Miscellaneous Parameters

Miscellaneous Parameters

Updated at: Jul 15, 2020 GMT+08:00

Set the following parameters by referring to Table 1.

gin_fuzzy_search_limit

Parameter description: The primary goal of developing GIN indexes was to create support for highly scalable full-text search in DWS, and there are often situations when a full-text search returns a large set of results. Moreover, this situation often occurs when the query contains frequently used words, so that the large result set is not even useful. Because reading many tuples from the disk and sorting them will consume large numbers of resources, this is unacceptable for production. To facilitate controlled execution of such queries, GIN has a configurable soft upper limit on the number of rows returned: configuration parameter gin_fuzzy_search_limit. The default value 0 indicates that there is no limit on the returned set. If a non-zero value is set, the returned set is a subset chosen from the whole result set at random.

This parameter is a USERSET parameter.

Value range: 0 to the maximum int value

Default value: 0

enable_cluster_resize

Parameter description: If an SQL statement involves tables belonging to different groups, you can enable this parameter to push the execution plan of the statement to improve performance.

This parameter is a SUSET parameter.

Value range: Boolean

  • on indicates the execution plan of the statement can be pushed.
  • off indicates the execution plan of the statement cannot be pushed.

Default value: off

This parameter is used for internal O&M. Do not set it to on unless absolutely necessary.

cstore_insert_mode

Parameter description: Specifies the storage location of data to be imported to an HDFS table. This parameter is needed for operations that involve data import, such as INSERT, UPDATE, COPY, DELTAMERGE, and VACUUM FULL.

This parameter is a USERSET parameter.

Value range: enumerated values

  • AUTO: The major part of imported data is stored in HDFS, and the tail is stored in the delta table.
  • DELTA: All the imported data is stored in the delta table.
  • MAIN: All the imported data is stored in HDFS.

Default value: AUTO

You can set other values as the default in the configuration file.

enable_upgrade_merge_lock_mode

Parameter description: If this parameter is set to on, the delta merge operation internally increases the lock level, and errors can be avoided when update and delete operations are performed at the same time.

This parameter is a USERSET parameter.

Value range: Boolean

  • If this parameter is set to on, the delta merge operation internally increases the lock level. In this way, when any two of the DELTAMERGE, UPDATE, and DELETE operations are concurrently performed, an operation can be performed only after the previous one is complete.
  • If this parameter is set to off, and any two of the DELTAMERGE, UPDATE, and DELETE operations are concurrently performed to data in a row in the delta table of the HDFS table, errors will be reported during the later operation, and the operation will stop.

Default value: off

ngram_gram_size

Parameter description: Specifies the length of the ngram parser segmentation.

This parameter is a USERSET parameter.

Value range: an integer ranging from 1 to 4

Default value: 2

ngram_grapsymbol_ignore

Parameter description: Specifies whether the ngram parser ignores graphical characters.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Ignores graphical characters.
  • off: Does not ignore graphical characters.

Default value: off

ngram_punctuation_ignore

Parameter description: Specifies whether the ngram parser ignores punctuations.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Ignores punctuations.
  • off: Does not ignore punctuations.

Default value: on

zhparser_multi_duality

Parameter description: Specifies whether Zhparser aggregates segments in long words with duality.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Aggregates segments in long words with duality.
  • off: Does not aggregate segments in long words with duality.

Default value: off

zhparser_multi_short

Parameter description: Specifies whether Zhparser executes long words composite divide.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Performs compound segmentation for long words.
  • off: Does not perform compound segmentation for long words.

Default value: on

zhparser_multi_zall

Parameter description: Specifies whether Zhparser displays all single words individually.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Displays all single words separately.
  • off: Does not display all single words separately.

Default value: off

zhparser_multi_zmain

Parameter description: Specifies whether Zhparser displays important single words separately.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Displays important single words separately.
  • off: Does not display important single words separately.

Default value: off

zhparser_punctuation_ignore

Parameter description: Specifies whether the Zhparser segmentation result ignores special characters including punctuations (\r and \n will not be ignored).

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Ignores all the special characters including punctuations.
  • off: Does not ignore all the special characters including punctuations.

Default value: on

zhparser_seg_with_duality

Parameter description: Specifies whether Zhparser aggregates segments in long words with duality.

This parameter is a USERSET parameter.

Value range: Boolean

  • on: Aggregates segments in long words with duality.
  • off: Does not aggregate segments in long words with duality.

Default value: off

acceleration_with_compute_pool

Parameter description: Specifies whether to use the computing resource pool for acceleration when a foreign OBS or HDFS table is queried.

This parameter is a USERSET parameter.

Value range: Boolean

  • on indicates that the query covering the foreign OBS or HDFS table is accelerated based on the cost when the computing resource pool is available.
  • off indicates that no query is accelerated using the computing resource pool.

Default value: off

behavior_compat_options

Parameter description: Specifies database compatibility behavior. Multiple items are separated by commas (,).

This parameter is a USERSET parameter.

Value range: a string

Default value: empty

  • Currently, only Table 1 is supported.
  • Multiple items are separated by commas (,), for example, set behavior_compat_options='end_month_calculate,display_leading_zero'.
Table 1 Compatibility configuration items

Configuration Item

Behavior

display_leading_zero

Specifies how floating point numbers are displayed.

  • If this item is not specified, for a decimal number between -1 and 1, the 0 before the decimal point is not displayed. For example, 0.25 is displayed as .25.
  • If this item is specified, for a decimal number between -1 and 1, the 0 before the decimal point is displayed. For example, 0.25 is displayed as 0.25.

end_month_calculate

Specifies the calculation logic of the add_months function.

Assume that the two parameters of the add_months function are param1 and param2, and that the month of param1 and param2 is result.

  • If this item is not specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in param1. For example:
1
2
3
4
5
select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)
  • If this item is specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in result. For example:
1
2
3
4
5
select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)

compat_analyze_sample

Specifies the sampling behavior of the ANALYZE operation.

If this item is specified, the sample collected by the ANALYZE operation will be limited to around 30,000 records, controlling CN memory consumption and maintaining the stability of ANALYZE.

bind_schema_tablespace

Binds a schema with the tablespace with the same name.

If a tablespace name is the same as sche_name, default_tablespace will also be set to sche_name if search_path is set to sche_name.

bind_procedure_searchpath

Specifies the search path of the database object for which no schema name is specified.

If no schema name is specified for a stored procedure, the search is performed in the schema to which the stored procedure belongs.

If the stored procedure is not found, the following operations are performed:

  • If this item is not specified, the system reports an error and exits.
  • If this item is specified, the search continues based on the settings of search_path. If the issue persists, the system reports an error and exits.

correct_to_number

Controls the compatibility of the to_number() result.

If this item is specified, the value of to_number() is the same as that of pg11. Otherwise, the value is the same as that of Oracle.

unbind_divide_bound

Controls the range check on the result of integer division.

If this item is specified, you do not need to check the range of the division result. For example, the result of INT_MIN/(-1) can be INT_MAX+1. If this item is not specified, an out-of-bounds error is reported because the result is greater than INT_MAX.

merge_update_multi

Performs an update if multiple rows are matched for MERGE INTO.

If this item is specified, no error is reported if multiple rows are matched. Otherwise, an error is reported (same as Oracle).

return_null_string

Specifies how to display the empty result (empty string '') of the lpad(), rpad(), repeat(), regexp_split_to_table(), and split_part() functions.

In Teradata-compatible mode, this parameter has no impact on the display of an empty result.

In Oracle-compatible mode, an empty string will be returned as NULL if this parameter is specified.

  • If this item is not specified, the empty string is displayed as NULL.
1
2
3
4
5
select length(lpad('123',0,'*')) from dual;
length
--------

(1 row)
  • If this item is specified, the empty string is displayed as single quotation marks ('').
1
2
3
4
5
select length(lpad('123',0,'*')) from dual;
length
--------
0
(1 row)

compat_concat_variadic

Specifies the compatibility of variadic results of the concat() and concat_ws() functions.

If this item is specified and a concat function has a parameter of the variadic type, different result formats in Oracle and Teradata are retained. If this item is not specified and a concat function has a parameter of the variadic type, the result format of Oracle is retained for both Oracle and Teradata.

table_skewness_warning_threshold

Parameter description: Specifies the threshold for triggering a table skew alarm.

This parameter is a SUSET parameter.

Value range: a floating point number ranging from 0 to 1

Default value: 1

table_skewness_warning_rows

Parameter description: Specifies the minimum number of rows for triggering a table skew alarm.

This parameter is a SUSET parameter.

Value range: an integer ranging from 0 to INT_MAX

Default value: 100000

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel