Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Compressed Direct Load Jump to:  
Category: >> Oracle PL/SQL >> Compressed Direct Load Bookmark and Share

<< lastnext >>

Snippet Name: Compressed Direct Load

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 Tablespace
» 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 TABLE ...
 
COMPRESS FOR DIRECT_LOAD OPERATIONS;
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT /*+ APPEND */ *
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
 
CREATE TABLE comptab
TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS
SELECT /*+ APPEND */ *
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');


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 75 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?