Snippet Name: Compressed Tablespace
Description: Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table.
* For an entire table, in the physical_properties clause of relational_table or object_table
* For a range partition, in the table_partition_description of the range_partitioning clause
* For a list partition, in the table_partition_description of the list_partitioning clause
* For the storage table of a nested table, in the nested_table_col_properties clause
Also see:
» Compressed Direct Load
» Compressed Tablespace
Comment: The table_compression clause is valid only for heap-organized tables.
Table compression saves disk space and reduces memory use in the buffer cache, and is completely transparent to applications. Compression ratios as high as 3.5 : 1 can be achieved. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for DML.
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009
|
CREATE TABLESPACE ...
DEFAULT COMPRESS;
conn / AS sysdba
CREATE TABLESPACE compressed
DATAFILE 'compressed.dbf' SIZE 5M
DEFAULT COMPRESS;
SELECT tablespace_name, def_tab_compression
FROM dba_tablespaces;
ALTER USER uwclass
QUOTA unlimited ON compressed;
conn uwclass/uwclass
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
CREATE TABLE comptab
TABLESPACE compressed AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
exec DBMS_STATS.gather_table_stats(USER, 'REGTAB');
exec DBMS_STATS.gather_table_stats(USER, 'COMPTAB');
SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
conn / AS sysdba
DROP TABLESPACE compressed INCLUDING CONTENTS AND DATAFILES; |
