The statistics setting feature allows you to explicitly configure basic table-level and column-level statistics.
Description
Settings on column-level histogram statistics are not supported.
OceanBase Database supports the following statistics setting methods:
Use the stored procedure
set_table_statsto set table-level statistics.Use the stored procedure
set_column_statsto set column-level statistics.
The stored procedures are defined as follows:
PROCEDURE 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,
avgrlen NUMBER DEFAULT NULL,
flags NUMBER DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
cachedblk NUMBER DEFAULT NULL,
cachehit NUMBER DEFAULT NULL,
force BOOLEAN DEFAULT FALSE
);
PROCEDURE 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,
epc NUMBER DEFAULT NULL,
minval RAW DEFAULT NULL,
maxval RAW DEFAULT NULL,
bkvals NUMARRAY DEFAULT NULL,
novals NUMARRAY DEFAULT NULL,
chvals CHARARRAY DEFAULT NULL,
eavals RAWARRAY DEFAULT NULL,
rpcnts NUMARRAY DEFAULT NULL,
eavs NUMBER DEFAULT NULL,
avgclen NUMBER DEFAULT NULL,
flags NUMBER DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE
);
The following table describes the parameters.
| Parameter | Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current logon username is used by default. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| colname | The name of the column. |
| numrows | The total number of rows at the table level. |
| avgrlen | The average length at the row level. |
| distcnt | The number of distinct values (NDV) value at the column level. |
| density | The density at the column level. |
| nullcnt | The number of NULL values at the column level. |
| avgclen | The average length at the column level. |
| force | Specifies whether to perform forced deletion and ignore the lock status. Default value: FALSE. |
The following example shows how to set the statistics:
Set the total number of rows to 10,000 for table
tbl1under useruser1.CALL dbms_stats.set_table_stats('user1', 'tbl1', numrows=>10000);For the
col1column in tabletbl1under useruser1, set the NDV to 10 and the number ofNULLvalues to 10.CALL dbms_stats.set_column_stats('user1', 'tbl1', 'col1', distcnt=>10, nullcnt=>10);