GATHER_TABLE_STATS|V4.2.2|OceanBase Database|docs|OceanBase Documentations

Parameter	Description
ownname	The schema where the table to be analyzed resides.
tabname	The name of the table.
partname	The name of the partition.
estimate_percent	The percentage of rows to be estimated. Value range: [0.000001,100].
block_sample	Specifies whether to use random block sampling (`TRUE`) or random row sampling (`FALSE`) for the database. Default value: `FALSE`. Random block sampling is more efficient, but sample values may be correlated if data is not randomly distributed on the disk.
method_opt	You can use only the `FOR ALL` syntax when setting global preferences or preferences at the schema, database, or dictionary level. Other than that, you can choose either or both of the following options for `method_opt`: `FOR ALL [INDEXED \\| HIDDEN] COLUMNS [size_clause]` `FOR COLUMNS [column_clause]` `size_clause` is defined as `size_clause := SIZE {integer \\| REPEAT \\| AUTO \\| SKEWONLY}`. `column_clause` is defined as `column_clause := column_name \\| extension name \\| extension`. `integer`: the number of histogram buckets. Valid range: [1,2048]. `REPEAT`: collects histograms only on columns with histograms. `AUTO`: determines the columns on which histograms are to be collected based on the data distribution and workloads of columns. `SKEWONLY`: determines the columns on which histograms are to be collected based on the data distribution of columns. `column_name`: the column name. `extension`: the name extension, which can be an expression or a column group in the format of `(column_name, column_name [, ...])`. Default value: `FOR ALL COLUMNS SIZE AUTO`.
degree	The degree of parallelism (DOP). Default value: `NULL`. `NULL` indicates that the default table DOP specified in the `DEGREE` clause of the `CREATE TABLE` or `ALTER TABLE` statement is used. When `DEGREE=>NULL` or `DEGREE=>n` is specified, `DBMS_STATS` may adopt serial execution if parallel execution cannot be guaranteed due to the object size.
granularity	The granularity of statistics to be collected (applicable only when the table is partitioned). `ALL`: collects all statistics, including subpartition-level, partition-level, and global statistics. `APPROX_GLOBAL AND PARTITION`: This option is similar to `GLOBAL AND PARTITION`. However, when this option is specified, global statistics are a summary of partition-level statistics. This option summarizes all statistics other than the number of distinct values and the number of distinct keys of indexes. However, it summarizes only the partitions with statistics. It collects global statistics if `partname` is `NULL` or summary fails, for example, statistics on a partition are missing. `AUTO`: determines the granularity based on the partition type. This is the default value. `DEFAULT`: collects global and partition-level statistics. This option is deprecated. We recommend that you use `GLOBAL AND PARTITION`. `GLOBAL`: collects global statistics. `GLOBAL AND PARTITION`: collects global and partition-level statistics. Statistics at the subpartition level will not be collected even for composite partitioned objects. `PARTITION`: collects partition-level statistics. `SUBPARTITION`: collects subpartition-level statistics.
cascade	Specifies to collect index statistics besides table and column statistics, which is equivalent to running the `GATHER_INDEX_STATS` procedure on each index in the schema.
no_invalidate	Specifies whether to refresh the plan cache during statistics collection. If you set the value to `TRUE`, the plan cache will not be refreshed during statistics collection.
stattype	The location where statistics are stored.
force	Specifies whether to perform forced collection and ignore the lock status. Default value: `FALSE`. If you set the value to `TRUE`, statistics will be collected even if they are locked.

ownname

The schema where the table to be analyzed resides.

tabname

The name of the table.

partname

The name of the partition.

estimate_percent

The percentage of rows to be estimated. Value range: [0.000001,100].

block_sample

Specifies whether to use random block sampling (TRUE) or random row sampling (FALSE) for the database. Default value: FALSE. Random block sampling is more efficient, but sample values may be correlated if data is not randomly distributed on the disk.

method_opt

You can use only the FOR ALL syntax when setting global preferences or preferences at the schema, database, or dictionary level. Other than that, you can choose either or both of the following options for method_opt:

FOR ALL [INDEXED \| HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [column_clause] size_clause is defined as size_clause := SIZE {integer \| REPEAT \| AUTO \| SKEWONLY}. column_clause is defined as column_clause := column_name \| extension name \| extension.
integer: the number of histogram buckets. Valid range: [1,2048].
REPEAT: collects histograms only on columns with histograms.
AUTO: determines the columns on which histograms are to be collected based on the data distribution and workloads of columns.
SKEWONLY: determines the columns on which histograms are to be collected based on the data distribution of columns.
column_name: the column name.
extension: the name extension, which can be an expression or a column group in the format of (column_name, column_name [, ...]). Default value: FOR ALL COLUMNS SIZE AUTO.

degree

The degree of parallelism (DOP). Default value: NULL. NULL indicates that the default table DOP specified in the DEGREE clause of the CREATE TABLE or ALTER TABLE statement is used. When DEGREE=>NULL or DEGREE=>n is specified, DBMS_STATS may adopt serial execution if parallel execution cannot be guaranteed due to the object size.

granularity

The granularity of statistics to be collected (applicable only when the table is partitioned).

ALL: collects all statistics, including subpartition-level, partition-level, and global statistics.
APPROX_GLOBAL AND PARTITION: This option is similar to GLOBAL AND PARTITION. However, when this option is specified, global statistics are a summary of partition-level statistics. This option summarizes all statistics other than the number of distinct values and the number of distinct keys of indexes. However, it summarizes only the partitions with statistics. It collects global statistics if partname is NULL or summary fails, for example, statistics on a partition are missing.
AUTO: determines the granularity based on the partition type. This is the default value.
DEFAULT: collects global and partition-level statistics. This option is deprecated. We recommend that you use GLOBAL AND PARTITION.
GLOBAL: collects global statistics.
GLOBAL AND PARTITION: collects global and partition-level statistics. Statistics at the subpartition level will not be collected even for composite partitioned objects.
PARTITION: collects partition-level statistics.
SUBPARTITION: collects subpartition-level statistics.

cascade

Specifies to collect index statistics besides table and column statistics, which is equivalent to running the GATHER_INDEX_STATS procedure on each index in the schema.

no_invalidate

Specifies whether to refresh the plan cache during statistics collection. If you set the value to TRUE, the plan cache will not be refreshed during statistics collection.

stattype

The location where statistics are stored.

force

Specifies whether to perform forced collection and ignore the lock status. Default value: FALSE. If you set the value to TRUE, statistics will be collected even if they are locked.

Error code	Description
ORA-20000	The table does not exist, or you do not have the required privileges.
ORA-20001	The input value is incorrect.

GATHER_TABLE_STATS

Syntax

Parameters

Exceptions

Considerations

Examples