ANALYZE_STATISTICS
Note: ANALYZE_STATISTICS is an alias of the function ANALYZE_HISTOGRAM, which is no longer documented.
Collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. By default, Vertica analyzes multiple columns in a single-query execution plan, depending on resource limits. Such multi-column analysis facilitates the following objectives:
- Reduce plan execution latency.
- Speed up analysis of relatively small tables with many columns.
Vertica writes statistics to the database catalog. The query optimizer uses this collected data to create query plans. Without this data, the query optimizer assumes uniform distribution of data values and equal storage usage for all projections.
You can cancel statistics collection with CTRL+C or by calling INTERRUPT_STATEMENT.
Syntax
ANALYZE_STATISTICS ('[ scope ]' [, 'column[,...]'] [, percent ] )
Returns
0—Success
If an error occurs, refer to vertica.log for details.
Parameters
| scope |
Specifies the table on which to collect data, as follows: [schema.]table If set to an empty string, Vertica collects statistics for all database tables and their projections. |
| column |
The name of a column in table, typically a predicate column. You can specify multiple comma-delimited columns. Vertica narrows the scope of the data collection to the specified columns. If you alter a table to add a column and populate its contents with either default or other values, call |
| percent |
A float value between 0 and 100 that specifies what percentage of data to read from disk (not the amount of data to analyze). If you omit this argument, Vertica sets the percentage to 10. Analyzing more than 10 percent disk space takes proportionally longer to process, but produces a higher level of sampling accuracy. |
Privileges
- Any INSERT/UPDATE/DELETE privilege on the specified table.
- USAGE privilege on schema that contains the table
Restrictions
- Vertica collects no statistics on live aggregate and Top-K projections that are anchored to the specified table.
- If you include a SQL function within an expression when you create a projection, Vertica collects no statistics for that projection.
- Vertica supports
ANALYZE_STATISTICSon local temporary tables but not on global temporary tables. To obtain statistics on a temporary table, create the table with the optionON COMMIT PRESERVE ROWS. Otherwise, Vertica deletes the table content when it commits the current transaction, so no table data is available for analysis.
