How to set statistics for Table/index/column in Oracle

Sometimes, you want to modify the statistics for testing purposes or some other things. Oracle has provided a standard procedure to set the statistics for the Table/index/column. In this post, we will check out how to do it

How to set statistics for Table

This is done using the SET_TABLE_STATS procedure of the DBMS_STATS package

DBMS_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,
   avgrlen        NUMBER   DEFAULT NULL,
   flags          NUMBER   DEFAULT NULL,
   statown        VARCHAR2 DEFAULT NULL,
   no_invalidate  BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   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);

• Here statid, and stattab are relevant if you are setting statistics in the user statistics table. The default is NULL and it sets the stats in the dictionary
• numrows: The number of rows in the table or partition.
• numblks: Number of blocks that the table or partition occupies.
• avgrlen: Average row length for the table or partition.
• no_validate: Controls the invalidation of dependent cursors when statistics are gathered

Example

exec dbms_stats.SET_TABLE_STATS( -
OWNNAME => 'SCOTT',
TABNAME => 'EMP_DATA',
NUMROWS => 10000,
NUMBLKS => 1200,
AVGRLEN => 10,
NO_INVALIDATE => FALSE
);

How to set statistics for Index

This is done using the SET_INDEX_STATS procedure of the DBMS_STATS package

DBMS_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,
   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 to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   guessq        NUMBER    DEFAULT NULL,
   cachedblk     NUMBER    DEFAULT NULL,
   cachehit      NUMBER    DEFUALT NULL,
   force         BOOLEAN   DEFAULT FALSE);

• Here statid, and stattab are relevant if you are setting statistics in the user statistics table. The default is NULL and it sets the stats in the dictionary
• numrows: Number of rows in the index (partition)
• numlblks: Number of leaf blocks in the index (partition)
• numdist: Number of distinct keys in the index (partition)
• avglblk: Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from numlblks and numdist.
• avgdblk: Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from clstfct and numdist.
• clstfct: Set clustering_factor
• indlevel: Height of the index (partition)

Example

exec dbms_stats.set_index_stats(
OWNNAME=>'SCOTT',
INDNAME=>'EMP_DATA_N1',
NUMDIST=>1000000)
INDLEVEL=>5);

How to set statistics for column

This is done using the SET_COLUMN_STATS procedure of the DBMS_STATS package

DBMS_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          StatRec DEFAULT NULL,
   avgclen       NUMBER DEFAULT NULL,
   flags         NUMBER DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);

• Here statid, and stattab are relevant if you are setting statistics in the user statistics table. The default is NULL and it sets the stats in the dictionary
• distcnt: Number of distinct values
• density: Column density. If this value is NULL and if distcnt is not NULL, then density is derived from distcnt.
• nullcnt: Number of NULLs
• avgclen: Average length for the column (in bytes)

Example

exec dbms_stats.SET_COLUMN_STATS( 
OWNNAME => 'SCOTT',
TABNAME => 'EMP_DATA',
COLNAME => 'EMP_NAME',
DISTCNT => 1000,
DENSITY => 0.22, -
NULLCNT => 20, -
AVGCLEN => 10);

I hope you like this post on How to set statistics for table in Oracle, How to set statistics for index in Oracle, How to set statistics for columns in Oracle

Related Articles

How to import statistics in Oracle
How to export statistics in Oracle
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to delete statistics from Table in Oracle