Friday, February 14, 2014

Tables will inherit HCC compression from Tablespace

We are on the Exadata.
The idea: to create the tablespace with HCC and move tables there.
Will tables be HCC-compressed ? Will tables inherit the COMPRESS FOR from the tablespace ?

Today i did the test and answer is YES !

Test env: we have the table BILLS in the non-HCC tablespace.
I created the new tablespace:

SQL> create bigfile tablespace HCC blocksize 8k datafile '+DATAC1'
size 1344m autoextend on next 1344m maxsize unlimited
default COMPRESS FOR QUERY HIGH
extent management local uniform size 4m
segment space management auto;

Tablespace created.


SQL> create table BILLS_HCC tablespace HCC as select * from BILLS;

Table created.


SQL> select segment_name, bytes from dba_segments where segment_name like 'BILLS%';

SEGMENT_NAME             BYTES
------------ -----------------
BILLS              27313307648
BILLS_HCC           6954156032


SQL> select 27313307648/6954156032 from dual;

27313307648/6954156032
----------------------
  3.927623642943305187


SQL> select table_name, compression,compress_for from dba_tables where table_name like 'BILLS%';

TABLE_NAME       COMPRESS COMPRESS_FOR
---------------- -------- ------------
BILLS_HCC        ENABLED  QUERY HIGH
BILLS            DISABLED

SQL> set timing on
SQL> select count(*) from BILLS;

            COUNT(*)
--------------------
           146432303

Elapsed: 00:00:02.83
SQL> select count(*) from BILLS_HCC;

            COUNT(*)
--------------------
           146432303

Elapsed: 00:00:02.65

1 comment:

  1. Thank, Yuri, very useful!
    But why elapsed time for both queries (executed by FULL TABLE SCAN imho) are almost the same despite of great segment size difference? - this looks a bit strange

    ReplyDelete

Note: Only a member of this blog may post a comment.

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...