How the Gathering Statistics Process Works¶

This section explains how the Server gathers the statistics of a view.

The process to gather the statistics of a view depends on the type of base view:

• JDBC base views: the Server gathers the statistics of the view from the system tables of the database. The information obtained from these tables depends on the database adapter of the view’s data source. The reason is that some databases provide more statistics about their tables/views than others.

  The table Statistics that are gathered from the system tables of each vendor lists the statistics that the Server can gather from each database vendor.

  If “Complete missing statistics…” is selected, the Server executes an additional SELECT query to obtain the statistics it cannot obtain from the system tables.

  On views created over an element of an object storage (Object Storage data in Parquet and Delta Format), Virtual DataPort also computes the statistics on the Embedded MPP.

  This does not apply to JDBC base views created from a query, nor to JDBC base views created over stored procedures. In these two cases, the statistics are gathered like for any non-JDBC base view.

• Other views: Virtual DataPort executes a query like the one in Sample SQL query executed to gather statistics of a non-JDBC base view to gather the statistics of the view. If the view involves an ODBC source, part of this query may be pushed down to that source, thus making the process faster.

SELECT count(*),
    , avg( length( <text field> ) )
    , max( <text field> )
    , min( <text field> )
    , count( DISTINCT <text field> )
    , ...
    , max( <numeric field> )
    , min( <numeric field> )
    , count( distinct( <numeric field> ) )
    , count( <numeric field> )
    , ...
    FROM view

When the data is obtained from other types of sources (e.g. delimited files, XML, etc.), Virtual DataPort retrieves all the rows from the source and computes the statistics by itself. In views where all the data is obtained from sources that are not databases, selecting the check box “Gather statistics for…” is equivalent to clearing it.

The following table lists which statistics are gathered from the system tables of each database vendor.

(*): by default, the PrestoDB/Trino adapter can obtain the statistics from the system tables of PrestoDB/Trino version 183 or higher, but not from earlier versions. To be able to do this for earlier versions of Presto, follow these steps:

1. Obtain the version of PrestoDB/Trino you are connecting to.

2. Login as an administrator and execute this command from the VQL Shell:

   SET 'com.denodo.vdb.contrib.storedprocedure.GenerateStatsProcedure.presto.statsversion' = '<value>';
   

   where <value> is:

   • pre_177: if the version of PrestoDB/Trino is 176 or lower. In these versions, the system tables do not provide statistics.

   • post_177: if the version is 177 or higher but lower than 183. In these versions, the system tables provide the statistics “number of rows”, “distinct values” and “null values” but not “min value” and “max value”.

   • post_183 (default value): if the version is 183 or higher. In these versions, the system tables provide the statistics listed in the table above.

3. In the installation of the administration tool you use, edit the file <DENODO_HOME>/conf/vdp-admin/VDBAdminConfiguration.properties and add this property

   com.denodo.vdb.contrib.storedprocedure.GenerateStatsProcedure.presto.statsversion=<version>
   

   Replace <version> with the same value you set in the previous step.

4. Restart the Virtual DataPort server and the administration tool.

(**): For Teradata 13 or older only the number of rows and distinct values are available

For the vendors not listed in this table, the statistics are obtained issuing SELECT queries like the one in Sample SQL query executed to gather statistics of a non-JDBC base view.

The following table lists the tables, statements or functions Virtual DataPort queries or executes to obtain the statistics listed in the table above. They depend on the database from which Virtual DataPort obtains the statistics.

The Server does not collect statistics for the fields of the following types:

• blob

• boolean

• xml

• array

• register.

• Any field whose source type property is one of the following:

  • BIT

  • CLOB

  • LONGVARCHAR

  • LONGNVARCHAR

For the fields of some of these types, it sets a value that is extrapolated from other statistics. For example, for blob, array and register fields, it assumes that each value is different. Therefore, the value of the statistic “Distinct values” is the number of rows of the view.

The cost-based optimizer does not use the maximum and minimum values of the fields of type blob, boolean, date, text, xml, array or record. Therefore, in the statistics table of the view, the cells of these fields are disabled and have the label “N/A”.

Scheduling the Gathering of Statistics¶

The stored procedure GENERATE_STATS gathers and stores the statistics of a view. Use Denodo Scheduler to schedule the gathering of the statistics of a view. The main objective of this is to gather them during periods where the system is not expected to be under heavy load.

Limitations of the Gathering Statistics Process¶

There are several limitations in the process of gathering statistics. When these limitations apply, statistics can be specified manually for the affected fields:

• In some databases, the statistics are not obtained for fields of some database data types, even if in Virtual DataPort they are mapped to a data type not included in the previous bullet. More precisely:

  • In IBM DB2, statistics are not collected for table fields of the data types LONGVARCHAR and LONGVARGRAPHIC.

  • In Microsoft SQL Server, statistics are not collected for table fields of the data types TEXT and NTEXT.

  • In Oracle, statistics are not collected for table fields of the data types CLOB, NCLOB, and SQLXML.

  • In Spark, minimum and maximum metrics are not available for binary and string types.

• In later versions of Informix, it is not possible to collect statistics from tables with fields of types INTERVAL.

• It is not possible to collect statistics from tables in SQL Server with fields of types UNIQUEIDENTIFIER or SQLVARIANT.

• It is not possible to collect statistics for views that have mandatory fields.