Quick Search:
 
 The Oracle COMPRESS Clause      [Return To Index] Jump to:  

Term: COMPRESS

Definition:
By using a compression algorithm specifically designed for relational data, Oracle is able to compress data with a higher efficiency than most standard compression techniques. Another benefit is that unlike other compression techniques, Oracle incurs virtually no performance penalty for SQL queries accessing compressed tables. In some cases compression may result in a slower transaction throughput, but it creates fewer writes because of a higher row density on the data block.

The table compression clause is valid only for heap-organized tables. Use the COMPRESS to instruct the database whether to compress data segments to reduce disk use.

Example Syntax:

CREATE TABLE <table_name> (
column_name_1,
column_name_2,
...
column_name_n, )
TABLESPACE <name> COMPRESS;


Example Usage:

CREATE TABLESPACE compressed
DATAFILE 'compressed.dbf' SIZE 5M
DEFAULT COMPRESS;

SELECT tablespace_name, def_tab_compression
FROM dba_tablespaces;

ALTER USER testexample
QUOTA unlimited ON compressed;

conn testexample/testexample

CREATE TABLE regtab
TABLESPACE testdata 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';



Related Links:

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org