Exadata Hybrid Columnar Compression (EHCC)

Let's see how EHCC help us to reduce storage space with performance database improvement.

Here we have created three tables with same data as per below.

1. Test  with no compression

2. Test_hcc_archive with archive high compression

3. Test_hcc_query with query high compression

#### List of tables with compression types ####

SQL> select table_name,compression,compress_for

  2    from user_tables

  3   where table_name in ('TEST','TEST_HCC_QUERY','TEST_HCC_ARCHIVE');

TABLE_NAME                 COMPRESS COMPRESS_FOR                            

------------------------------ -------- ------------                            

TEST                           DISABLED                                         

TEST_HCC_ARCHIVE      ENABLED    ARCHIVE HIGH                            

TEST_HCC_QUERY         ENABLED    QUERY HIGH                              

#### Each Tables are with 100000 row data ####

SQL> select count(*) from test;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.11

SQL> select count(*) from test_hcc_query;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.10

SQL> select count(*) from test_hcc_archive;

  COUNT(*)                                                                      

----------                                                                      

    100000                                                                      

Elapsed: 00:00:00.10

#### Checking the size of data for each tables ####

SQL> SELECT segment_name,sum(bytes)/1024/1024 MB

  2  FROM user_segments

  3  WHERE segment_name in ('TEST','TEST_HCC_QUERY','TEST_HCC_ARCHIVE')

  4  GROUP BY segment_name

  5  ORDER BY MB

  6  /

SEGMENT_NAME                           MB                                         

----------------------------------------------------

TEST_HCC_ARCHIVE               .75

TEST_HCC_QUERY .75

TEST 792

                                       

Here we can see the magic of compression. Exadata has compressed the data to 1 MB from 792 MB. Here data we have populated that are for testing purpose so both compression showing same values based on type of data but in real time it will show the difference.

Now I am creating one more test1 table and will populate data 2 times then our above tables.

SQL> create table test1 as select * from test;

Table created.

Elapsed: 00:00:04.47

SQL> select table_name,compression,compress_for

  2    from user_tables

  3   where table_name in ('TEST','TEST_HCC_QUERY','TEST_HCC_ARCHIVE', 'TEST1');

TABLE_NAME                  COMPRESS    COMPRESS_FOR                            

------------------------------ --------      ------------                            

TEST                            DISABLED                                         

TEST1                          DISABLED                                         

TEST_HCC_ARCHIVE       ENABLED       ARCHIVE HIGH                            

TEST_HCC_QUERY          ENABLED       QUERY HIGH                              

Elapsed: 00:00:00.02

#### Test1 table without compression ###

SQL> SELECT segment_name,sum(bytes)/1024/1024 MB

  2  FROM user_segments

  3  WHERE segment_name in ('TEST1')

  4  GROUP BY segment_name

  5  ORDER BY MB

  6  /

SEGMENT_NAME                           MB                                         

----------------------------------------------------

TEST1     792                                          

Now moving test1 to query high compression.

SQL> alter table test1 move compress for query high;

Table altered.

Elapsed: 00:00:02.44

SQL>  select table_name,compression,compress_for

  2    from user_tables

  3   where table_name in ('TEST','TEST_HCC_QUERY','TEST_HCC_ARCHIVE' ,'TEST1');

TABLE_NAME                     COMPRESS COMPRESS_FOR                            

------------------------------ -------- ------------                            

TEST                           DISABLED                                         

TEST1                          ENABLED      QUERY HIGH                              

TEST_HCC_ARCHIVE       ENABLED      ARCHIVE HIGH                            

TEST_HCC_QUERY          ENABLED      QUERY HIGH                              

Elapsed: 00:00:00.02

#### Test1 table after compression ###

SQL> SELECT segment_name,sum(bytes)/1024/1024 MB

  2  FROM user_segments

  3  WHERE segment_name in ('TEST_HCC_QUERY','TEST_HCC_ARCHIVE','TEST','TEST1')

  4  GROUP BY segment_name

  5  ORDER BY MB

  6  /

 SEGMENT_NAME                           MB                                         

----------------------------------------------------

TEST_HCC_ARCHIVE .75

TEST_HCC_QUERY .75

TEST 792

TEST1                                    .75                                          

                                                                                

Elapsed: 00:00:00.03

You can observe drastic change in table size by doing EHCC.

####Populating more data in Test1 table ####

SQL> begin

  2  for i in 100001..200000 loop

  3  insert into test1 values (i, 'bc','de','ef','gh');

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.14

#########  Test1 table size without compression  ###

SQL> SELECT segment_name,sum(bytes)/1024/1024 MB

  2  FROM user_segments

  3  WHERE segment_name in ('TEST_HCC_QUERY','TEST_HCC_ARCHIVE','TEST','TEST1')

  4  GROUP BY segment_name

  5  ORDER BY MB

  6  /

 SEGMENT_NAME                           MB                                         

----------------------------------------------------

TEST_HCC_ARCHIVE .75

TEST_HCC_QUERY .75

TEST 792

TEST1     792                                          

                                                                                

Elapsed: 00:00:00.03

### Moving for compression ###

SQL> alter table test1 move compress for query high;

Table altered.

Elapsed: 00:00:08.14

### Table size after compression ####

SQL> SELECT segment_name,sum(bytes)/1024/1024 MB

  2  FROM user_segments

  3  WHERE segment_name in ('TEST_HCC_QUERY','TEST_HCC_ARCHIVE','TEST','TEST1')

  4  GROUP BY segment_name

  5  ORDER BY MB

  6  /

 SEGMENT_NAME                           MB                                         

----------------------------------------------------

TEST_HCC_ARCHIVE .75

TEST_HCC_QUERY .75

TEST 792

TEST1      2                                          

                                                                                

Elapsed: 00:00:00.03

Note : All the data we have taken is purely for testing purpose. In real time compression ratio may change as per table structure.