Forum Stats

  • 3,853,729 Users
  • 2,264,259 Discussions
  • 7,905,436 Comments

Discussions

Updating Compressed table

826230
826230 Member Posts: 9
edited Jan 6, 2011 11:40AM in Advanced Compression
Hi,

I'm currently testing the benefits of Oracle 11g OLTP Compression. While everthing works great (like space savings of 50%, faster table scans), it is "updates" that made things go for a toss.
Observations:
I have a table with around 400,000 records. If I issue update on fewer records say 2000 or 3000, things are fine. But if I issue update on the entire column, things go worse. The size of the table is increased dramatically. What is the reason behind this? Is it just because data is written in uncompressed form and future inserts would kick in compression?
I dont think its ROW-CHAINING issue here, as I'm not inserting data. ROW-MIGRATION is definite case but as per the ORACLE documents, Migrated rows are compressed.

Pls shed some light on this. I need to know why the size of the table increases on BULK UPDATES.


Thank You.

Answers

  • mrmessin
    mrmessin Member Posts: 287
    Version 11.1 or 11.2 also could you give some detail on how the table was created, also check that the compression type is correct, in a lot of cases situtations like this found that the compression type was not as expected.

    select owner, table_name, compression from dba_tables where owner = '<your owner>' and table_name = '<your table name>' ;
  • 826230
    826230 Member Posts: 9
    Version:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    SQL> select owner, table_name, compression, compress_for from dba_tables where table_name='COMPRESS_TABLE';

    OWNER TABLE_NAME COMPRESS COMPRESS_FOR
    ------------------------ ------------------------------ -------- ------------
    TESTC COMPRESS_TABLE ENABLED OLTP


    I think the tables are rightly compressed. My concern is performance impact of updates.
    Pls share if you know why the size of the table increases on BULK UPDATES and why updates on Compressed table take longer?


    Any suggestion would be appreciated.
  • 826230
    826230 Member Posts: 9
    Awaiting experts' suggestions pls..
  • 790846
    790846 Member Posts: 52
    There where couple of bugs similar to your case 11.1.0.7.(For example 9667930). I would suggest you to create a test case and upload to Oracle Support for help.
This discussion has been closed.