9 Replies Latest reply: Jul 12, 2010 9:11 PM by ca200197 RSS

    Advaced Compression Limitations -- Based on no of columns

    742409
      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
          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
            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
              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
                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
                  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
                    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
                      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'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
                          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"