Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Reference » System Objects » System procedures » Alphabetical list of system procedures

 

sa_get_histogram system procedure

Retrieves the histogram for a column.

Syntax
sa_get_histogram( 
  col_name,
  tbl_name
  [, owner_name ]
)
Arguments
  • col_name   Use this CHAR(128) parameter to specify the column for which to retrieve the histogram.

  • tbl_name   Use this CHAR(128) parameter to specify the table in which col_name is found.

  • owner_name   Use this optional CHAR(128) parameter to specify the owner of tbl_name.

Result set
Column name Data type Description
StepNumber SMALLINT Histogram bucket number. The frequency of the first bucket ( StepNumber = 0) indicates the selectivity of NULLs.
Low CHAR(128) Lowest (inclusive) column value in the bucket.
High CHAR(128) Highest (exclusive) column value in the bucket.
Frequency DOUBLE Selectivity of values in the bucket.
Remarks

This procedure, intended for internal diagnostic purposes, retrieves column statistics from the database server for the specified columns. Note that while these statistics are permanently stored in the ISYSCOLSTAT system table, they are maintained in memory while the server is running, and written to ISYSCOLSTAT periodically. As such, the statistics returned by the sa_get_histogram system procedure may differ from those obtained by selecting from ISYSCOLSTAT at any given point of time.

You can manually update ISYSCOLSTAT with the latest statistics held in memory using the sa_flush_statistics system procedure, however, this is not recommended in a production environment, and should be reserved for diagnostic purposes. See sa_flush_statistics system procedure.

A singleton bucket is indicated by a Low value in the result set being equal to the corresponding High value.

It is recommended that you use the Histogram utility to view histograms. See Histogram utility (dbhist).

To determine the selectivity of a predicate over a string column, use the ESTIMATE or ESTIMATE_SOURCE functions. For string columns, both sa_get_histogram and the Histogram utility retrieve nothing from the ISYSCOLSTAT system table. Attempting to retrieve string data generates an error. See ESTIMATE function [Miscellaneous], and ESTIMATE_SOURCE function [Miscellaneous].

Statistics (including histograms) may not be present for a table or materialized view, for example, if statistics were recently dropped. In this case, the result set for the sa_get_histogram system procedure is empty. To create statistics for a table or materialized view, execute a CREATE STATISTICS statement. See CREATE STATISTICS statement.

Permissions

DBA authority required

Side effects

None

See also
Example

For example, the following statement retrieves the histogram for the ProductID column of the SalesOrderItems table:

CALL sa_get_histogram( 'ProductID', 'SalesOrderItems' );