GENERATE_STATS_FOR_FIELDS (deprecated)¶

Description

The stored procedure GENERATE_STATS_FOR_FIELDS gathers and stores the statistics of the fields of a view (minimum value, maximum value, etc. of each field) and the number of rows of the view. It works as the procedure GENERATE_STATS, with the difference that it only obtains the statistics of the fields you indicate.

If possible, use the procedure GENERATE_SMART_STATS_FOR_FIELDS, which can query the system tables of the database to obtain these statistics instead of executing a regular SELECT statement.

Syntax

GENERATE_STATS_FOR_FIELDS(
      viewname : text,
    , fields : array
    , databasename : text
)

• viewname: the procedure will gather the statistics of the view with this name (with the same case)

• fields: array of field names that the procedure will obtain its statistics for. When you specify only some fields of the view, the procedure does not delete the statistics of the other fields of the view that have already been gathered.

  If null, the procedure gathers the statistics of all the fields of the view.

  If an empty array (i.e. {}), the procedure gathers the number of rows of the view, but does not obtain the statistics of any field.

• databasename: name of the database to which viewname belongs.

  If null the procedure looks for viewname in the current database.

Privileges Required

Only users that have the Write privilege on the view can execute this procedure. This means, the following users can execute this procedure:

• Administrators or administrators of this database.

• Users that have the Connect and Write privileges on this database.

• Users that have the Connect privilege on this database and Write privilege on this view.

Examples

Example 1

SELECT *
FROM GENERATE_STATS_FOR_FIELDS()
WHERE databasename = 'customer360_db'
    AND viewname = 'top_customer_by_region'
        AND fields = { ROW('region'), ROW('customer_id'), ROW('amount') };

This statement gathers and stores the statistics for the fields region, customer_id and amount of the view top_customer_by_region.

The parameter fields is an array so you have to use the construct { ROW(...), ROW(...),...} to define the array of field names. You can see more details about this construct in the section Conditions with Compound Values.

Example 2

SELECT *
FROM GENERATE_STATS_FOR_FIELDS()
WHERE databasename = 'customer360_db'
    AND viewname = 'top_customer_by_region'
    AND fields = {}

This statement gathers and stores the number of rows of the view, but does not obtain the statistics for the fields of the view. If previously, you obtained the statistics of the views, the statistics of the fields remain in place, it just refreshes the number of rows.