show_compression_count.sql

This script prompts you for a schema name and table name, and it’ll show which distinct compression types are in use on the rows within that table, and a count of how many rows are using that compression.

-- Author: Matthew Walden
-- Date: 14-APR-2012
-- Tested on: 11.2.0.2
-- Description: This script shows the various types of compression in use within a table, and a count of the rows using each type.
--
-- WARNING: Take care when running against large tables!
set veri off
select decode(dbms_compression.get_compression_type('&&schema','&&table',rowid),
1,'NOCOMPRESS',
2,'OLTP',
4,'HCC QUERY HIGH',
8,'HCC QUERY LOW',
16,'HCC ARCHIVE HIGH',
32,'HCC ARCHIVE LOW',
'UNKNOWN') compression_type,count(*) from &schema..&table
group by decode(dbms_compression.get_compression_type('&schema','&table',rowid),
1,'NOCOMPRESS',
2,'OLTP',
4,'HCC QUERY HIGH',
8,'HCC QUERY LOW',
16,'HCC ARCHIVE HIGH',
32,'HCC ARCHIVE LOW',
'UNKNOWN');
undef schema
undef table
Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: