Starting with Oracle 8i oracle introduce to compress "index" or "IOT" table.
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c08schem.htm#4602
Oracle 9ir2 Introduce head organized TABLE compression.
Oracle 9ir2 compresses data by eliminating duplicate values in a database block.
We can compress tables and materialized views. For partitioned tables, it is possible to choose to compress some or all partitions.
Compression attribute can be declared for a tablespace, table or partition level.Compression occurs while data is being bulk inserted or bulk loaded these operation include.
1. Direct Path SQL * loader
2. Create table ... as select statement
3. Parallel INSERT
4. Serial INSERT with an APPEND hint
Data compression works for all data types except LOB.
SQL> create table T1 as select * from all_objects;
Table created.SQL> --COMPRESSION is not enable for the table
SQL> exec dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.SQL> select round(blocks*8) tablesize from
2 dba_tables where table_name='T1';
TABLESIZE----------
7944
SQL> --Now enable compression for the table
SQL> alter table T1 move COMPRESS;
Table altered.SQL> exec dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.SQL> select round(blocks*8) tablesize from
2 dba_tables where table_name='T1';
TABLESIZE----------
2208
1.We can Enable COMPRESSION at Table Creation time.
CREATE TABLE TABLE_NAME(COLUMN DATATYPE) COMPRESS;
2. We can enable table compress on existing table
ALTER TABLE TABLE_NAME MOVE COMPRESS;
3. For view compression is enable or disabled for the table check USER_TABLES views
select TABLE_NAME,COMPRESSION
from user_tables
where table_name='TABLE_NAME';
4. For DISABLE compression for the table
ALTER TABLE TABLENAME MOVE NOCOMPRESS;
If you want to read more please refer following links
http://www.oracle.com/technology/products/bi/pdf/o9ir2_compression_twp.pdf
http://www.vldb.org/conf/2003/papers/S28P01.pdf
Thursday, July 24, 2008
Oracle Table Compression
Subscribe to:
Post Comments (Atom)
2 comments:
I tried to use compression in Oracle 11g (for all operations). However when I collected statistics it seems there is no saving. I have common data in most of the columns and expected 30-40% saving in space. Any suggestions why benefits of compression are not observed. I inserted around 500-1000 rows.
Hmmm...is it possible 500-1000 rows wasn't enough? Tables are physically stored in blocks (typically 8k) and then segments that grow by an extent size.
Depending on your data, if you were inserting 1000 rows in a table, and each row was a few characters, it may fit within a single block...so you wouldn't see any change in the segment size. Similarly, if the table size+1000 rows wasn't enough to cause the table to grow beyond the minimum extent size, you might not see a change.
Consider posting your code as an example. Also, try your test again using a lot more data. A trick I use is:
insert into table select * from table;
-- repeat over and over
To gather compression results, this isn't a good idea because you're copying all the same data over and over...making it appear your data is more compressible than it actually is, but it is an easy way to cause growth in your table.
Something else to consider when compressing your tables is...how will it affect your performance? Take a look at this:
http://otipstricks.blogspot.com/search/label/Exadata%20Compression%20OLTP%20HCC%20Swingbech%20Index
Post a Comment