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
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
Thank, Yuri, very useful!
ReplyDeleteBut 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