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 (''), AUDIT audits the database.

granularity

The level at which the audit reports its results, one of the following strings:

  • database
  • schema
  • table

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 online_sales is a schema, the following statements are identical:

AUDIT('sales', 'schema');
AUDIT('sales');

If granularity is less than name, AUDIT returns with a message that refers to system table V_CATALOG.USER_AUDITS. You can query this table to find detailed information on the objects within name at the specified granularity. For example:

=> 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 AUDIT analyzes the entire database. A full database audit significantly impacts performance, so Vertica does not recommend it for a production database

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 AUDIT to run for a longer period than a full database audit. The lower you specify this value, the more resources the audit uses, as it performs more data sampling.

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:

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