Hybrid Columnar Compression - Oracle Exadata Recipes: A Problem-Solution Approach - page 470

subpartition ap_1996 values less than (to_date('01-JAN-1997','dd-MON-yyyy'))
compress for query high ,
... Output omitted for brevity
subpartition ap_2011 values less than (to_date('01-JAN-2012','dd-MON-yyyy')),
subpartition ap_max values less than (maxvalue)
... Output omitted for brevity
subpartition ap_jan2010 values less than (to_date('01-FEB-2010','dd-MON-yyyy')),
), partition partother values (default))
... Output omitted for brevity
subpartition ap_jan2010 values less than (to_date('01-FEB-2010','dd-MON-yyyy')),
tablespace apps_ts_tx_data nologging
as
select * from xla.xla_ae_lines;
Table created.
SQL>
Specifying Compression Attributes for a Tablespace
You can also specify default compression settings at the tablespace level using the DEFAULT COMPRESS FOR clause in
your CREATE TABLESPACE command. Doing so will enable HCC compression for rows inserted or loaded via Oracle's
direct path insert/load operations. The following is an example of using the CREATE TABLESPACE command to set a
tablespace's default compression:
SQL> create bigfile tablespace rtl3 datafile '+DATA_CM01'
size 2g autoextend on next 1m
extent management local autoallocate
segment space management auto
default compress for query low;
Tablespace created.
SQL>
To list your tablespace's default compression settings, run the SQL statement in Listing 16-2.
Listing 16-2. lst16-02-tbs-compsettings.sql
SQL> select tablespace_name,
def_tab_compression,
nvl(compress_for,'NONE') compress_for
from dba_tablespaces;
Tablespace CompSetting CompressType
---------------------- ----------------- --------------------
SYSTEM DISABLED NONE
SYSAUX DISABLED NONE
.. Output omitted for brevity
TBS_TEST DISABLED NONE
TBS_OLTPCOMP ENABLED OLTP
TBS_QUERYHIGH ENABLED QUERY HIGH