This discussion is archived
9 Replies Latest reply: Jul 12, 2010 7:11 PM by ca200197 RSS

Advaced Compression Limitations -- Based on no of columns

742409 Newbie
Currently Being Moderated
URGENT NEED - Would appreciate your help -

Till date we worked on 10g where in our database is holding a partitioned table with less than 255 columns with each partition size of 70GB , and is being compressed and moved to history database ,we achieved good compression ratios in this activity.

But once the table is modified with more than 305 columns because of Business Requirement , the compresstion ratio is drastically reduced from 30 or 40 percent to only 5%.

So ,now we are looking at advanced compression i.e using , where we want to confirm whether this limitation exists even in 11g or overcomed.

Regards,
Phani Kumar P
+919948677317
phanikumar.p@tatatel.co.in
  • 1. Re: Advaced Compression Limitations -- Based on no of columns
    damorgan Oracle ACE Director
    Currently Being Moderated
    I will not respond to anyone that uses the word URGENT in a post. Using this word is an attempt to convey to us that your issue is more important than other people's issues and that is disrespectful. It also means that your issue is as important as someone who's house is on fire or who is drowning. Clearly not the case.

    After you apologize I, or others, will gladly respond. thank you.

    I will however give you one piece of unsolicited advise:
    Any table with more than 50 columns should be considered highly suspicious.
    255 columns in a table is outrageous.
    305 is pure insanity.

    Read this:
    http://www.morganslibrary.org/reference/normalization.html

    And also this:
    http://www.morganslibrary.org/files/codd-1970.pdf

    If we wanted flat files we would still be using COBOL.
  • 2. Re: Advaced Compression Limitations -- Based on no of columns
    742409 Newbie
    Currently Being Moderated
    Hi
    I apologize for the word i have used. Many Thanks for the reply.Can you suggest me how to proceed futher in overcoming the problem , since the size is growing daily which equals to 100GB of a single daily partition , we are in pressure to save the storage space cost.

    Thanks in Advance

    Regards,
    Phani Kumar P
    +919948677317
    phanikumar.p@tatatel.co.in
  • 3. Re: Advaced Compression Limitations -- Based on no of columns
    damorgan Oracle ACE Director
    Currently Being Moderated
    To advise you would require the DDL for the table and the business rules being followed that show what each column is for and its relationship to the other columns in the table.

    Generally speaking when I see more than 50 columns in a table, there is the very rare exception, it is because people are thinking horizontally not vertically. Consider the following two tables.
    CREATE TABLE bad_design (
    row_identifie r NUMBER,
    attribute1      NUMBER,
    attribute_val1  VARCHAR2(20),
    attribute2      NUMBER,
    attribute_val2  VARCHAR2(20));
    
    CREATE TABLE good_design (
    row_identifier   NUMBER,
    attribute_number NUMBER,
    attribute_value  VARCHAR2(20));
    Both can be extended to hold 500 attributes. The first one will add 1000 columns. The second none.

    Likely what you are experiencing is something like this.

    A Boeing 747 contains more than 1.2 million parts. No sane person would ever consider a plane-parts inventory to start with program_id and line_number followed by 1.199 million columns. But that is the thinking that often leads to 305 columns.

    If not then you are going to need to either post a lot of information or go back and examine what it is that appears to justify any object having 304 non-relational attributes.
  • 4. Re: Advaced Compression Limitations -- Based on no of columns
    742409 Newbie
    Currently Being Moderated
    I checked with the application team , and got the confirmation that the table structure cannot be modified right now as it is heading to many dependencies ...invalid .

    I want to know if this is the limitation of Oracle 11g with the columns greater than 255 columns cannot achieve good compression ratios.

    Regards,
    Phani Kumar P
    +919948677317
    phanikumar.p@tatatel.co.in
  • 5. Re: Advaced Compression Limitations -- Based on no of columns
    damorgan Oracle ACE Director
    Currently Being Moderated
    The Oracle database has no limitation you are going to bump into anytime soon.

    Architecture, normalization, and sanity are limits you seem to have bumped into already.

    The application team needs to be taken out for retraining.

    Fix the tables and build a denormalized view if you must but this appears to be pure lunacy.

    Editorial Comment: I love it when application folks, who know nearly nothing about relational databases, are allowed to make bad decisions and then some poor DBA is expected to somehow make it work. Consider looking them straight in the eye and saying the word "NO!"

    And if they would like me to explain it to them have them email me.
  • 6. Re: Advaced Compression Limitations -- Based on no of columns
    Stewart Bryson Oracle ACE Director
    Currently Being Moderated
    Compression works at the block level... replacing repeating values within the block with references to that value. With new columns added, I can guess a possible issue: the rows may be sorted differently going into the table, perhaps sorted on one of the new columns as opposed to a previous column.

    The way the data is sorted affects the possible repetitions of values within a block... and therefore the degree of compression.

    This could be the cause.

    Stewart Bryson
  • 7. Re: Advaced Compression Limitations -- Based on no of columns
    757025 Newbie
    Currently Being Moderated
    According to Note ID 882712.1:
    Applies to: Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2

    RESTRICTIONS ASSOCIATED WITH COMPRESSION
    1)Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
    *2)Compressed tables must not have more than 255 columns.*
    3)Compression is not applied to lob segments.
    4)Table compression is only valid for heap organized tables, not index organized tables.
    5)The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
    6)Table compression cannot be specified for external or clustered tables.


    So to answer your question, don't expect an upgrade to 11g to fix compression for tables with more than 255 columns.
  • 8. Re: Advaced Compression Limitations -- Based on no of columns
    damorgan Oracle ACE Director
    Currently Being Moderated
    Oracle's notes are interesting ... but as Tom Kyte advises ... trust but verify.
    SQL> SELECT * FROM v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE    11.1.0.7.0      Production
    TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production
    
    SQL> select count(*) from user_tab_cols where table_name = 'XXX_SPLIT';
    
      COUNT(*)
    ----------
           921
    
    SQL> CREATE TABLE compression_demo
      2  COMPRESS FOR ALL OPERATIONS AS
      3  SELECT * FROM xxx_split;
    
    Table created.
    
    SQL> SELECT table_name, compression, compress_for
      2  FROM user_tables
      3  WHERE table_name LIKE '%DEMO%';
    
    TABLE_NAME                     COMPRESS COMPRESS_FOR
    ------------------------------ -------- ------------------
    COMPRESSION_DEMO               ENABLED  FOR ALL OPERATIONS
    Looks like a few more than 255 columns.
  • 9. Re: Advaced Compression Limitations -- Based on no of columns
    ca200197 Newbie
    Currently Being Moderated
    Just because user_(dba_/all_)tables says OLTP compression is enabled doesn't mean the table is actually compressed. For tables with more than 255 columns compression is ignored even if enabled.

    Quoting Tom Kyte: "trust but verify"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points