Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 28, 2003

Oracle Optimizer: Moving to and working with CBO - Part 4 - Page 4

By Amar Kumar Padhi

9.4.2) Deleting statistics with DBMS_STATS


DBMS_STATS.DELETE_TABLE_STATS deletes table statistics.

Call Syntax

	(ownname, tabname, partname, 
	stattab, statid, cascade_parts, 
	cascade_columns, cascade_indexes, 

  • cascade_parts - delete statistics for all partitions (partname should be null).
  • cascade_columns - delete column statistics. Default is true.
  • cascade_indexes - delete index statistics. Default is true.

e.g.: Delete statistics for a table and its columns and indexes.

orAP>exec dbms_stats.delete_table_stats(ownname => 'APPS', tabname => 'AM21');
PL/SQL procedure successfully completed.

e.g.: Delete statistics for table only. Column and index statistics will be preserved.

orAP>exec dbms_stats.delete_table_stats(ownname => 'APPS', tabname => 'AM21', -
> cascade_columns => false, cascade_indexes => false);
PL/SQL procedure successfully completed.


DBMS_STATS.DELETE_COLUMN_STATS deletes individual column statistics.

Call Syntax

dbms_stats.delete_column_stats(ownname, tabname, colname, partname, stattab,
	statid, cascade_parts, statown);

e.g.: Deleting statistics for one column.

orAP>exec dbms_stats.delete_column_stats(ownname => 'APPS', tabname => 'AM21', -
> colname => 'DESCRIPTION');
PL/SQL procedure successfully completed.


DBMS_STATS.DELETE_INDEX_STATS deletes individual index statistics.

Call Syntax

dbms_stats.delete_index_stats(ownname, indname, partname, stattab, statid, 
cascade_parts, statown);

e.g.: Deleting index statistics.

orAP>exec dbms_stats.delete_index_stats(ownname => 'APPS', indname => 'AM21_N1');
PL/SQL procedure successfully completed.


DBMS_STATS.DELETE_SCHEMA_STATS deletes the complete schema statistics.

Call Syntax

dbms_stats.delete_schema_stats(ownname, stattab, statid, statown);

e.g.: Deleting statistics for schema FA.

SQL> exec dbms_stats.delete_schema_stats('FA');
PL/SQL procedure successfully completed.


DBMS_STATS.DELETE_DATABASE_STATS deletes the complete database statistics.

Call Syntax

dbms_stats.delete_database_stats(stattab, statid, statown);

9.4.3) Providing user statistics with DBMS_STATS


Use this routine to set your own statistics in the dictionary instead of the RDBMS statistics.

Call Syntax

dbms_stats.set_table_stats(ownname, tabname, partname, stattab, statid, numrows, 
numblks, avgrlen, flags, statown);
  • numrows - number of rows.
  • numblks - blocks in the table.
  • avgrlen - average row length.
  • flags - currently for internal use only.


SQL> exec dbms_stats.set_table_stats(ownname => 'JASHAN', tabname => 'TMP_CKFA', -
> numrows => 12422, numblks => 100, avgrlen => 124);
PL/SQL procedure successfully completed.

jaJA>select owner, num_rows, blocks, avg_row_len
  2  from dba_tables
  3  where table_name = 'TMP_CKFA';
JASHAN              |     12422|       100|       124


DBMS_STATS.SET_COLUMN_STATS sets column statistics explicitly.

Call Syntax

dbms_stats.set_column_stats(ownname, tabname, colname, partname, stattab, statid, 
	distcnt, density, nullcnt, srec, avgclen, 
	flags, statown);
  • distcnt - number of distinct values.
  • density - column density. If null, it is derived from distcnt.
  • nullcnt - null count.
  • srec - record of type DBMS_STATS.STATREC, value populated by
  • avgclen - average column length.

e.g.: Setting statistics for one column of a table.

jaJA>exec dbms_stats.set_column_stats(ownname => 'JASHAN', tabname => 
> colname => 'CODE', distcnt => 1000, density => 5, nullcnt => 0, 
avgclen => 12);
PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, low_value, high_value, density,
  2         num_nulls, num_buckets, avg_col_len
  3  from   dba_tab_columns
  4* where  table_name = 'TMP_CKFA' and column_name = 'CODE'
CODE       |        1000|      |       |      5|         0|          1|         12


Set index statistics.

Call Syntax

dbms_stats.set_index_stats(ownname, indname, partname, stattab, statid, numrows, numlblks, 
	numdist, avglblk, avgdblk, clstfct, indlevel, flags, statown);
  • numlblks - number of leaf blocks.
  • numdist - number of distinct keys.
  • avglblk - average number of leaf blocks in which each distinct key appears.
  • avgdblk - average number of data blocks in the table pointed to by the distinct keys.
  • clstfct - clustering factor.
  • indlevel - Height of the index.


jaJA>exec dbms_stats.set_index_stats(ownname => 'JASHAN', indname => 'TMP_CKFA_N1', -
> numrows => 1000, numlblks => 100, numdist => 100, avglblk => 1, avgdblk => 12, -
> clstfct => 1000, indlevel => 2);

PL/SQL procedure successfully completed.

jaJA>select num_rows, blevel, leaf_blocks, avg_leaf_blocks_per_key,
  2         avg_data_blocks_per_key, clustering_factor, user_stats
  3  from   dba_indexes
  4  where  index_name = 'TMP_CKFA_N1';
    1000|     2|        100|         1|        12|        1000|YES

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM

Thanks for your registration, follow us on our social networks to keep up-to-date