AUDIT_FLEX

Estimates the ROS size of one or more flexible tables contained in a database, schema, or projection.

The audit_flex() function measures the export size of the flex data values (does not include flex keys) for the __raw__ column of one or more flexible tables. The function does not audit other flex table columns that are created as, or promoted to, real columns. Temporary flex tables are not included in the audit.

Each time a user calls audit_flex(), Vertica stores the results in the V_CATALOG.USER_AUDITS system table.

Syntax

AUDIT_FLEX (name)

Parameters

name

Specifies what database entity to audit. Enter the entity name as a string in single quotes (''), as follows: 

  • Empty string ('') — Return the size of the ROS containers for all flexible tables in the database. You cannot enter the database name.
  • Schema name ('schema_name') — Return the size of all __raw__ columns of flexible tables in schema_name.
  • A projection name ('proj_name') — Return the ROS size of a projection for a __raw__ column.
  • A flex table name ('flex_table_name') — Return the ROS size of a flex table's __raw__ column.

Privileges

Note: AUDIT_FLEX() works only on the flexible tables, projections, schemas, and databases to which the user has permissions.

Examples

To audit the flex tables in the database:

dbs=> select audit_flex('');
audit_flex
------------
8567679
(1 row)

To audit the flex tables in a specific schema, such as public

dbs=> select audit_flex('public');
audit_flex
------------
8567679
(1 row)

To audit the flex tables in a specific projection, such as bakery_b0

dbs=> select audit_flex('bakery_b0');
 audit_flex
------------
 8566723
(1 row)

To audit a flex table, such as bakery

dbs=> select audit_flex('bakery');
 audit_flex
------------
 8566723
(1 row)

To report the results of all audits saved in the USER_AUDITS, the following shows part of an extended display from the system table showing an audit run on a schema called test, and the entire database, dbs

dbs=> \x
Expanded display is on.

dbs=> select * from user_audits;
-[ RECORD 1 ]-------------------------+------------------------------ size_bytes | 0 user_id | 45035996273704962 user_name | release object_id | 45035996273736664 object_type | SCHEMA object_schema | object_name | test audit_start_timestamp | 2014-02-04 14:52:15.126592-05 audit_end_timestamp | 2014-02-04 14:52:15.139475-05 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | f confidence_interval_lower_bound_bytes | 0 confidence_interval_upper_bound_bytes | 0 sample_count | 0 cell_count | 0 -[ RECORD 2 ]-------------------------+------------------------------ size_bytes | 38051 user_id | 45035996273704962 user_name | release object_id | 45035996273704974 object_type | DATABASE object_schema | object_name | dbs audit_start_timestamp | 2014-02-05 13:44:41.11926-05 audit_end_timestamp | 2014-02-05 13:44:41.227035-05 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | f confidence_interval_lower_bound_bytes | 38051 confidence_interval_upper_bound_bytes | 38051 sample_count | 0 cell_count | 0 -[ RECORD 3 ]-------------------------+------------------------------ . . .