13 Replies Latest reply: Aug 7, 2012 9:51 AM by 917041 RSS

    Compressing the Table

    917041
      Hi,

      Oracle Release 11.2.0.3.0

      I am using the compress option while creating the table like
      CREATE TABLE <tablename>
      (
      ......
      ......
      ......
      ) parallel compress nologging;
      Now I am getting a feed back that the data is not compressed. How to check whether data is compressed or not when USER_TABLES/USER_TAB_SUBARTITIONS are showing the table to be compressed.
      Do I need to compress it exclusively?

      Regards,
      Vikash
        • 1. Re: Compressing the Table
          Girish Sharma
          SQL> ed
          Wrote file afiedt.buf
          
            1  create table test
            2  (
            3  col1 number,
            4  col2 number
            5* ) parallel compress nologging
          SQL> /
          
          Table created.
          
          SQL> select compression from user_tables
            2  where table_name='TEST';
          
          COMPRESS
          --------
          ENABLED
          How to check whether data is compressed
          compression COLUMN OF DBA/USER_TABLES
          Do I need to compress it exclusively?
          No.

          Regards
          Girish Sharma
          • 2. Re: Compressing the Table
            Helios-GunesEROL
            Hi;

            What you see by bellow query:
            select TABLE_NAME,COMPRESSION from USER_TABLES where table_name='TEST';

            Regard
            Helios
            • 3. Re: Compressing the Table
              Karan
              The data should not immediately get compressed...
              • 4. Re: Compressing the Table
                917041
                Its ENABLED:

                TABLE_NAME COMPRESS
                ------------------------------ --------
                TEST ENABLED


                But I am getting feedback that data is not compressed. Is there some other data dictionary where I can check for data compression.
                • 5. Re: Compressing the Table
                  Girish Sharma
                  vikash verma wrote:
                  Its ENABLED:

                  TABLE_NAME COMPRESS
                  ------------------------------ --------
                  TEST ENABLED


                  But I am getting feedback that data is not compressed. Is there some other data dictionary where I can check for data compression.
                  Then there are chances that either feedback sender is not addressing the compressed table, or you are not getting the proper feedback, because when data dictionary is saying that compression is ENABLED; it means it is ENABLED, no other thought / doubt / reinventing the wheel. Oracle never tells lie...!

                  Regards
                  Girish Sharma
                  • 6. Re: Compressing the Table
                    Aman....
                    And how is that person getting to know that the compression is not enabled?

                    Aman....
                    • 7. Re: Compressing the Table
                      John Spencer
                      You, or whoever is giving the feedback that the data is not compressed, do realize that the data is only compressed for storage, and that Oracle uncompresses the data before sending it back to the user right?

                      John
                      • 8. Re: Compressing the Table
                        Iordan Iotzov
                        It appears that you are trying to use BASIC compression -http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN11630

                        Please note that “Rows inserted without using direct-path insert and updated rows are uncompressed.”

                        That means that if data was inserted with a simple INSERT, then no compression would have happened.

                        Change the compression method (if you have appropriate license) or change the way the data is inserted (by using direct-path insert) in order get compression.

                        Iordan Iotzov
                        http://iiotzov.wordpress.com/
                        • 9. Re: Compressing the Table
                          Karan
                          As its not oltp compression, its basic compression, it would not be compressed immediately like i said, also it will be compressed at the time of bulk load operations like CREATE TABLE AS SELECT or like insert with append hint... In your case it will not be compressed during updates and other operations.... and also you should have shown the results for COMPRESS_FOR column which is more important rather than COMPRESSION column

                          Regards
                          Karan

                          Edited by: Karan on Aug 4, 2012 7:06 AM
                          • 10. Re: Compressing the Table
                            917041
                            Hi Iotzov,

                            I am using BASIC COMPRESS method but direct loading mode in sqlldr. I think I should go with OLTP COMPRESS option, Is there anything I need to take care about before changing the compression type? Is there any adverse effect compared to BASIC compression method.

                            Thanks,
                            Vikash
                            • 11. Re: Compressing the Table
                              917041
                              Hi Karan.
                              Its showing BASIC in COMPRESS_FOR column. But I am loading 65GB size data files to the table. When I compressed it after the load, there was a release of 1GB of space.
                              Regards,
                              Vikash
                              • 12. Re: Compressing the Table
                                sb92075
                                vikash verma wrote:
                                Hi Karan.
                                Its showing BASIC in COMPRESS_FOR column. But I am loading 65GB size data files to the table. When I compressed it after the load, there was a release of 1GB of space.
                                are you bragging or complaining about having released 1GB of space?
                                How does the cost of 1GB compare to you time spent on this effort?
                                • 13. Re: Compressing the Table
                                  917041
                                  Neither I am bragging nor complaining. I am telling the fact. Definetly it took around 30 Mins to get that 1GB of space but still I need to get where I am wrong. Thats what I am looking for in this forum.

                                  Regards,
                                  Vikash