DBE_STATS
The advanced package DBE_STATS implements some capabilities of managing statistics information, including locking, unlocking, rolling back, and clearing historical statistics.
The DBE_STATS advanced package does not support temporary tables.
API Description
|
API |
Description |
|---|---|
|
Locks table-level statistics. |
|
|
Locks partition-level statistics. |
|
|
Locks column-level statistics. |
|
|
Locks statistics about all tables in a specified schema. |
|
|
Unlocks table-level statistics. |
|
|
Unlocks partition-level statistics. |
|
|
Unlocks column-level statistics. |
|
|
Unlocks statistics about all tables in a specified schema. |
|
|
Rolls back table-level statistics to a specified time point. |
|
|
Rolls back partition-level statistics to a specified time point. |
|
|
Rolls back column-level statistics to a specified time point. |
|
|
Rolls back statistics about all tables in a specified schema to a specified time point. |
|
|
Clears all historical statistics before a specified time point. |
|
|
Obtains the retention period of historical statistics. |
|
|
Obtains the earliest available time of the current historical statistics. |
|
|
Creates a statistics table for storing statistics. |
|
|
Deletes a statistics table for storing statistics. |
|
|
Retrieves statistics for the specified index and stores them in the user statistics table. |
|
|
Retrieves statistics for the specified table and stores them in the user statistics table. |
|
|
Retrieves statistics for the specified column and stores them in the user statistics table. |
|
|
Retrieves statistics for the specified schema and stores them in the user statistics table. |
|
|
Retrieves statistics for the specified index from the user statistics table and writes it back to the system catalog. |
|
|
Retrieves statistics for the specified table from the user statistics table and writes it back to the system catalog. |
|
|
Retrieves statistics for the specified column from the user statistics table and writes it back to the system catalog. |
|
|
Retrieves statistics for the specified schema from the user statistics table and writes it back to the system catalog. |
|
|
Sets column-related statistics, including single-column, multi-column, and expression statistics. |
|
|
Sets index-related statistics. |
|
|
Sets table-related statistics. |
|
|
Deletes column-related statistics, including single-column, multi-column, and expression statistics. |
|
|
Deletes index-related statistics. |
|
|
Deletes table-related statistics. |
|
|
Deletes statistics about all tables, indexes, and columns in a schema. |
- DBE_STATS.LOCK_TABLE_STATS
Locks the statistics of a table. After the table is locked, the statistics of the table cannot be updated, and the indexes, partitions, and columns involved in the table are locked synchronously. This API does not return any value.
The prototype of the DBE_STATS.LOCK_TABLE_STATS function is as follows:
DBE_STATS.LOCK_TABLE_STATS( ownname VARCHAR2, tabname VARCHAR2 );
Table 2 DBE_STATS.LOCK_TABLE_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the table to be locked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to be locked.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- You can check stat_state in reloptions of the table to obtain the lock status of the table.
- When a table is locked, its statistics cannot be updated.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table. gaussdb=# CREATE SCHEMA dbe_stats_lock; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_lock; SET gaussdb=# CREATE TABLE t1(a int,b int); CREATE TABLE -- Lock the table and check its locking status. gaussdb=# CALL DBE_STATS.LOCK_TABLE_STATS(ownname=>'dbe_stats_lock',tabname=>'t1'); lock_table_stats ------------------ (1 row) gaussdb=# SELECT relname,instr(reloptions::text,'stat_state=locked',1,1) <> 0 as exist_lock FROM PG_CLASS WHERE relname='t1' and relnamespace = (SELECT oid FROM PG_NAMESPACE WHERE nspname='dbe_stats_lock'); relname | exist_lock ---------+------------ t1 | t (1 row) -- An error is reported after the ANALYZE operation is performed. gaussdb=# ANALYZE t1; ERROR: The statistics is locked, cannot be updated. -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_lock; DROP SCHEMA
- DBE_STATS.LOCK_PARTITION_STATS
Locks the statistics of a partition. After the partition is locked, the statistics of the partition cannot be updated, and the indexes, partitions, and columns related to the partition are locked synchronously. This API does not return any value.
The prototype of the DBE_STATS.LOCK_PARTITION_STATS function is as follows:
DBE_STATS.LOCK_PARTITION_STATS( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 );Table 3 DBE_STATS.LOCK_PARTITION_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the partition to be locked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to which the partition to be locked belongs.
partname
varchar2
No
Name of the partition to be locked.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- You can view stat_state in reloptions of the partition to obtain the lock status of the table.
- When the partition or the table to which the table belongs is locked, its statistics cannot be updated.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table. gaussdb=# CREATE SCHEMA dbe_stats_lock; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_lock; SET gaussdb=# CREATE TABLE upart_table(a int, b int, c int) PARTITION BY RANGE(a) ( PARTITION p1 VALUES LESS THAN(1200), PARTITION p2 VALUES LESS THAN(2400), PARTITION p3 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE -- Lock a partition. Other partitions and tables are not affected. gaussdb=# CALL DBE_STATS.LOCK_PARTITION_STATS(ownname=>'dbe_stats_lock',tabname=>'upart_table',partname=>'p1'); lock_partition_stats ---------------------- (1 row) gaussdb=# SELECT relname,instr(reloptions::text,'stat_state=locked',1,1) <> 0 as exist_lock FROM PG_CLASS WHERE relname='upart_table'; relname | exist_lock -------------+------------ upart_table | f (1 row) gaussdb=# SELECT relname,instr(reloptions::text,'stat_state=locked',1,1) <> 0 as exist_lock FROM PG_PARTITION WHERE parentid='upart_table'::REGCLASS; relname | exist_lock -------------+------------ upart_table | f p2 | f p3 | f p1 | t (4 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE upart_table; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_lock; DROP SCHEMA
- DBE_STATS.LOCK_COLUMN_STATS
Locks the statistics of a column. After the column is locked, the statistics of the column cannot be updated. This API does not return any value.
The prototype of the DBE_STATS.LOCK_COLUMN_STATS function is as follows:
DBE_STATS.LOCK_COLUMN_STATS( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL );Table 4 DBE_STATS.LOCK_COLUMN_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the column to be locked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to which the column to be locked belongs.
colname
varchar2
No
Name of the column to be locked.
partname
varchar2
Yes
Name of the partition to which the column to be locked belongs. The default value is NULL.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- If partname is NULL, the list-level statistics are locked by default.
- If the column-level statistics of the table do not exist, the locking can be executed successfully but does not take effect.
- You can view stastate in PG_STATISTIC/PG_STATISTIC_EXT to obtain the lock status.
- Before locking multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If a column is locked or the table or partition to which the column belongs is locked, the statistics cannot be updated. The locked information about the column is not displayed. You need to view the locked information by using logging_module of DBE_STATS at the DEBUG2 level in logs.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_lock; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_lock; SET gaussdb=# CREATE TABLE t1(a int,b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(generate_series(1,100),1); INSERT 0 100 gaussdb=# ANALYZE t1; ANALYZE -- After locking a column, check the locking status of the column. gaussdb=# CALL DBE_STATS.LOCK_COLUMN_STATS(ownname=>'dbe_stats_lock',tabname=>'t1',colname=>'a'); lock_column_stats ------------------- (1 row) gaussdb=# SELECT staattnum,stastate FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stastate -----------+---------- 2 | u 1 | l (2 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_lock; DROP SCHEMA
- DBE_STATS.LOCK_SCHEMA_STATS
Locks statistics about all tables in a specified schema. This API does not return any value.
The prototype of the DBE_STATS.LOCK_SCHEMA_STATS function is as follows:
DBE_STATS.LOCK_SCHEMA_STATS( ownname VARCHAR2 );Table 5 DBE_STATS.LOCK_SCHEMA_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the specified schema. Null indicates that the current schema is used by default.
- When this API is called, all tables on which the current user has the ANALYZE permission in the schema are locked. Tables on which the current user does not have the ANALYZE permission are skipped.
- To call this API, you must have the USAGE permission on the specified schema.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of locking schema statistics is the same as that of locking table statistics. The following describes only the API calling method. For details, see the example of DBE_STATS.LOCK_TABLE_STA.... gaussdb=# CALL DBE_STATS.LOCK_SCHEMA_STATS(ownname=>'dbe_stats_lock'); lock_schema_stats ------------------- (1 row)
- DBE_STATS.UNLOCK_TABLE_STATS
Unlocks the statistics of a table. After the table is unlocked, the statistics of the table can be updated. The indexes, partitions, and columns involved in the table are unlocked synchronously. This API does not return any value.
The prototype of the DBE_STATS.UNLOCK_TABLE_STATS function is as follows:
DBE_STATS.UNLOCK_TABLE_STATS( ownname VARCHAR2, tabname VARCHAR2 );
Table 6 DBE_STATS.UNLOCK_TABLE_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the table to be unlocked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to be unlocked.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- You can check stat_state in reloptions of the table to obtain the lock status of the table.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of unlocking table statistics is the same as that of locking table statistics. The following describes only the API calling method. For details, see the DBE_STATS.LOCK_TABLE_STA... example. gaussdb=# CALL DBE_STATS.UNLOCK_TABLE_STATS(ownname=>'dbe_stats_lock',tabname=>'t1'); unlock_table_stats -------------------- (1 row)
- DBE_STATS.UNLOCK_PARTITION_STATS
Unlocks the statistics of a partition. After the partition is unlocked, the statistics of the partition can be updated. The indexes, partitions, and columns involved in the partition are unlocked synchronously. This API does not return any value.
The prototype of the DBE_STATS.UNLOCK_PARTITION_STATS function is as follows:
DBE_STATS.UNLOCK_PARTITION_STATS( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 );Table 7 DBE_STATS.UNLOCK_PARTITION_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the partition to be unlocked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to which the partition to be unlocked belongs.
partname
varchar2
No
Name of the partition to be unlocked.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- You can view stat_state in reloptions of the partition to obtain the lock status of the table.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of unlocking partition statistics is the same as that of locking partition statistics. The following describes only the API calling method. For details, see the DBE_STATS.LOCK_PARTIION_... example. gaussdb=# CALL DBE_STATS.UNLOCK_PARTITION_STATS(ownname=>'dbe_stats_lock',tabname=>'upart_table',partname=>'p1'); unlock_partition_stats ------------------------ (1 row)
- DBE_STATS.UNLOCK_COLUMN_STATS
Unlocks the statistics of a column. After being unlocked, the statistics of the column can be updated. This API does not return any value.
The prototype of the DBE_STATS.UNLOCK_COLUMN_STATS function is as follows:
DBE_STATS.LOCK_COLUMN_STATS( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL );Table 8 DBE_STATS.UNLOCK_COLUMN_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the schema to which the column to be unlocked belongs. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table to which the column to be unlocked belongs.
colname
varchar2
No
Name of the column to be unlocked.
partname
varchar2
Yes
Name of the partition to which the column to be unlocked belongs. The default value is NULL.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- If partname is NULL, the list-level statistics and column-level statistics of the cascaded partition are unlocked by default.
- You can view the stastate bit in PG_STATISTIC/PG_STATISTIC_EXT to obtain the lock status.
- Before unlocking multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of unlocking column statistics is the same as that of locking column statistics. The following describes only the API calling method. For details, see the DBE_STATS.LOCK_COLUMN_STATS example. gaussdb=# CALL DBE_STATS.UNLOCK_COLUMN_STATS(ownname=>'dbe_stats_lock',tabname=>'t1',colname=>'a'); unlock_column_stats --------------------- (1 row)
- DBE_STATS.UNLOCK_SCHEMA_STATS
Locks statistics about all tables in a specified schema. This API does not return any value.
The prototype of the DBE_STATS.UNLOCK_SCHEMA_STATS function is as follows:
DBE_STATS.UNLOCK_SCHEMA_STATS( ownname VARCHAR2 );Table 9 DBE_STATS.UNLOCK_SCHEMA_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
Yes
Name of the specified schema. Null indicates that the current schema is used by default.
- When this API is called, all tables on which the current user has the ANALYZE permission in the schema are unlocked. Tables on which the current user does not have the ANALYZE permission are skipped.
- To call this API, you must have the USAGE permission on the specified schema.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of unlocking schema statistics is the same as that of unlocking table statistics. The following describes only the API calling method. For details, see the DBE_STATS.LOCK_TABLE_STATS example. gaussdb=# CALL DBE_STATS.UNLOCK_SCHEMA_STATS(ownname=>'dbe_stats_lock'); unlock_schema_stats --------------------- (1 row)
- DBE_STATS.RESTORE_TABLE_STATS
Rolls back table statistics to a specified time point. The last statistics collected before the time point are loaded to the system catalog, and the statistics of the lower-level partitions, indexes, and columns that are not locked are cascaded. This API does not return any value.
The prototype of the DBE_STATS.RESTORE_TABLE_STATS function is as follows:
DBE_STATS.RESTORE_TABLE_STATS( ownname VARCHAR2, tabname VARCHAR2, as_of_timestamp TIMESTAMPTZ, restore_cluster_index BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT NULL );Table 10 DBE_STATS.RESTORE_TABLE_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
Yes
Name of the schema to which the table to be rolled back belongs. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Name of the table to be rolled back.
as_of_timestamp
timestamp with time zone
No
Target time point to be rolled back.
restore_cluster_index
BOOLEAN
Yes
Not supported currently.
force
BOOLEAN
Yes
Determines whether to forcibly roll back the statistics when the statistics are locked. The default value is FALSE.
no_invalidate
BOOLEAN
Yes
Not supported currently.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- When this API is called, the statistics of its lower-level partitions, indexes, and columns are rolled back at the same time. If the lower-level partitions or columns are locked, the statistics are skipped.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics twice. gaussdb=# CREATE SCHEMA dbe_stats_restore; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_restore; SET gaussdb=# CREATE TABLE t1(a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE -- View history tables. gaussdb=# SELECT relname,reltuples FROM GS_TABLESTATS_HISTORY WHERE relname='t1'; relname | reltuples ---------+----------- t1 | 3 t1 | 6 (2 rows) -- Display statistics in the current system catalog. gaussdb=# SELECT relname,reltuples FROM PG_CLASS WHERE relname='t1' AND relnamespace = (SELECT oid FROM PG_NAMESPACE WHERE nspname='dbe_stats_restore'); relname | reltuples ---------+----------- t1 | 6 (1 row) -- Roll back to the earliest statistics and check the system catalog. gaussdb=# CALL DBE_STATS.RESTORE_TABLE_STATS(ownname=>'dbe_stats_restore',tabname=>'t1',as_of_timestamp=>((SELECT MIN(reltimestamp) FROM GS_TABLESTATS_HISTORY WHERE relname='t1') + INTERVAL '1 second')); restore_table_stats --------------------- (1 row) gaussdb=# SELECT relname,reltuples FROM PG_CLASS WHERE relname='t1' AND relnamespace = (SELECT oid FROM PG_NAMESPACE WHERE nspname='dbe_stats_restore'); relname | reltuples ---------+----------- t1 | 3 (1 row) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_restore; DROP SCHEMA
- DBE_STATS.RESTORE_PARTITION_STATS
Rolls back partition statistics to a specified time point. The last statistics collected before the time point are loaded to the system catalog, and the statistics of the lower-level partitions, indexes, and columns that are not locked are cascaded. This API does not return any value.
The prototype of the DBE_STATS.RESTORE_PARTITION_STATS function is as follows:
DBE_STATS.RESTORE_PARTITION_STATS( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, as_of_timestamp TIMESTAMPTZ, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT NULL );Table 11 DBE_STATS.RESTORE_PARTITION_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
Yes
Name of the schema to which the partition to be rolled back belongs.
tabname
VARCHAR2
No
Name of the table to which the partition to be rolled back belongs.
partname
VARCHAR2
No
Name of the partition to be rolled back.
as_of_timestamp
timestamp with time zone
No
Target time point to be rolled back.
force
BOOLEAN
Yes
Determines whether to forcibly roll back the statistics when the statistics are locked.
no_invalidate
BOOLEAN
Yes
Not supported currently.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- When this API is called, the statistics of its lower-level partitions, indexes, and columns are rolled back.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of rolling back partition statistics is the same as that of rolling back table statistics. The following describes only the API calling method. For details, see the example of DBE_STATS.RESTORE_TABLE_STATS. gaussdb=# CALL DBE_STATS.RESTORE_PARTITION_STATS(ownname=>'dbe_stats_restore',tabname=>'t1',partname=>'p1',as_of_timestamp=>((SELECT MIN(reltimestamp) FROM GS_TABLESTATS_HISTORY WHERE relname='t1') + INTERVAL '1 second')); restore_partition_stats ------------------------- (1 row)
- DBE_STATS.RESTORE_COLUMN_STATS
Rolls back the column statistics to a specified time point. The last statistics collected before the time point to the system catalog are loaded. This API does not return any value.
The prototype of the DBE_STATS.RESTORE_COLUMN_STATS function is as follows:
RESTORE_COLUMN_STATS( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, as_of_timestamp TIMESTAMPTZ, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT NULL );Table 12 DBE_STATS.RESTORE_COLUMN_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
Yes
Name of the schema to which the column to be rolled back belongs.
tabname
VARCHAR2
No
Name of the table to which the column to be rolled back belongs.
colname
VARCHAR2
No
Name of the column to be rolled back.
partname
VARCHAR2
Yes
Name of the partition to which the column to be rolled back belongs.
as_of_timestamp
timestamp with time zone
No
Target time point to be rolled back.
force
BOOLEAN
Yes
Determines whether to forcibly roll back the statistics when the statistics are locked.
no_invalidate
BOOLEAN
Yes
Not supported currently.
- When using this API, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- If partname is NULL, the list-level statistics are rolled back by default.
- Before rolling back multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics twice. gaussdb=# CREATE SCHEMA dbe_stats_restore; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_restore; SET gaussdb=# CREATE TABLE t1(a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE -- View the statistics in the history table. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM GS_STATISTIC_HISTORY WHERE starelid='t1'::REGCLASS ORDER BY statimestamp; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+------------ 1 | -.333333 | 1 | {1} | {1} 2 | -.333333 | 1 | {1} | {1} 1 | -.333333 | 1 | {.5,.5} | {1,2} 2 | -.333333 | 1 | {.5,.5} | {1,2} (4 rows) -- Query statistics in the current system catalog. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+------------ 1 | -.333333 | 1 | {.5,.5} | {1,2} 2 | -.333333 | 1 | {.5,.5} | {1,2} (2 rows) -- Roll back to the earlier time point and query the statistics in the system catalog. gaussdb=# CALL DBE_STATS.RESTORE_COLUMN_STATS(ownname=>'dbe_stats_restore',tabname=>'t1',colname=>'a',as_of_timestamp=>((SELECT MIN(reltimestamp) FROM GS_TABLESTATS_HISTORY WHERE relname='t1') + INTERVAL '1 second')); restore_column_stats ---------------------- (1 row) gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+------------ 1 | -.333333 | 1 | {1} | {1} 2 | -.333333 | 1 | {.5,.5} | {1,2} (2 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_restore; DROP SCHEMA
- DBE_STATS.RESTORE_SCHEMA_STATS
Rolls back statistics of all tables in a specified schema to a specified time point. This API does not return any value.
The prototype of the DBE_STATS.RESTORE_SCHEMA_STATS function is as follows:
DBE_STATS.RESTORE_SCHEMA_STATS( ownname VARCHAR2, as_of_timestamp TIMESTAMPTZ, force BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT NULL );Table 13 DBE_STATS.RESTORE_SCHEMA_STATS API Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
Yes
Name of a schema.
as_of_timestamp
timestamp with time zone
No
Target time point to be rolled back.
force
BOOLEAN
Yes
Determines whether to forcibly roll back the statistics when the statistics are locked.
no_invalidate
BOOLEAN
Yes
Not supported currently.
- When this API is called, all tables on which the current user has the ANALYZE permission in the schema are rolled back. Tables on which the current user does not have the ANALYZE permission are skipped.
- To call this API, you must have the USAGE permission on the specified schema.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Roll back schema statistics, which is the same as rolling back table statistics. -- Create a table, insert data, and collect statistics twice. gaussdb=#CREATE SCHEMA dbe_stats_restore; CREATE SCHEMA gaussdb=#SET CURRENT_SCHEMA=dbe_stats_restore; SET gaussdb=#CREATE TABLE t1(a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=#INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=#INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=#ANALYZE t1; ANALYZE gaussdb=#INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=#INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=#INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=#ANALYZE t1; ANALYZE -- View the system catalog. gaussdb=#SELECT relname,reltuples FROM GS_TABLESTATS_HISTORY WHERE relname='t1'; relname | reltuples ---------+----------- t1 | 3 t1 | 6 (2 rows gaussdb=#SELECT relname,reltuples FROM PG_CLASS WHERE relname='t1' AND relnamespace = (SELECT oid FROM PG_NAMESPACE WHERE nspname='dbe_stats_restore'); relname | reltuples ---------+----------- t1 | 6 (1 row) -- Perform rollback. gaussdb=#CALL DBE_STATS.RESTORE_SCHEMA_STATS(ownname=>'dbe_stats_restore',as_of_timestamp=>((SELECT MIN(reltimestamp) FROM GS_TABLESTATS_HISTORY WHERE relname='t1') + INTERVAL '1 second')); restore_schema_stats ---------------------- (1 row) -- Check the system catalog again. gaussdb=#SELECT relname,reltuples FROM PG_CLASS WHERE relname='t1' AND relnamespace = (SELECT oid FROM PG_NAMESPACE WHERE nspname='dbe_stats_restore'); relname | reltuples ---------+----------- t1 | 3 (1 row) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_restore; DROP SCHEMA
- DBE_STATS.PURGE_STATS
Clears all historical statistics before a specified time point. This API does not return any value.
The prototype of the DBE_STATS.PURGE_STATS function is as follows:
DBE_STATS.PURGE_STATS( before_timestamp TIMESTAMPTZ );Table 14 DBE_STATS.PURGE_STATS API Parameter
Type
Whether NULL Is Allowed
Description
before_timestamp
timestamp with time zone
No
Time node for clearing.
Only the initial user can call this API.
Example:
-- Create a table and collect statistics twice. gaussdb=# CREATE SCHEMA dbe_stats_purge; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_purge; SET gaussdb=# CREATE TABLE t1(a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(1,1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES(2,2); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE -- View history tables. gaussdb=# SELECT relname,reltuples FROM GS_TABLESTATS_HISTORY WHERE relname='t1'; relname | reltuples ---------+----------- t1 | 3 t1 | 6 (2 rows) -- Clear the earlier history statistics and view the history table. gaussdb=# CALL DBE_STATS.PURGE_STATS(before_timestamp=>((SELECT MIN(reltimestamp) FROM GS_TABLESTATS_HISTORY WHERE relname='t1') + INTERVAL '1 second')); purge_stats ------------- (1 row) gaussdb=# SELECT relname,reltuples FROM GS_TABLESTATS_HISTORY WHERE relname='t1'; relname | reltuples ---------+----------- t1 | 6 (1 row) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_purge; DROP SCHEMA
- DBE_STATS.GET_STATS_HISTORY_RETENTION
Obtains the retention period of historical statistics. The retention period set by DBE_STATS.ALTER_STATS_HISTORY_RETENTION is returned. This API does not have input parameters.
The prototype of the DBE_STATS.GET_STATS_HISTORY_RETENTION function is as follows:
DBE_STATS.GET_STATS_HISTORY_RETENTION() returns NUMBER;
Example:
gaussdb=# CALL DBE_STATS.GET_STATS_HISTORY_RETENTION(); get_stats_history_retention ----------------------------- 31 (1 row) - DBE_STATS.GET_STATS_HISTORY_AVAILABILITY
Obtains the earliest available time of the current historical statistics. The earliest time of all historical statistics in the current database is returned. This API does not have input parameters.
The prototype of the DBE_STATS.GET_STATS_HISTORY_AVAILABILITY function is as follows:
DBE_STATS.GET_STATS_HISTORY_AVAILABILITY() returns TIMESTAMPTZ;
Example:
gaussdb=# ANALYZE; ANALYZE gaussdb=# CALL DBE_STATS.GET_STATS_HISTORY_AVAILABILITY(); get_stats_history_availability -------------------------------- 2023-08-27 02:07:04.065217+08 (1 row)
All the preceding function prototypes are the C_FUNCTION function prototypes with the same data types and columns, and are named DBE_STATS.XXXX_C_FUNCTION.
The DBE_STATS advanced package API is valid only for ordinary tables.
- DBE_STATS.CREATE_STAT_TABLE
Creates a statistics table for storing user statistics. This API does not return any value.
The prototype of the DBE_STATS.CREATE_STAT_TABLE function is as follows:
DBE_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL, global_temporary BOOLEAN DEFAULT NULL );Table 15 DBE_STATS.CREATE_STAT_TABLE parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the created statistics table is located. Null indicates that the schema is created in the current schema by default.
stattab
VARCHAR2
No
Name of a statistics table.
tblspace
VARCHAR2
Yes
Tablespace where the created statistics table is located. If not specified, the default tablespace is used.
global_temporary
BOOLEAN
Yes
This parameter is not supported currently.
- To call this procedure to create a statistics table, you must have the permission to create tables and indexes in the specified schema and tablespace.
- To distinguish ordinary tables from statistics tables, the statstable column in reloptions of a statistics table is not an empty string after the statistics table is created. Considering that the structure of the statistics table may be upgraded in the future, to implement forward compatibility, statstable is assigned the version information of the current statistics table.
- When this table is created, the gs_stattab_oid_statid_type_nameinfo_index index is automatically generated for index scanning. oid is the OID of the statistics table. If the index is deleted, the export and import APIs are unavailable.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a stattab statistics table in dbe_stats_create_drop_schema and manually insert a data record. You can query the data record in the new user statistics table. gaussdb=# CREATE SCHEMA dbe_stats_create_drop_schema; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_create_drop_schema; SET gaussdb=# CALL DBE_STATS.CREATE_STAT_TABLE(ownname => 'dbe_stats_create_drop_schema', stattab => 'stat_tab'); create_stat_table ------------------- (1 row) gaussdb=# INSERT INTO stat_tab(statid, type, relname) VALUES('123', 't', 't1'); INSERT 0 1 gaussdb=# SELECT statid, type, relname FROM stat_tab; statid | type | relname --------+------+--------- 123 | t | t1 (1 row)
- DBE_STATS.DROP_STAT_TABLE
Deletes the user statistics table. This API does not return any value.
The prototype of the DBE_STATS.DROP_STAT_TABLE function is as follows:
DBE_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2 );Table 16 DBE_STATS.DROP_STAT_TABLE parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the statistics table is located. Null indicates that the current schema is used by default.
stattab
VARCHAR2
No
Name of a statistics table.
- To call this procedure to delete a statistics table, you must have the permission to delete the table and index in the specified schema.
- Ordinary tables whose statstable attribute is empty cannot be deleted using this procedure.
- If an index is created on the user statistics table or other objects that depend on the table are created, all objects that depend on the table are deleted when this procedure is called.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Delete the created user statistics table and the new schema. gaussdb=# CALL DBE_STATS.DROP_STAT_TABLE(ownname => 'dbe_stats_create_drop_schema', stattab => 'stat_tab'); drop_stat_table ----------------- (1 row) gaussdb=# DROP SCHEMA dbe_stats_create_drop_schema CASCADE; DROP SCHEMA
- DBE_STATS.EXPORT_INDEX_STATS
Searches the system catalog for the statistics of the specified index according to and store the result in the user statistics table. This API does not return any value.
The prototype of the DBE_STATS.EXPORT_INDEX_STATS function is:
DBE_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL );
Table 17 DBE_STATS.EXPORT_INDEX_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema where the index is located. Null indicates that the current schema is used by default.
indname
VARCHAR2
No
Name of the index whose statistics are to be exported.
partname
VARCHAR2
Yes
Name of a partitioned index. If the index has been partitioned but partname is null, global and partitioned index statistics are exported.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
- To call this procedure to export index statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to export statistics to a user statistics table, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to export the reltuples (numrows), relpages (numlblks), and relallvisible (relallvisible) statistics of indexes.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_export; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_export; SET gaussdb=# CREATE TABLE t2(a int, b int, c int) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) ( SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) ) ); CREATE TABLE gaussdb=# CREATE INDEX idx_t2_local_a ON t2(a) LOCAL ( PARTITION p1_idx_a ( SUBPARTITION s1_idx_a, SUBPARTITION s2_idx_a ), PARTITION p2_idx_a ( SUBPARTITION s3_idx_a, SUBPARTITION s4_idx_a ), PARTITION p3_idx_a ( SUBPARTITION s5_idx_a, SUBPARTITION s6_idx_a )); CREATE INDEX gaussdb=# INSERT INTO t2 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 400, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 600, 1); INSERT 0 1 gaussdb=# ANALYZE t2; ANALYZE gaussdb=# ANALYZE t2((a,b)); ANALYZE -- Create a user statistics table as the target table for exporting statistics. gaussdb=# CALL DBE_STATS.CREATE_STAT_TABLE('dbe_stats_export', 'export_table'); create_stat_table ------------------- (1 row) -- Export index statistics together with partitioned index statistics. -- View the current statistics. gaussdb=# SELECT relpages,reltuples FROM PG_CLASS WHERE relname='idx_t2_local_a'; relpages | reltuples ----------+----------- 6 | 6 (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s1_idx_a | 1 | 0 s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 (6 rows) -- Export the statistics and view the data in the user statistics table. gaussdb=# CALL DBE_STATS.EXPORT_INDEX_STATS(ownname => 'dbe_stats_export', indname => 'idx_t2_local_a', partname=> null, stattab => 'export_table', statid => 'idx_s1'); export_index_stats -------------------- (1 row) gaussdb=# SELECT relname,partname,numrows,numblocks FROM export_table; relname | partname | numrows | numblocks ----------------+----------+---------+----------- idx_t2_local_a | | 6 | 6 idx_t2_local_a | s6_idx_a | 0 | 1 idx_t2_local_a | s5_idx_a | 0 | 1 idx_t2_local_a | s4_idx_a | 0 | 1 idx_t2_local_a | s3_idx_a | 0 | 1 idx_t2_local_a | s2_idx_a | 0 | 1 idx_t2_local_a | s1_idx_a | 0 | 1 (7 rows) -- Delete a table or namespace. gaussdb=# DROP INDEX idx_t2_local_a; DROP INDEX gaussdb=# DROP TABLE t2; DROP TABLE gaussdb=# DROP TABLE export_table; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_export; DROP SCHEMA
- DBE_STATS.EXPORT_TABLE_STATS
Retrieves statistics for the specified table and stores them in the user statistics table. This API does not return any value.
The prototype of the DBE_STATS.EXPORT_TABLE_STATS function is as follows:
DBE_STATS.EXPORT_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, stat_category VARCHAR2 DEFAULT NULL );
Table 18 DBE_STATS.EXPORT_TABLE_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Name of the table whose statistics are to be exported.
partname
VARCHAR2
Yes
Table partition name. If the table has been partitioned but partname is null, global and partitioned table statistics are exported.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
cascade
BOOLEAN
Yes
Specifies whether to export statistics about columns, indexes, multiple columns, and index expressions. The default value is TRUE.
statown
VARCHAR2
Yes
Not supported currently.
stat_category
VARCHAR2
Yes
Not supported currently.
- To call this procedure to export table statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to export statistics to a user statistics table, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to export the reltuples (numrows), relpages (numblks), and relallvisible (relallvisible) statistics of tables. For details about how to export column-level and index-level statistics in cascading mode, see the statistics of the EXPORT_INDEX_STATS and EXPORT_COLUMN_STATS APIs.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of exporting table statistics is the same as that of exporting index statistics. The following describes only the API calling method. For details, see the DBE_STATS.EXPORT_INDEX_STATS example. gaussdb=# CALL DBE_STATS.EXPORT_TABLE_STATS(ownname => 'dbe_stats_export', tabname => 't2', partname =>null, stattab => 'export_table', statid => 't1_t', cascade => true); export_table_stats -------------------- (1 row)
- DBE_STATS.EXPORT_COLUMN_STATS
Retrieves statistics for the specified columns (multiple columns or expressions) and stores them in the user statistics table. This API does not return any value.
The prototype of the DBE_STATS.EXPORT_COLUMN_STATS function is:
DBE_STATS.EXPORT_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL );
Table 19 DBE_STATS.EXPORT_COLUMN_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Name of the table (index name corresponding to the expression) to which the column whose statistics are to be exported belongs.
colname
VARCHAR2
No
Column name or multiple column names (If tabname is an index, colname is an expression name.)
partname
VARCHAR2
Yes
Partition name. If the table or index has been partitioned but partname is null, global and partitioned key, multiple columns, and index statistics are exported.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
- To call this procedure to export table statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to export statistics to a user statistics table, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You can query PG_STATISTIC or PG_STATISTIC_EXT to export column-level statistics.
- Before exporting the statistics of multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_export; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA=dbe_stats_export; SET gaussdb=# CREATE TABLE IF NOT EXISTS t1 (a int, b int, c int); CREATE TABLE gaussdb=# CREATE INDEX idx_t1_expr on t1((a*a), (a+a), (a*b)); CREATE INDEX gaussdb=# INSERT INTO t1 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (150, 550, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# ANALYZE t1((a,b)); ANALYZE -- Create a user statistics table. gaussdb=# CALL DBE_STATS.CREATE_STAT_TABLE('dbe_stats_export', 'export_table'); create_stat_table ------------------- (1 row) -- View column-level statistics. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+--------------- 1 | -1 | 2 | | {100,150,250} 2 | -1 | 2 | | {150,300,550} 3 | -.333333 | 1 | {1} | {1} (3 rows) -- Export column-level statistics and view the user statistics table. gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_export', tabname => 't1', colname=> 'a', partname => null, stattab => 'export_table', statid => 't1_c'); export_column_stats --------------------- (1 row) gaussdb=# SELECT relname,columnname,stadistinct,stakind1,stanumbers1,stavalues1 FROM export_table; relname | columnname | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+------------+-------------+----------+-------------+--------------- t1 | a | -1 | 2 | | {100,150,250} (1 row) -- View expression statistics. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='idx_t1_expr'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+--------------------- 1 | -1 | 2 | | {10000,22500,62500} 2 | -1 | 2 | | {200,300,500} 3 | -1 | 2 | | {30000,37500,82500} (3 rows) -- Export expression statistics and view the user statistics table. gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_export', tabname => 'idx_t1_expr', colname => 'expr', stattab => 'export_table', statid => 't1_expr'); export_column_stats --------------------- (1 row) gaussdb=# SELECT relname,columnname,stadistinct,stakind1,stanumbers1,stavalues1 FROM export_table where statid='t1_expr'; relname | columnname | stadistinct | stakind1 | stanumbers1 | stavalues1 -------------+------------+-------------+----------+-------------+--------------------- idx_t1_expr | expr | -1 | 2 | | {10000,22500,62500} (1 row) -- Export the statistics of multiple columns. -- View the statistics of multiple columns. gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+---------------------+-------------+----------+-------------+------------ 1 2 | extname_-1308656218 | -1 | 8 | | (1 row) -- Export and view the user statistics table. gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_export', tabname => 't1', colname=> 'extname_-1308656218', partname => null, stattab => 'export_table', statid => 't1_c'); export_column_stats --------------------- (1 row) gaussdb=# SELECT relname,columnname,stadistinct,stakind1,stanumbers1,stavalues1 FROM export_table; relname | columnname | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+---------------------+-------------+----------+-------------+--------------- t1 | a | -1 | 2 | | {100,150,250} t1 | extname_-1308656218 | -1 | 8 | | (2 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP TABLE export_table; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_export; DROP SCHEMA
- DBE_STATS.EXPORT_SCHEMA_STATS
Retrieves statistics for the specified schema and stores them in the user statistics table. This API does not return any value.
The prototype of the DBE_STATS.EXPORT_SCHEMA_STATS function is:
DBE_STATS.EXPORT_TABLE_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, stat_category VARCHAR2 DEFAULT NULL);
Table 20 DBE_STATS.EXPORT_SCHEMA_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
stat_category
VARCHAR2
Yes
Not supported currently.
- To call this procedure to export table statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to export statistics to a user statistics table, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to export statistics about all tables on which the current user has permission in the specified schema. Tables on which the current user does not have permission are skipped and no error is reported.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of exporting schema statistics is the same as that of exporting table statistics. The following describes only the API calling method. For details, see the DBE_STATS.EXPORT_TABLE_STATS example. gaussdb=# CALL DBE_STATS.EXPORT_SCHEMA_STATS(ownname => 'dbe_stats_export', stattab => 'export_table', statid => 's1'); export_schema_stats --------------------- (1 row)
- DBE_STATS.IMPORT_INDEX_STATS
Retrieves statistics for the specified index from the user statistics table of the stattab identifier and writes it back to the system catalog. This API does not return any value.
The prototype of the DBE_STATS.IMPORT_INDEX_STATS function is as follows:
DBE_STATS.IMPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE );
Table 21 DBE_STATS.IMPORT_INDEX_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
indname
VARCHAR2
No
Name of the index whose statistics are to be imported.
partname
VARCHAR2
Yes
Name of the index partition. If the index has been partitioned but partname is null, global and partitioned index statistics are imported.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
no_invalidate
BOOLEAN
Yes
Not supported currently.
force
BOOLEAN
Yes
Specifies whether to forcibly import data. The lock status is ignored. The default value is FALSE.
- To call this procedure to import statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to import statistics from a user statistics table to a system catalog, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to import the reltuples (numrows), relpages (numlblks), and relallvisible (relallvisible) statistics of indexes.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_import; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_import; SET gaussdb=# CREATE TABLE t2(a int, b int, c int) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) ( SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) ) ); CREATE TABLE gaussdb=# CREATE INDEX idx_t2_local_a ON t2(a) LOCAL ( PARTITION p1_idx_a ( SUBPARTITION s1_idx_a, SUBPARTITION s2_idx_a ), PARTITION p2_idx_a ( SUBPARTITION s3_idx_a, SUBPARTITION s4_idx_a ), PARTITION p3_idx_a ( SUBPARTITION s5_idx_a, SUBPARTITION s6_idx_a )); CREATE INDEX gaussdb=# INSERT INTO t2 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 400, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 600, 1); INSERT 0 1 gaussdb=# ANALYZE t2; ANALYZE -- Create a user statistics table and export the data. gaussdb=# CALL DBE_STATS.CREATE_STAT_TABLE('dbe_stats_import', 'export_table'); create_stat_table ------------------- (1 row) gaussdb=# CALL DBE_STATS.IMPORT_INDEX_STATS(ownname => 'dbe_stats_import', indname => 'idx_t2_local_a', partname=> null, stattab => 'export_table', statid => 'idx_s1'); export_index_stats -------------------- (1 row) gaussdb=# SELECT relname,partname,numrows,numblocks FROM export_table; relname | partname | numrows | numblocks ----------------+----------+---------+----------- idx_t2_local_a | | 6 | 6 idx_t2_local_a | s6_idx_a | 0 | 1 idx_t2_local_a | s5_idx_a | 0 | 1 idx_t2_local_a | s4_idx_a | 0 | 1 idx_t2_local_a | s3_idx_a | 0 | 1 idx_t2_local_a | s2_idx_a | 0 | 1 idx_t2_local_a | s1_idx_a | 0 | 1 (7 rows) -- Insert data again, collect statistics, and view the statistics. gaussdb=# INSERT INTO t2 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 400, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 600, 1); INSERT 0 1 gaussdb=# ANALYZE t2; ANALYZE gaussdb=# SELECT relpages,reltuples FROM PG_CLASS WHERE relname='idx_t2_local_a'; relpages | reltuples ----------+----------- 6 | 12 (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s1_idx_a | 1 | 0 s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 (6 rows) -- Import the statistics in the user statistics table to the statistics information system table. gaussdb=# CALL DBE_STATS.IMPORT_INDEX_STATS(ownname => 'dbe_stats_import', indname => 'idx_t2_local_a', stattab => 'export_table', statid => 'idx_s1'); import_index_stats -------------------- (1 row) gaussdb=# SELECT relpages,reltuples FROM PG_CLASS WHERE relname='idx_t2_local_a'; relpages | reltuples ----------+----------- 6 | 6 (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s1_idx_a | 1 | 0 s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 (6 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE t2; DROP TABLE gaussdb=# DROP TABLE export_table; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_import; DROP SCHEMA
- DBE_STATS.IMPORT_TABLE_STATS
Retrieves statistics for the specified table from the user statistics table of the stattab identifier and writes it back to the system catalog. This API does not return any value.
The prototype of the DBE_STATS.IMPORT_TABLE_STATS function is as follows:
DBE_STATS.IMPORT_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT NULL );
Table 22 DBE_STATS.IMPORT_TABLE_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
varchar2
No
Name of the schema. Null indicates that the current schema is used by default.
tabname
varchar2
No
Name of the table whose statistics are to be imported.
partname
varchar2
Yes
Table partition name. If the table has been partitioned but partname is null, global and partitioned table statistics are imported.
stattab
varchar2
No
Name of the user table that stores statistics.
statid
varchar2
Yes
(Optional) Identifier that points to statistics in stattab.
cascade
Boolean
Yes
Specifies whether to import statistics about columns, indexes, multiple columns, and index expressions. The default value is true.
statown
varchar2
Yes
Not supported currently.
no_invalidate
Boolean
Yes
Not supported currently.
force
Boolean
Yes
Specifies whether to forcibly import data. The lock status is ignored. The default value is FALSE.
stat_category
varchar2
Yes
Not supported currently.
- To call this procedure to import statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to import statistics from a user statistics table to a system catalog, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to import the reltuples (numrows), relpages (numblks), and relallvisible (relallvisible) statistics of tables. For details about column-level and index-level statistics imported in cascading mode, see the statistics of the IMPORT_INDEX_STATS and IMPORT_COLUMN_STATS APIs.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of importing table statistics is the same as that of importing index statistics. The following describes only the API calling method. For details, see the DBE_STATS.IMPORT_INDEX_STATS example. gaussdb=# CALL DBE_STATS.IMPORT_TABLE_STATS(ownname => 'dbe_stats_import', tabname => 't2', partname => 'p1', stattab => 'export_table', statid => 't1_t', cascade => true, force => false); import_table_stats -------------------- (1 row)
- DBE_STATS.IMPORT_COLUMN_STATS
Retrieves statistics for the specified columns (multiple columns or expressions) from the user statistics table of the stattab identifier and writes it back to the system catalog. This API does not return any value.
The prototype of the DBE_STATS.IMPORT_COLUMN_STATS function is as follows:
DBE_STATS.IMPORT_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE );
Table 23 DBE_STATS.IMPORT_COLUMN_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Name of the table (index name corresponding to the expression) to which the column whose statistics are to be imported belongs.
colname
VARCHAR2
No
Column name or multiple column names (If tabname is an index, colname is an expression name.)
partname
VARCHAR2
Yes
Partition name. If the table or index has been partitioned but partname is null, global and partitioned key, multiple columns, and index statistics are imported.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
no_invalidate
BOOLEAN
Yes
Not supported currently.
force
BOOLEAN
Yes
Specifies whether to forcibly import data. The lock status is ignored. The default value is FALSE.
- To call this procedure to import statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to import statistics from a user statistics table to a system catalog, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You can query PG_STATISTIC or PG_STATISTIC_EXT to import column-level statistics.
- Before importing multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_import; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_import; SET gaussdb=# CREATE TABLE IF NOT EXISTS t1 (a int, b int, c int); CREATE TABLE gaussdb=# CREATE INDEX idx_t1_expr on t1((a*a), (a+a), (a*b)); CREATE INDEX gaussdb=# INSERT INTO t1 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (150, 550, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# ANALYZE t1((a,b)); ANALYZE -- Create a user statistics table and export the statistics of a single column or multiple columns. gaussdb=# CALL DBE_STATS.CREATE_STAT_TABLE('dbe_stats_import', 'export_table'); create_stat_table ------------------- (1 row) gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+---------------------+-------------+----------+-------------+------------ 1 2 | extname_-1308656218 | -1 | 8 | | (1 row) gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 't1', colname=> 'a', partname => null, stattab => 'export_table', statid => 't1_c'); export_column_stats --------------------- (1 row) gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 'idx_t1_expr', colname => 'expr', stattab => 'export_table', statid => 't1_expr'); export_column_stats --------------------- (1 row) gaussdb=# CALL DBE_STATS.EXPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 't1', colname=> 'extname_-1308656218', partname => null, stattab => 'export_table', statid => 't1_c'); export_column_stats --------------------- (1 row) gaussdb=# SELECT relname,columnname,stadistinct,stakind1,stanumbers1,stavalues1 FROM export_table; relname | columnname | stadistinct | stakind1 | stanumbers1 | stavalues1 -------------+---------------------+-------------+----------+-------------+--------------------- idx_t1_expr | expr | -1 | 2 | | {10000,22500,62500} t1 | a | -1 | 2 | | {100,150,250} t1 | extname_-1308656218 | -1 | 8 | | (3 rows) -- Insert data again, collect statistics, and view the system catalog. gaussdb=# INSERT INTO t1 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (150, 550, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# ANALYZE t1((a,b)); ANALYZE -- View the column statistics before import. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+---------------------------+--------------- 1 | -.5 | 1 | {.333333,.333333,.333333} | {100,150,250} 2 | -.5 | 1 | {.333333,.333333,.333333} | {150,300,550} 3 | -.166667 | 1 | {1} | {1} (3 rows) -- View expression statistics before import. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='idx_t1_expr'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+---------------------------+--------------------- 1 | -.5 | 1 | {.333333,.333333,.333333} | {10000,22500,62500} 2 | -.5 | 1 | {.333333,.333333,.333333} | {200,300,500} 3 | -.5 | 1 | {.333333,.333333,.333333} | {30000,37500,82500} (3 rows) -- View the multi-column statistics before import. gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+---------------------+-------------+----------+-------------+------------ 1 2 | extname_-1308656218 | -.5 | 8 | | (1 row) -- Import single-column and multi-column statistics and view the system catalog. gaussdb=# CALL DBE_STATS.IMPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 't1', colname=> 'a', partname => null, stattab => 'export_table', statid => 't1_c', force => false); import_column_stats --------------------- (1 row) gaussdb=# CALL DBE_STATS.IMPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 'idx_t1_expr', colname=> 'expr', partname => null, stattab => 'export_table', statid => 't1_expr', force => false); import_column_stats --------------------- (1 row) gaussdb=# CALL DBE_STATS.IMPORT_COLUMN_STATS(ownname => 'dbe_stats_import', tabname => 't1', colname=> 'extname_-1308656218', partname => null, stattab => 'export_table', statid => 't1_c', force => false); import_column_stats --------------------- (1 row) -- View the column-level statistics after import. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+---------------------------+--------------- 1 | -1 | 2 | | {100,150,250} 2 | -.5 | 1 | {.333333,.333333,.333333} | {150,300,550} 3 | -.166667 | 1 | {1} | {1} (3 rows) -- View the expression-level statistics after import. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='idx_t1_expr'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+-------------+--------------------- 1 | -1 | 2 | | {10000,22500,62500} 2 | -1 | 2 | | {200,300,500} 3 | -1 | 2 | | {30000,37500,82500} (3 rows) -- View the multi-column statistics after import. gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+---------------------+-------------+----------+-------------+------------ 1 2 | extname_-1308656218 | -1 | 8 | | (1 row) -- Delete a table or namespace. gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP TABLE export_table; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_import; DROP SCHEMA
- DBE_STATS.IMPORT_SCHEMA_STATS
Retrieves statistics for the specified schema from the user statistics table of the stattab identifier and writes it back to the system catalog. This API does not return any value.
The prototype of the DBE_STATS.IMPORT_SCHEMA_STATS function is as follows:
DBE_STATS.IMPORT_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT NULL);
Table 24 DBE_STATS.IMPORT_SCHEMA_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Name of the schema. Null indicates that the current schema is used by default.
stattab
VARCHAR2
No
Name of the user table that stores statistics.
statid
VARCHAR2
Yes
(Optional) Identifier that points to statistics in stattab.
statown
VARCHAR2
Yes
Not supported currently.
no_invalidate
BOOLEAN
Yes
Not supported currently.
force
BOOLEAN
Yes
Specifies whether to forcibly import data. The lock status is ignored. The default value is FALSE.
stat_category
VARCHAR2
Yes
Not supported currently.
- To call this procedure to import statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- To call this procedure to import statistics from a user statistics table to a system catalog, you must have the SELECT, INSERT, DELETE, and UPDATE permissions on the user statistics table.
- You need to import statistics about all tables on which the current user has permission in the specified schema. Tables on which the current user does not have permission are skipped and no error is reported.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The method of importing schema statistics is the same as that of importing table statistics. This section describes only the API calling method. For details, see the DBE_STATS.IMPORT_TABLE_STATS example. gaussdb=# CALL DBE_STATS.IMPORT_SCHEMA_STATS(ownname => 'dbe_stats_import', stattab => 'export_table', statid => 's1',force => false); import_schema_stats --------------------- (1 row)
- DBE_STATS.SET_COLUMN_STATS
Sets column-related statistics, including single-column, multi-column, and expression statistics. This API does not return any value.
The prototype of the DBE_STATS.SET_COLUMN_STATS function is as follows:
DBE_STATS.SET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, distcnt NUMBER DEFAULT NULL, density NUMBER DEFAULT NULL, nullcnt NUMBER DEFAULT NULL, srec VARCHAR2 DEFAULT NULL, avgclen NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE );Table 25 DBE_STATS.SET_COLUMN_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the table is located. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Table name.
colname
VARCHAR2
No
Column name.
partname
VARCHAR2
Yes
Name of the table partition whose statistics are to be set. If the table has been partitioned and partname is null, global column–level statistics are set.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
distcnt
NUMBER
Yes
Number of NDVs in the column.
density
NUMBER
Yes
Not supported currently.
nullcnt
NUMBER
Yes
Number of null values in the column.
srec
VARCHAR2
Yes
This parameter is not supported currently.
avgclen
NUMBER
Yes
Average length (in bytes) of the fields in the column.
flags
NUMBER
Yes
This parameter is not supported currently.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
force
NUMBER
Yes
Behavior when the statistics are locked. If the value is true, the procedure sets the value even if the column-level statistics are locked. The default value is FALSE.
- To call this procedure to set column-level statistics for a table, you must have the same permissions as the ANALYZE statement on the tables involved in the column. For details, see ANALYZE | ANALYSE.
- This API can be used to set expression statistics. Expression creation depends on indexes. Therefore, when this API is called to set expression statistics, tabname must be set to the index name.
- Currently, only NDV (distcnt), nullfrac (nullcnt) and width (avgclen) statistics of columns can be set.
- After the statistics of a single column or expression are deleted, this API can still be called to set the statistics of the corresponding single column or expression. In this case, new data is inserted into the PG_STATISTIC system catalog. After the multi-column statistics are deleted, this API cannot be used to reset the statistics.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Prerequisites: Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_set_schema; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_set_schema; SET gaussdb=# DROP TABLE IF EXISTS t1; NOTICE: table "t1" does not exist, skipping DROP TABLE gaussdb=# CREATE TABLE IF NOT EXISTS t1 (a int, b int, c int); CREATE TABLE gaussdb=# CREATE INDEX idx_t1_expr ON t1((a*a), (a+a), (a*b)); CREATE INDEX gaussdb=# INSERT INTO t1 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (150, 550, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE gaussdb=# ANALYZE t1((a, b)); ANALYZE -- Set single-column statistics. -- Query the original statistics. gaussdb=# SELECT staattnum,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stanullfrac | stawidth | stadistinct -----------+-------------+----------+------------- 1 | 0 | 4 | -.75 2 | 0 | 4 | -.75 3 | 0 | 4 | -.25 (3 rows) -- Set the column-level statistics and query again. gaussdb=# CALL DBE_STATS.SET_COLUMN_STATS(ownname => 'dbe_stats_set_schema', tabname => 't1', colname => 'a', distcnt => 6, nullcnt => 5, avgclen => 42); set_column_stats ------------------ (1 row) gaussdb=# SELECT staattnum,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC WHERE starelid='t1'::REGCLASS; staattnum | stanullfrac | stawidth | stadistinct -----------+-------------+----------+------------- 1 | 1 | 42 | 6 2 | 0 | 4 | -.75 3 | 0 | 4 | -.25 (3 rows) -- Set multi-column statistics. -- Query the original statistics. gaussdb=# SELECT stakey,staextname,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stanullfrac | stawidth | stadistinct --------+---------------------+-------------+----------+------------- 1 2 | extname_-1308656218 | 0 | 8 | -.75 (1 row) -- Set the statistics and query again. gaussdb=# CALL DBE_STATS.SET_COLUMN_STATS(ownname => 'dbe_stats_set_schema', tabname => 't1', colname => 'extname_-1308656218', distcnt => 5, nullcnt => 5, avgclen => 2); set_column_stats ------------------ (1 row) gaussdb=# SELECT stakey,staextname,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC_EXT WHERE starelid='t1'::REGCLASS; stakey | staextname | stanullfrac | stawidth | stadistinct --------+---------------------+-------------+----------+------------- 1 2 | extname_-1308656218 | 1 | 2 | 5 (1 row) -- Set expression statistics. -- Query the original statistics. gaussdb=# SELECT staattnum,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC WHERE starelid='idx_t1_expr'::REGCLASS; staattnum | stanullfrac | stawidth | stadistinct -----------+-------------+----------+------------- 1 | 0 | 4 | -.75 2 | 0 | 4 | -.75 3 | 0 | 4 | -.75 (3 rows) -- Query an expression column name. gaussdb=# SELECT attnum, attname FROM PG_ATTRIBUTE WHERE attrelid='idx_t1_expr'::REGCLASS; attnum | attname --------+--------- 1 | expr 2 | expr1 3 | expr2 (3 rows) -- Set the statistics and query again. gaussdb=# CALL DBE_STATS.SET_COLUMN_STATS(ownname => 'dbe_stats_set_schema', tabname => 'idx_t1_expr', colname => 'expr', distcnt => 13, nullcnt => 5, avgclen => 22); set_column_stats ------------------ (1 row) gaussdb=# SELECT staattnum,stanullfrac,stawidth,stadistinct FROM PG_STATISTIC WHERE starelid='idx_t1_expr'::REGCLASS; staattnum | stanullfrac | stawidth | stadistinct -----------+-------------+----------+------------- 1 | 1 | 22 | 13 2 | 0 | 4 | -.75 3 | 0 | 4 | -.75 (3 rows) -- Delete a table or namespace. gaussdb=# DROP INDEX idx_t1_expr; DROP INDEX gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_set_schema cascade; DROP SCHEMA
- DBE_STATS.SET_INDEX_STATS
Sets index-related statistics. This API does not return any value.
The prototype of the DBE_STATS.SET_INDEX_STATS function is:
DBE_STATS.SET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows NUMBER DEFAULT NULL, numlblks NUMBER DEFAULT NULL, relallvisible NUMBER DEFAULT NULL, numdist NUMBER DEFAULT NULL, avglblk NUMBER DEFAULT NULL, avgdblk NUMBER DEFAULT NULL, clstfct NUMBER DEFAULT NULL, indlevel NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, guessq NUMBER DEFAULT NULL, cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFAULT NULL, force BOOLEAN DEFAULT FALSE );Table 26 DBE_STATS.SET_INDEX_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema to which the index belongs. Null indicates that the current schema is used by default.
indname
VARCHAR2
No
Index name.
partname
VARCHAR2
Yes
Name of the index partition whose statistics are to be set. If the index has been partitioned and partname is null, global index–level statistics are set.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
numrows
NUMBER
Yes
Number of rows in an index.
numlblks
NUMBER
Yes
Number of pages in an index.
relallvisible
NUMBER
Yes
Number of pages marked as all visible in the index.
numdist
NUMBER
Yes
This parameter is not supported currently.
avglblk
NUMBER
Yes
This parameter is not supported currently.
avgdblk
NUMBER
Yes
This parameter is not supported currently.
clstfct
NUMBER
Yes
This parameter is not supported currently.
indlevel
NUMBER
Yes
This parameter is not supported currently.
flags
NUMBER
Yes
This parameter is not supported currently.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
guessq
NUMBER
Yes
This parameter is not supported currently.
cachedblk
NUMBER
Yes
This parameter is not supported currently.
cachehit
NUMBER
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is TRUE, the procedure sets the value even if the index-level statistics are locked. The default value is FALSE.
- To call this procedure to set index statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- Currently, only reltuples (numrows), relpages (numlblks) and relallvisible (relallvisible) statistics of indexes can be set.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Prerequisites: Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_set_schema; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_set_schema; SET gaussdb=# DROP TABLE IF EXISTS t1; NOTICE: table "t1" does not exist, skipping DROP TABLE gaussdb=# CREATE TABLE IF NOT EXISTS t1 (a int, b int, c int); CREATE TABLE gaussdb=# CREATE INDEX idx_t1_a on t1(a); CREATE INDEX gaussdb=# INSERT INTO t1 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (150, 550, 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (250, 150, 1); INSERT 0 1 gaussdb=# ANALYZE t1; ANALYZE -- Set index statistics. -- View the current index statistics. gaussdb=# SELECT reltuples,relpages,relallvisible FROM PG_CLASS WHERE relname='idx_t1_a'; reltuples | relpages | relallvisible -----------+----------+--------------- 3 | 2 | 0 (1 row) -- Set the statistics and query again. gaussdb=# CALL DBE_STATS.SET_INDEX_STATS(indname => 'idx_t1_a', ownname => 'dbe_stats_set_schema', numrows => 1, numlblks => 3, relallvisible => 6); set_index_stats ----------------- (1 row) gaussdb=# SELECT reltuples,relpages,relallvisible FROM PG_CLASS WHERE relname='idx_t1_a'; reltuples | relpages | relallvisible -----------+----------+--------------- 1 | 3 | 6 (1 row) -- Delete a table or namespace. gaussdb=# DROP INDEX idx_t1_a; DROP INDEX gaussdb=# DROP TABLE t1; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_set_schema CASCADE; DROP SCHEMA
- DBE_STATS.SET_TABLE_STATS
Sets table-level statistics. This API does not return any value.
The prototype of the DBE_STATS.SET_TABLE_STATS function is as follows:
DBE_STATS.SET_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows NUMBER DEFAULT NULL, numblks NUMBER DEFAULT NULL, relallvisible NUMBER DEFAULT NULL, avgrlen NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFAULT NULL, force BOOLEAN DEFAULT FALSE, im_imcu_count NUMBER DEFAULT NULL, im_block_count NUMBER DEFAULT NULL, scanrate NUMBER DEFAULT NULL );Table 27 DBE_STATS.SET_TABLE_STATS parameters Parameter
Parameter
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the table is located. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Table name.
partname
VARCHAR2
Yes
Name of the table partition whose statistics are to be set. If the table has been partitioned and partname is null, global table–level statistics are set.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
numrows
NUMBER
Yes
Number of rows in a table.
numblks
NUMBER
Yes
Number of pages in a table.
relallvisible
NUMBER
Yes
Number of pages marked as all visible in a table.
avgrlen
NUMBER
Yes
This parameter is not supported currently.
flags
NUMBER
Yes
This parameter is not supported currently.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
cachedblk
NUMBER
Yes
This parameter is not supported currently.
cachehit
NUMBER
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is TRUE, the procedure sets the value even if the table statistics are locked. The default value is FALSE.
im_imcu_count
NUMBER
Yes
This parameter is not supported currently.
im_block_count
NUMBER
Yes
This parameter is not supported currently.
scanrate
NUMBER
Yes
This parameter is not supported currently.
- To call this procedure to set table statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- Currently, only reltuples (numrows), relpages (numblks) and relallvisible (relallvisible) statistics of tables can be set.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The procedure for setting index statistics is similar to that for setting index statistics. The following describes only the API calling method. For details, see the DBE_STATS.SET_INDEX_STATS API example. -- Set table-level statistics. gaussdb=# CALL DBE_STATS.SET_TABLE_STATS(tabname => 't1', ownname => 'dbe_stats_set_schema', numrows => 1, numblks => 3, relallvisible => 6); set_table_stats ----------------- (1 row)
- DBE_STATS.DELETE_COLUMN_STATS
Deletes column-related statistics, including single-column, multi-column, and expression statistics. This API does not return any value.
The prototype of the DBE_STATS.DELETE_COLUMN_STATS function is as follows:
DBE_STATS.DELETE_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, col_stat_type VARCHAR2 DEFAULT NULL );Table 28 DBE_STATS.DELETE_COLUMN_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the table is located. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Table name.
colname
VARCHAR2
No
Column name.
partname
VARCHAR2
Yes
Name of the table partition whose statistics are to be deleted. If the table has been partitioned and partname is null, global column–level statistics are deleted. If cascade_parts is true, column-level statistics of all partitions are deleted.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
cascade_parts
BOOLEAN
Yes
Specifies whether to delete the statistics of all partitions under a column when deleting the column-level statistics. The default value is true.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is true, the procedure deletes the column statistics even if they are locked. The default value is false.
col_stat_type
VARCHAR2
Yes
This parameter is not supported currently.
- To call this procedure to delete column statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- This API can be used to delete expression statistics. Expression creation depends on indexes. Therefore, when this API is called to delete expression statistics, tabname must be set to the index name.
- If the table is a level-2 partitioned table and partname is set to the level-1 partition name, and cascade_parts is set to true, statistics on all level-2 partitions under the level-1 partition will be deleted.
- Before deleting the statistics of multiple columns, query the staextname column in PG_STATISTIC_EXT to obtain the aliases of multiple columns and transfer them as the input parameter colname.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Prerequisites: Create a table and collect statistics. gaussdb=# CREATE SCHEMA dbe_stats_delete_schema; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_delete_schema; SET gaussdb=# DROP TABLE IF EXISTS t2; NOTICE: table "t2" does not exist, skipping DROP TABLE gaussdb=# CREATE TABLE t2(a int, b int, c int) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) ( SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) )); CREATE TABLE gaussdb=# INSERT INTO t2 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 400, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 600, 1); INSERT 0 1 gaussdb=# ANALYZE t2; ANALYZE gaussdb=# ANALYZE t2((a, b)); ANALYZE -- Delete the single-column statistics of table t2 and do not cascade partitions. The partition statistics still exist. -- View statistics about table-level columns. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t2'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+---------------------------+--------------- 1 | -.5 | 1 | {.333333,.333333,.333333} | {100,300,600} 2 | -.5 | 1 | {.5,.333333} | {600,300} 3 | -.166667 | 1 | {1} | {1} (3 rows) -- View statistics about partition-level columns. gaussdb=# SELECT part.relname,s.staattnum,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid; relname | staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+-----------+-------------+----------+-------------+------------ p1 | 1 | -1 | 1 | {1} | {100} p1 | 2 | -1 | 2 | | {300,600} p1 | 3 | -1 | 1 | {1} | {1} p2 | 1 | -1 | 1 | {1} | {300} p2 | 2 | -1 | 2 | | {300,600} p2 | 3 | -1 | 1 | {1} | {1} p3 | 1 | -1 | 1 | {1} | {600} p3 | 2 | -1 | 2 | | {400,600} p3 | 3 | -1 | 1 | {1} | {1} (9 rows) -- Delete the table-level single-column statistics and do not cascade partitions. The table-level statistics do not exist, and the partition-level column statistics still exist. gaussdb=# CALL DBE_STATS.DELETE_COLUMN_STATS(ownname => 'dbe_stats_delete_schema', tabname => 't2', colname => 'a', partname => null, cascade_parts => false); delete_column_stats --------------------- (1 row) gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t2'::REGCLASS; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+--------------+------------ 2 | -.5 | 1 | {.5,.333333} | {600,300} 3 | -.166667 | 1 | {1} | {1} (2 rows) gaussdb=# SELECT part.relname,s.staattnum,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid; relname | staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+-----------+-------------+----------+-------------+------------ p1 | 1 | -1 | 1 | {1} | {100} p1 | 2 | -1 | 2 | | {300,600} p1 | 3 | -1 | 1 | {1} | {1} p2 | 1 | -1 | 1 | {1} | {300} p2 | 2 | -1 | 2 | | {300,600} p2 | 3 | -1 | 1 | {1} | {1} p3 | 1 | -1 | 1 | {1} | {600} p3 | 2 | -1 | 2 | | {400,600} p3 | 3 | -1 | 1 | {1} | {1} (9 rows) -- Delete the multi-column statistics of table t2 and cascade partitions. Both the column-level statistics of cascaded partitions and partitions are deleted. -- Query statistics. gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t2'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+---------------------+-------------+----------+-------------+------------ 1 2 | extname_-1308656218 | -1 | 8 | | (1 row) gaussdb=# SELECT part.relname,s.stakey,s.staextname,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC_EXT s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid; relname | stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+--------+---------------------+-------------+----------+-------------+------------ p1 | 1 2 | extname_-1308656218 | -1 | 8 | | p2 | 1 2 | extname_-1308656218 | -1 | 8 | | p3 | 1 2 | extname_-1308656218 | -1 | 8 | | (3 rows) -- Delete the multi-column statistics and query the statistics. gaussdb=# CALL DBE_STATS.DELETE_COLUMN_STATS(ownname => 'dbe_stats_delete_schema', tabname => 't2', colname => 'extname_-1308656218', partname => null, cascade_parts => true); delete_column_stats --------------------- (1 row) gaussdb=# SELECT stakey,staextname,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC_EXT WHERE starelid='t2'::REGCLASS; stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 --------+------------+-------------+----------+-------------+------------ (0 rows) gaussdb=# SELECT part.relname,s.stakey,s.staextname,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC_EXT s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid; relname | stakey | staextname | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+--------+------------+-------------+----------+-------------+------------ (0 rows) -- Delete the column-level statistics of the p1 partition. The table-level, p2, and p3 column statistics are not affected. -- Query statistics. gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t2'::REGCLASS and staattnum=2; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+--------------+------------ 2 | -.5 | 1 | {.5,.333333} | {600,300} (1 row) gaussdb=# SELECT part.relname,s.staattnum,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid and s.staattnum=2; relname | staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+-----------+-------------+----------+-------------+------------ p1 | 2 | -1 | 2 | | {300,600} p2 | 2 | -1 | 2 | | {300,600} p3 | 2 | -1 | 2 | | {400,600} (3 rows) -- Delete the column statistics of the p1 partition and query the statistics. gaussdb=# CALL DBE_STATS.DELETE_COLUMN_STATS(ownname => 'dbe_stats_delete_schema', tabname => 't2', colname => 'b', partname => 'p1', cascade_parts => false); delete_column_stats --------------------- (1 row) gaussdb=# SELECT staattnum,stadistinct,stakind1,stanumbers1,stavalues1 FROM PG_STATISTIC WHERE starelid='t2'::REGCLASS and staattnum=2; staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 -----------+-------------+----------+--------------+------------ 2 | -.5 | 1 | {.5,.333333} | {600,300} (1 row) gaussdb=# SELECT part.relname,s.staattnum,s.stadistinct,s.stakind1,s.stanumbers1,s.stavalues1 FROM PG_STATISTIC s,PG_PARTITION part WHERE part.parentid='t2'::REGCLASS and s.starelid=part.oid and s.staattnum=2; relname | staattnum | stadistinct | stakind1 | stanumbers1 | stavalues1 ---------+-----------+-------------+----------+-------------+------------ p2 | 2 | -1 | 2 | | {300,600} p3 | 2 | -1 | 2 | | {400,600} (2 rows) -- Delete a table or namespace. gaussdb=# DROP TABLE t2; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_delete_schema CASCADE; DROP SCHEMA
- DBE_STATS.DELETE_INDEX_STATS
Deletes index-related statistics. This API does not return any value.
The prototype of the DBE_STATS.DELETE_INDEX_STATS function is:
DBE_STATS.DELETE_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, stattype VARCHAR2 DEFAULT NULL, force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT NULL )Table 29 DBE_STATS.DELETE_INDEX_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema to which the index belongs. Null indicates that the current schema is used by default.
indname
VARCHAR2
No
Index name.
partname
VARCHAR2
Yes
Name of the index partition whose statistics are to be deleted. If the index has been partitioned and partname is null, global index–level statistics are deleted. If cascade_parts is true, index-level statistics of all partitions are deleted.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
cascade_parts
BOOLEAN
Yes
Specifies whether to delete the statistics of all partitions under an index when deleting the index-level statistics. The default value is true.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
stattype
VARCHAR2
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is true, the procedure deletes the index statistics even if they are locked. The default value is false.
stat_category
VARCHAR2
Yes
This parameter is not supported currently.
- To call this procedure to delete index statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- Deleting index statistics is equivalent to set reltuples, relpages, and relallvisible to 0.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Prerequisites: Create a table. gaussdb=# CREATE SCHEMA dbe_stats_delete_schema; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = dbe_stats_delete_schema; SET gaussdb=# DROP TABLE IF EXISTS t2; NOTICE: table "t2" does not exist, skipping DROP TABLE gaussdb=# CREATE TABLE t2(a int, b int, c int) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) ( SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) )); CREATE TABLE gaussdb=# CREATE INDEX idx_t2_local_a ON t2(a) LOCAL ( PARTITION p1_idx_a ( SUBPARTITION s1_idx_a, SUBPARTITION s2_idx_a ), PARTITION p2_idx_a ( SUBPARTITION s3_idx_a, SUBPARTITION s4_idx_a ), PARTITION p3_idx_a ( SUBPARTITION s5_idx_a, SUBPARTITION s6_idx_a )); CREATE INDEX gaussdb=# INSERT INTO t2 VALUES (100, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (100, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 300, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (300, 600, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 400, 1); INSERT 0 1 gaussdb=# INSERT INTO t2 VALUES (600, 600, 1); INSERT 0 1 gaussdb=# ANALYZE t2; ANALYZE gaussdb=# ANALYZE t2((a, b)); ANALYZE -- Delete the statistics of the idx_t2_local_a index without cascading the statistics of the partitioned index. -- View statistics. gaussdb=# SELECT relpages,reltuples FROM PG_CLASS WHERE relname='idx_t2_local_a'; relpages | reltuples ----------+----------- 6 | 6 (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s1_idx_a | 1 | 0 s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 (6 rows) -- Delete statistics and query the system catalog. gaussdb=# CALL DBE_STATS.DELETE_INDEX_STATS(ownname => 'dbe_stats_delete_schema', indname => 'idx_t2_local_a', partname => null, cascade_parts => false); delete_index_stats -------------------- (1 row) gaussdb=# SELECT relpages,reltuples FROM PG_CLASS WHERE relname='idx_t2_local_a'; relpages | reltuples ----------+----------- 0 | 0 (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s1_idx_a | 1 | 0 s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 (6 rows) -- Delete statistics about the s1_idx_a partition of the idx_t2_local_a index. gaussdb=# CALL DBE_STATS.DELETE_INDEX_STATS(ownname => 'dbe_stats_delete_schema', indname => 'idx_t2_local_a', partname => 's1_idx_a', cascade_parts => false); delete_index_stats -------------------- (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s2_idx_a | 1 | 0 s3_idx_a | 1 | 0 s4_idx_a | 1 | 0 s5_idx_a | 1 | 0 s6_idx_a | 1 | 0 s1_idx_a | 0 | 0 (6 rows) -- Delete index statistics of all partitions in cascading mode. gaussdb=# CALL DBE_STATS.DELETE_INDEX_STATS(ownname => 'dbe_stats_delete_schema', indname => 'idx_t2_local_a', partname => null, cascade_parts => true); delete_index_stats -------------------- (1 row) gaussdb=# SELECT relname,relpages,reltuples FROM PG_PARTITION WHERE parentid='idx_t2_local_a'::REGCLASS; relname | relpages | reltuples ----------+----------+----------- s6_idx_a | 0 | 0 s5_idx_a | 0 | 0 s4_idx_a | 0 | 0 s3_idx_a | 0 | 0 s2_idx_a | 0 | 0 s1_idx_a | 0 | 0 (6 rows) -- Delete a table or namespace. gaussdb=# DROP INDEX idx_t2_local_a; DROP INDEX gaussdb=# DROP TABLE t2; DROP TABLE gaussdb=# DROP SCHEMA dbe_stats_delete_schema CASCADE; DROP SCHEMA
- DBE_STATS.DELETE_TABLE_STATS
Deletes table-level statistics. This API does not return any value.
The prototype of the DBE_STATS.DELETE_TABLE_STATS function is:
DBE_STATS.DELETE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, cascade_columns BOOLEAN DEFAULT TRUE, cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT NULL )Table 30 DBE_STATS.DELETE_TABLE_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema where the table is located. Null indicates that the current schema is used by default.
tabname
VARCHAR2
No
Table name.
partname
VARCHAR2
Yes
Name of the table partition whose statistics are to be deleted. If the table has been partitioned and partname is null, global table–level statistics are deleted. If cascade_parts is true, table-level statistics of all partitions are deleted.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
cascade_parts
BOOLEAN
Yes
Specifies whether to delete the statistics of all table partitions when deleting the table-level statistics. The default value is true.
cascade_columns
BOOLEAN
Yes
Specifies whether to call delete_column_stats to delete all column-level statistics of the table. The default value is true.
cascade_indexes
BOOLEAN
Yes
Specifies whether to call delete_index_stats to delete all index-level statistics of the table. The default value is true.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is true, the procedure deletes the table statistics even if they are locked. The default value is false.
stat_category
VARCHAR2
Yes
This parameter is not supported currently.
- To call this procedure to delete table statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- If cascade_columns or cascade_indexes is set to true, cascade_parts also determines whether to delete the statistics of the corresponding partition key or index in cascading mode. For example, if cascade_parts is set to true, not only table-level statistics of all partitions of a partitioned table are deleted, but also column-level and index-level statistics of all partitions are deleted.
- Deleting table statistics is equivalent to clearing the information of reltuples, relpages, and relallvisible.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- The procedure for deleting table-level index statistics is similar to that for deleting index statistics. The following describes only the API calling method. For details, see the DBE_STATS.DELETE_INDEX_STATS example. -- Delete table-level statistics, including all column and index statistics of a table. gaussdb=# CALL DBE_STATS.DELETE_TABLE_STATS(ownname => 'dbe_stats_delete_schema', tabname => 't2', cascade_columns => true, cascade_indexes => true); delete_table_stats -------------------- (1 row)
- DBE_STATS.DELETE_SCHEMA_STATS
Deletes all table-level, index-level, and column-level statistics in a schema. This API does not return any value.
The prototype of the DBE_STATS.DELETE_SCHEMA_STATS function is:
DBE_STATS.DELETE_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT NULL, force BOOLEAN DEFAULT FALSE, stat_category VARCHAR2 DEFAULT NULL );Table 31 DBE_STATS.DELETE_SCHEMA_STATS parameters Parameter
Type
Whether NULL Is Allowed
Description
ownname
VARCHAR2
No
Schema name. Null indicates that the current schema is used by default.
stattab
VARCHAR2
Yes
This parameter is not supported currently.
statid
VARCHAR2
Yes
This parameter is not supported currently.
statown
VARCHAR2
Yes
This parameter is not supported currently.
no_invalidate
BOOLEAN
Yes
This parameter is not supported currently.
force
BOOLEAN
Yes
Behavior when the statistics are locked. If the value is true, the procedure deletes the statistics even if they are locked. The default value is false.
stat_category
VARCHAR2
Yes
This parameter is not supported currently.
- To call this procedure to delete statistics, you must have the same permission on the specified table as that of the ANALYZE statement. For details, see ANALYZE | ANALYSE.
- If you delete statistics in a schema, that is, delete all tables on which the current user has permission in the specified schema, tables on which the current user does not have permission are skipped and no error is reported.
- If ownname is set to null, the current schema is used. In this case, you need to set behavior_compat_options to bind_procedure_searchpath.
Example:
-- Only API calling is displayed here. For details about the implementation cases, see DBE_STATS.DELETE_TABLE_STATS. gaussdb=# CALL DBE_STATS.DELETE_SCHEMA_STATS(ownname => 'dbe_stats_delete_schema'); delete_schema_stats --------------------- (1 row)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
