AUDIT
Estimates the raw data size of a database, schema, or table as it is counted in an audit of the database size.
AUDIT
estimates the size using the same data sampling method as Vertica uses, to determine if a database complies with the licensed database size allowance. The results of this function are not considered when Vertica determines whether the size of the database complies with the Vertica license's data allowance. For details, see Calculating the Database Size in the Administrator's Guide.
Syntax
AUDIT('name'[, 'granularity'] [, error‑tolerance[, confidence‑level] ])
Parameters
name |
The database, schema, or table to audit. If you supply an empty string ( |
granularity |
The level at which the audit reports its results, one of the following strings:
The level of granularity must be equal to or less than the granularity of name. If you omit this parameter, granularity is the same level as name. Thus, if AUDIT('sales', 'schema'); AUDIT('sales'); If granularity is less than name, => SELECT AUDIT('online_sales','table'); AUDIT ------------------------------------------------------------------ See table sizes in v_catalog.user_audits for schema online_sales (1 row) => SELECT * FROM user_audits WHERE object_schema='online_sales'; -[ RECORD 1 ]-------------------------+------------------------------ size_bytes | 59016 user_id | 45035996273704962 user_name | dbadmin object_id | 45035996273739208 object_type | TABLE object_schema | online_sales object_name | online_page_dimension license_name | vertica audit_start_timestamp | 2016-06-17 10:37:31.238291-04 audit_end_timestamp | 2016-06-17 10:37:31.358232-04 confidence_level_percent | 99 error_tolerance_percent | 20 used_sampling | f confidence_interval_lower_bound_bytes | 59016 confidence_interval_upper_bound_bytes | 59016 sample_count | 0 cell_count | 0 -[ RECORD 2 ]-------------------------+------------------------------ ... |
error‑tolerance |
Specifies the percentage margin of error allowed in the audit estimate. Enter the tolerance value as a decimal number, between 0 and 100. The default value is 5, for a 5% margin of error. Setting this value to 0 results in a full database audit, which is very resource intensive, as Due to the iterative sampling that the auditing process uses, making the error tolerance a small fraction of a percent (for example, 0.00001) can cause |
confidence‑level |
Specifies the statistical confidence level percentage of the estimate. Enter the confidence value as a decimal number, between 0 and 100. The default value is 99, indicating a confidence level of 99%. The higher the confidence value, the more resources the function uses, since it performs more data sampling. Setting this value to 100 results in a full audit of the database, which is very resource intensive, as the function analyzes all of the database. A full database audit significantly impacts performance, so Vertica does not recommend it for a production database. |
Privileges
Non-superuser:
- Table: SELECT privilege
- Schema: USAGE privilege
- Database: Only audits the size of data that the user has permission to access
Examples
Audit the entire database:
=> SELECT AUDIT(''); AUDIT ---------- 76376696 (1 row)
Audit the database with 25% error tolerance:
=> SELECT AUDIT('',25); AUDIT ---------- 75797126 (1 row)
Audit the database with a 25% level of tolerance and a 0% confidence level:
=> SELECT AUDIT('',25,90); AUDIT ---------- 76402672 (1 row)
Audit schema online_sales
:
=> SELECT AUDIT('online_sales'); AUDIT ---------- 35716504 (1 row)
Audit schema online_sales
and report the results by table:
=> SELECT AUDIT('online_sales','table'); AUDIT ------------------------------------------------------------------ See table sizes in v_catalog.user_audits for schema online_sales (1 row) => \x Expanded display is on. => SELECT * FROM user_audits WHERE object_schema = 'online_sales'; -[ RECORD 1 ]-------------------------+------------------------------ size_bytes | 64960 user_id | 45035996273704962 user_name | dbadmin object_id | 45035996273717636 object_type | TABLE object_schema | online_sales object_name | online_page_dimension audit_start_timestamp | 2011-04-05 09:24:48.224081-04 audit_end_timestamp | 2011-04-05 09:24:48.337551-04 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | f confidence_interval_lower_bound_bytes | 64960 confidence_interval_upper_bound_bytes | 64960 sample_count | 0 cell_count | 0 -[ RECORD 2 ]-------------------------+------------------------------ size_bytes | 20197 user_id | 45035996273704962 user_name | dbadmin object_id | 45035996273717640 object_type | TABLE object_schema | online_sales object_name | call_center_dimension audit_start_timestamp | 2011-04-05 09:24:48.340206-04 audit_end_timestamp | 2011-04-05 09:24:48.365915-04 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | f confidence_interval_lower_bound_bytes | 20197 confidence_interval_upper_bound_bytes | 20197 sample_count | 0 cell_count | 0 -[ RECORD 3 ]-------------------------+------------------------------ size_bytes | 35614800 user_id | 45035996273704962 user_name | dbadmin object_id | 45035996273717644 object_type | TABLE object_schema | online_sales object_name | online_sales_fact audit_start_timestamp | 2011-04-05 09:24:48.368575-04 audit_end_timestamp | 2011-04-05 09:24:48.379307-04 confidence_level_percent | 99 error_tolerance_percent | 5 used_sampling | t confidence_interval_lower_bound_bytes | 34692956 confidence_interval_upper_bound_bytes | 36536644 sample_count | 10000 cell_count | 9000000