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
