USER MANUALS

GET_STATS_FOR_FIELDS

Description

The stored procedure GET_STATS_FOR_FIELDS gathers and returns - not stores - the statistics of the fields of a JDBC base view (minimum value,maximum value, etc. of each field) and the number of rows of the view.

To obtain these statistics, it may query the system tables of a JDBC database or it may execute a SELECT query with aggregation functions instead.

Before obtaining the statistics for a JDBC base view, make sure that the source database (Oracle, IBM DB2, etc.) has the statistics of this table. If the statistics are not present or are outdated, the statistics gathered will not be accurate and the execution plans selected by the cost-based optimizer could be suboptimal.

Syntax

GET_STATS_FOR_FIELDS (
      input_mode : text
    , input_database_name : text
    , input_view_name : text
    , input_fields: array
    , input_table_stats : boolean
    , input_no_delegate : boolean
    , input_return_queries : boolean
    , input_save : boolean
)

  • input_mode: sets how the procedure gathers the statistics. Valid values:

    • SMART_ONLY: the procedure gathers the statistics from the system tables of the database.

    • SMART_THEN_ATSOURCE_THROUGH_VDP: the procedure gathers the statistics from the system tables of the database. Then, it executes a SELECT statement to gather the statistics that it cannot not obtain from the system tables of the database.

    • ATSOURCE_THROUGH_VDP_ONLY: the procedure does not query the system tables. Instead, it just executes a SELECT statement.

    • SMART_CACHE_ONLY: the procedure gathers the statistics from the system tables of the database used by VDP to cache the view.

    • SMART_CACHE_THEN_ATCACHE_THROUGH_VDP: the procedure gathers the statistics from the system tables of the database used by VDP to cache the view. Then, it executes a SELECT statement to gather the statistics that it cannot not obtain from the system tables of the cache database. These aggregation queries will be executed by the database used by VDP to cache the view.

    • ATCACHE_THROUGH_VDP_ONLY: the procedure does not query the system tables. Instead, it just executes a SELECT statement. These aggregation queries will be executed by the database used by VDP to cache the view.

  • input_database_name: name of the database to which view_name belongs.

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

  • input_view_name: the procedure will gather the statistics of this view (with the same case).

  • input_fields: array of registers with the fields whose statistics you want to gather. Each register has these fields:

    • field_name: name of the field.

    • stats: array of statistics that you want to collect: FIELD_COUNT_DISTINCT (Distinct values), FIELD_AVG_LEN (Average size), FIELD_MAX (Max value), FIELD_MIN (Min value) and FIELD_NUM_NULLS (Null values).

      If null, it gathers all the statistics of the field.

    • source_stat_name: name of the object in the database that holds the statistics of this view.

      Some sources like Microsoft SQL Server, allow the user to generate multiple statistics over the same field or set of fields and store them in different objects.

      If null, it obtains the statistics from the default object. This value is ignored when is not applicable.

  • input_table_stats: if true, the procedure obtains the number of rows of the table. If false, it does not.

  • input_no_delegate: if true, the procedure does not push down to the source any aggregation function when executing SELECT statements. If false, it does.

  • input_return_queries: if true, the procedure will also return any statement or query that was executed to obtain the statistics of the table. If false, it will not.

  • input_save: if true, the procedure stores the gathered statistics of the table instead of returning them. If false, the stored procedure returns the statistics but they are not persisted.

Examples

Example 1

CALL GET_STATS_FOR_FIELDS('SMART_ONLY'
    , 'admin'
    , 'internet_inc'
        , {
        ROW('iinc_id',NULL,NULL),
        ROW('specific_field1',NULL,NULL),
        ROW('taxid',NULL,NULL)
        }
    , true
    , false
    , false
    , false);

It gathers all the statistics of the fields iinc_id, specific_field1 and taxid of the view internet_inc. To do

this, it only queries the system tables of the database.

Example 2

CALL GET_STATS_FOR_FIELDS('SMART_ONLY'
    , 'admin'
    , 'internet_inc'
        , {
        ROW('iinc_id',NULL,NULL),
        ROW('specific_field1',NULL,NULL),
        ROW('taxid',NULL,NULL)
        }
    , false
    , false
    , false
    , false);

It does the same as the previous example, but it does not gather the number of rows of the table (note the value of the latest parameter).

Example 3

CALL GET_STATS_FOR_FIELDS('SMART_ONLY'
    , 'admin'
    , 'internet_inc'
        , { ROW('summary',
            { ROW('FIELD_NUM_NULLS'),
            ROW('FIELD_COUNT_DISTINCT'),
            ROW('FIELD_AVG_LEN') }, NULL)}
    , true
    , false
    , false
    , false);

It gathers the number of rows of the view and the statistics “Null values”, “Distinct values” and “Average size” of the field summary.

Because of the parameter SMART_ONLY, to do this, it only queries the system tables of the database.

Example 4

CALL GET_STATS_FOR_FIELDS('SMART_ONLY'
    , 'admin'
    , 'internet_inc'
        , {
        ROW('iinc_id',NULL,NULL),
        ROW('specific_field1',NULL,NULL),
        ROW('taxid',NULL,NULL)
        }
    , true
    , false
    , false
    , true);

It gathers all the statistics of the fields iinc_id, specific_field1 and taxid of the view internet_inc, but will store these values instead of returning them.

Add feedback