分类: Oracle
2008-10-06 16:35:26
NOCOMPRESS
- The table or partition is not compressed. This is the default action when no compression clause is specified.
COMPRESS
- This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
COMPRESS FOR DIRECT_LOAD OPERATIONS
- This option has the same affect as the simple COMPRESS
keyword.
COMPRESS FOR ALL OPERATIONS
- This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE
initialization parameter to be set to 11.1.0 or higher. Table-level compression settings are reflected in the-- Table compression. CREATE TABLE test_tab_1 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL, created_date DATE NOT NULL ) COMPRESS FOR ALL OPERATIONS; -- Partition-level compression. CREATE TABLE test_tab_2 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL, created_date DATE NOT NULL ) PARTITION BY RANGE (created_date) ( PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS, PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS, PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS, PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS );
COMPRESSION
and COMPRESS_FOR
columns of the [DBA|ALL|USER]_TABLES
views.Tables defined with partition-level compression and no table-level compression display NULL values in these columns.SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------------ TEST_TAB_1 ENABLED FOR ALL OPERATIONS TEST_TAB_2 2 rows selected. SQL>
COMPRESSION
and COMPRESS_FOR
columns of the [DBA|ALL|USER]_TAB_PARTITIONS
views.The compression settings for tables and partitions can be modified using theSELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ ------------------------------ -------- ------------------ TEST_TAB_2 TEST_TAB_Q1 ENABLED DIRECT LOAD ONLY TEST_TAB_2 TEST_TAB_Q2 ENABLED DIRECT LOAD ONLY TEST_TAB_2 TEST_TAB_Q3 ENABLED FOR ALL OPERATIONS TEST_TAB_2 TEST_TAB_Q4 DISABLED 4 rows selected. SQL>
ALTER TABLE
command. The alterations have no effect on existing data, only on new operations applied to the table.Default compression settings can be specified at the tablespace level using theALTER TABLE test_tab_1 NOCOMPRESS; ALTER TABLE test_tab_2 MODIFY PARTITION test_tab_q4 COMPRESS FOR ALL OPERATIONS;
CREATE TABLESPACE
and ALTER TABLESPACE
commands. The current settings are displayed in the DEF_TAB_COMPRESSION
and COMPRESS_FOR
columns of the DBA_TABLESPACES
view.When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.CREATE TABLESPACE test_ts DATAFILE '/u01/app/oracle/oradata/ora11g/test_ts01.dbf' SIZE 1M DEFAULT COMPRESS FOR ALL OPERATIONS; SELECT def_tab_compression, compress_for FROM dba_tablespaces WHERE tablespace_name = 'TEST_TS'; DEF_TAB_ COMPRESS_FOR -------- ------------------ ENABLED FOR ALL OPERATIONS 1 row selected. SQL> ALTER TABLESPACE test_ts DEFAULT NOCOMPRESS; SELECT def_tab_compression, compress_for FROM dba_tablespaces WHERE tablespace_name = 'TEST_TS'; DEF_TAB_ COMPRESS_FOR -------- ------------------ DISABLED 1 row selected. SQL> DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;
COMPRESS FOR ALL OPERATIONS
option was used.