Page 1 of 1
how to calculate compression on table in 6.x
Posted: Fri Sep 14, 2012 3:27 pm
by potluri27
How do i calulate raw size and compressed achieved in 6.x on a table ?
compression.sh script is broken and doesnot work in 6.x
Re: how to calculate compression on table in 6.x
Posted: Fri Sep 14, 2012 7:53 pm
by jpcavanaugh
What errors are you getting from compression.sh?
Re: how to calculate compression on table in 6.x
Posted: Sun Mar 24, 2013 6:11 pm
by pborne
It's a two-step process:
1. Ask Vertica to estimate the raw size of the table or schema. Check out the syntax for audit() in the "SQL Reference" document:
2. Run the following query to compute the compression ratio:
Code: Select all
SELECT *, RawSize / CompressedSize AS Ratio
FROM (
SELECT audit_start_timestamp,
anchor_table_name,
size_bytes AS RawSize,
CAST(SUM(ros_used_bytes) AS DECIMAL(14, 2)) AS CompressedSize
FROM projection_storage ps
INNER JOIN user_audits ua ON ps.projection_schema = ua.object_schema
AND ps.anchor_table_name = ua.object_name
WHERE audit_start_timestamp > now() - 1
GROUP BY 1, 2, 3
) A
ORDER BY audit_start_timestamp, anchor_table_name;
Patrice