Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle COMPRESS Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 177 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?