Was this helpful?
Generate Statistics Dialog
The Generate Statistics dialog lets you set options for optimizing a selected database. This dialog contains the following pages:
For more information, see Generate Statistics.
General Page (Generate Statistics Dialog)
This page lets you set basic options for generating statistics. The following options are available:
Effective User
Specifies the effective user name for the session. Valid only for a privileged user, DBA, or sessions that have the db_admin database privilege.
Some commands, including Backup Database (see Backup Database Dialog), Restore Database (see Restore Database Dialog), Verify Database (see Verify Database Dialog), Create Database (see General Page (New Database)), and Delete Database (see Delete Database Dialog), restrict the use of the effective user option to privileged users.
Note:  This option does not assume the group of the effective user.
Generate Statistics on Keys/Indexes Columns Only
Specifies whether to generate statistics only on key and indexed columns
Tables
Specifies whether to generate statistics on all tables or specific tables. If you select Specify Tables, click the Tables button to choose the tables to include. If you specify particular tables, you may choose to include all columns or select specific columns by clicking the Columns button.
You also may exclude specific tables from statistics generation. Click the Tables button to choose the tables to exclude.
Indexes
Lets you choose whether to generate statistics on all indexes or specific indexes. If you selected tables to exclude, all indexes are automatically included.
Data Set Size
Lets you specify the size of the data set on which to generate statistics. If you select Generate Statistics on Subset of Data, further options become available:
Sort Tuple Identifiers
Specifies that the tuple identifiers (TIDs), which are used to retrieve the sample rows, should be sorted before the rows are retrieved
Estimate Number of Distinct Values
Directs the optimization to use an algorithm to estimate the number of distinct values for columns whose histograms are built with sampling
Precision Level
Specifies the percentage of table rows to sample for the data set
Default: 10
Range: 1–99
Verbose Mode, Print Information about each Column
Specifies that information should be printed for each column in the database as it is being processed
Options Page (Generate Statistics Dialog)
This page lets you set specific options for generating statistics. The following options are available:
Wait for Database
Specifies whether to wait until the database has no other open connections before starting to generate statistics
Default: Don’t Wait
Exclusive Database Lock
Specifies that an exclusive database lock be issued while statistics are generated
Optimize System Catalogs
Optimizes the system catalogs in addition to the base tables
Composite Histogram on Primary Key
Requests a composite histogram on the primary key structure
Include Encrypted Columns
Specifies that statistics should be included for encrypted columns.
For more information about OPTIMIZEDB and data at rest encryption, see the Ingres Security Guide.
Use 'fast query' to Build Histogram
Specifies the “fast query” option, which significantly reduces the time to build a histogram. This option improves performance only when the repetition factor of the column is 20 or higher. This option alone generates a global temporary table from the values of the histogrammed columns. The histograms are then built by reading from the faster temporary table, rather than the base table.
Disable Global Temp Tables for 'fast query'
When Use 'fast query' to Build Histogram is selected, choosing this option prevents the histogram from being built from a generated global temporary table
Print Histograms for Each Column
Specifies that the histogram generated for each column be printed in the generated statistics
Reuse Existing Repetition Factor
Specifies that the existing repetition factor be used, if any
Read All Rows
Disables the default behavior of creating histograms from a maximum 500,000-row sample. Setting this option helps to ensure that all rows are read from a table during the histogram-building process.
Max Cells Inexact Histogram
Specifies the maximum number of cells that an inexact histogram can contain. In an inexact histogram, each cell represents a range of values.
Default: 200
Range: 2–14999
Max Cells Exact Histogram
Specifies the maximum number of cells an exact histogram can contain. In an exact histogram, each cell represents a single unique value.
Default: 200
Range: 1–15000
Column Is Complete and Contains All Values
Indicates whether a column contains all possible values. The range of values in a column affects query optimization. By default, columns are assumed to be incomplete.
Determine Min/Max Values for Each Column
Directs optimization to determine only the minimum and maximum values for each column rather than full statistics. Because minimum and maximum values for columns from the same table can be determined by a single scan through the table, this option provides a quick way to generate a minimal set of statistics. Minimal statistics cannot be created on columns holding only null values.
Display Formatting Page (Generate Statistics Dialog)
This page lets you set display formatting options for generating statistics. The following options are available:
Minimum Character Column Width
Sets the minimum field width for character columns
Default: 6
Minimum Text Column Width
Sets the minimum field width for text columns
Default: 6
Integer Output Width for i1's
Sets the integer output column width for i1’s
Default: 6
Integer Output Width for i2's
Sets the integer output column width for i2’s
Default: 6
Integer Output Width for i4's
Sets the integer output column width for i4’s
Default: 13
Floating-Point Output Width for f4's
Sets the floating-point output column width for f4’s. You must select one of the following output formats:
Floating-Point N (default)
Specifies floating-point format. If numbers are too large for this format, they are displayed in exponential format.
Floating-Point
Specifies floating-point format. If numbers are too large for this format, they are displayed in exponential format, and asterisks (****) are printed to represent display overflow.
Decimal Align
Specifies floating-point format with decimal alignment. If numbers are too large for this format, they are displayed in exponential format.
Exponential
Specifies exponential format
You must also set the number fields:
Left number field
Specifies output column width. To prevent column overflow, the left field should be set to the number of the right field + 7.
Default: 10
Right number field
Specifies the number of decimal places in the output
Default: 3
Floating-Point Output Width for f8's
Sets the the floating-point output column width for f8’s. You must select one of the following output formats:
Floating-Point N (default)
Specifies floating-point format. If numbers are too large for this format, they are displayed in exponential format.
Floating-Point
Specifies floating-point format. If numbers are too large for this format, they are displayed in exponential format, and asterisks (****) are printed to represent display overflow.
Decimal Aligned
Specifies floating-point format with decimal alignment. If numbers are too large for this format, they are displayed in exponential format.
Exponential
Specifies exponential format
You must also set the number fields:
Left number field
Specifies output column width. To prevent column overflow, the left field should be set to the number of the right field + 7.
Default: 10
Right number field
Specifies the number of decimal places in the output
Default: 3
Input/Output Files (Generate Statistics Dialog)
This page lets you specify input and output files and related settings. Files must be stored on the server. The following options are available:
Statistics Input Filename
Specifies a file name to read statistics from instead of operating directly on the database. The file must be an ASCII-format file that was generated by the STATDUMP command using the ‑o flag.
Note:  A file with histogram data in hexadecimal format cannot be used.
For more information, see the Ingres Command Reference Guide.
Read Row and Page Counts
Directs the optimization to read the row and page count values in the Statistics Input file and to store these values in the appropriate system catalog. (You can view these values in iitables.)
Precision Level
Directs the optimization to read floating-point numbers using the specified precision level. To use this option, you must specify a Statistics Input Filename.
Default: 10
Output Filename
Specifies that the output be written to the specified file instead of to the system catalogs
Generate Cell Values in Hex
Generates histogram cell values in hexadecimal format, which is useful for seeing how Unicode data is stored
Output Log (Generate Statistics Dialog)
The Output Log page displays the output of the optimization process based on the options you have selected on the other dialog pages.
Last modified date: 04/24/2023