Use the GENERATE STATISTICS command to generate information about each column proportion of duplicate values, and the maximum and minimum values. The optimizer uses this information to determine the most efficient way to run a query. Run the GENERATE STATISTICS command when you initially load a table and after the table data is changed (for example, after you add or delete records). This enables the query optimizer to make better choices when planning user queries.
GENERATE STATISTICS ON <tablename> [ (<col>[,<col>… ]) ];
The GENERATE STATISTICS command takes the following inputs:
|<tablename>||The name of a table to analyze. The default is all tables in all schemas of the current database.|
|<col>||The column or columns to analyze.|
The GENERATE STATISTICS command has the following output:
|GENERATE STATISTICS||The command completed successfully.|
You must be the admin user, the table owner, the owner of the database or schema that contains the table, or your account must have the Genstats privilege for the table or for the Table object class. In addition, your user account must have the List privilege for the schema that contains the table.
- Generate statistics for column
cnumberof the table
MYDB.SCH1(USER)=> GENERATE STATISTICS ON cows (cnumber);
- Generate statistics for all the tables in database
MYDB(USER)=> GENERATE STATISTICS;