10 Replies Latest reply: Aug 6, 2014 9:31 AM by Laury RSS

    Empty blocks after compression

    Laury

      Hi,

       

      After having compressed a table, is it still possible to get emty blocks? In this scenario, how can we compress further?

       

      Thanks by advance.

      Kind Regards.

        • 1. Re: Empty blocks after compression
          sol.beach

          NO

           

          You seem to have contracted Compulsive Tuning Disorder.

           

          Why are you obsessed with eliminating empty block?

          • 2. Re: Empty blocks after compression
            B Dave

            No,

            Why don't you let some space for updates.

             

            You can do it by re-organization of table. But it will take lot of time, specially if size of table is large.

            Also, you have to consider indexes, constraints and many things while doing re-org.

             

            HTH

            Dave

            • 3. Re: Empty blocks after compression
              Aman....

              user8058501 wrote:

               

              Hi,

               

              After having compressed a table, is it still possible to get emty blocks? In this scenario, how can we compress further?

               

              Thanks by advance.

              Kind Regards.

              No, there won't be any more empty blocks.

               

              Aman....

              • 4. Re: Empty blocks after compression
                Laury

                Hi,

                 

                Thanks for these interesting answers.
                I am a "new bee" with "compression". No, I am not obsessed by empty blocks.
                But I am concened by a very big table of about 500 GB, which is compressed.
                This table is also partitioned.

                 

                So, if I well understand it is not possible to het empty blocks? Even if updated/deletes are done against this table?

                 

                Partitioned indexes are created against this table, but they are not compressed.
                Except from reducing disk storage, what advantage would I have from compressing indexes (as opposed to compressed tables)?


                Thanks and Regards

                • 5. Re: Empty blocks after compression
                  arpit sinha

                  The compression have no effect on existing data, only on new operations applied to the table.

                  • 6. Re: Empty blocks after compression
                    Laury

                    So, if data is deleted/updated, it will affect the compression?

                    What about empty blocks in this scenario?

                    Kind Regards

                    • 7. Re: Re: Empty blocks after compression
                      Mike Kutz

                      This table will only compress data during bulk load operations.

                      NOTE WELL!!!  Any updates will cause that data block to become uncompressed and won't be recompressed!!

                      CREATE TABLE T ( ... ) COMPRESS;
                      

                       

                      This table will compress the data for OLTP operations. (ie UPDATES/DELETES)

                      It requires the Advance Compression Add-on ($$) and 11g+

                      CREATE TABLE T (....)  COMPRESS FOR OLTP; -- note: syntax changed between 11.1 and 11.2
                      

                       

                      These have higher compression ratios than others by using Hybrid Columnar Compression method.

                      They requires a supporting oracle storage system (I believe, the Adv. Compression add-on also)

                      (ie Exadata, possibly ZFS)

                      CREATE TABLE T (...) COMPRESS FOR QUERY;
                      CREATE TABLE Z (...) COMPRESS FOR ARCHIVE;
                      

                       

                      12c's ILM clauses will allow you to automagically compress historical data by partition.

                       

                      MK

                       

                      PS - don't worry about "empty blocks".  You're going to use them up, eventually.

                      • 8. Re: Empty blocks after compression
                        Laury

                        Hi,

                         

                        Thanks, interesting.
                        But I think that empty blocks will be a problem if the policy for the table is not well chosen.
                        I mean the following: if the table is essentially "read only" and pretty "big", compression is very probably a good option.
                        But I guess this is not suitable at all if significant updates/deletes are performed on that table (thus the policy used for that table is changed for instance).
                        Besides that, in these scenarios, I might also face "fragmentation" problems, due to the fact a row or updated row might not fit anymore in a block where there is compressed data (which mean that it could be even worse). Correct me if I am wrong.

                        Is there a way to compress a table when the table has already rows, or does this feature striclty apply to bulk-loads?

                         

                        Kind Regards.

                        • 9. Re: Empty blocks after compression
                          Mike Kutz

                          user8058501 wrote:

                          But I guess this is not suitable at all if significant updates/deletes are performed on that table (thus the policy used for that table is changed for instance).

                          This is what the Advance Compression add-on is used for.... COMPRESS FOR OLTP

                           

                          Is there a way to compress a table when the table has already rows, or does this feature striclty apply to bulk-loads?

                          You can compress the data that is in a table after you add rows to it by using ALTER TABLE T COMPRESS MOVE.

                          However, it will take {current disk space} + {new disk space} + {some overhead disk space} to do it.

                           

                          I don't know your exact requirements, but, it sounds like, your best bet would be to Partition your data table by week/month/year and run a job that compresses that partition and moves it to a different tablespace.

                          (12c's ILM clause for CREATE/ALTER TABLE allows you to do this auto-magically.)

                           

                          MK

                          • 10. Re: Empty blocks after compression
                            Laury

                            Hi,

                             

                            Thanks, very interesting post.
                            And of course for this option, a special license above the EE is necessary.

                             

                            Kind Regards.