8 Replies Latest reply: Feb 16, 2013 4:26 PM by rp0428 RSS

    Does compressing a table reset the highwater mark

    Bhavi Savla
      Hi,
      I want to compress a table/partition using the following command:
      alter table <table_name> move compress;
      I want to reduce the datafile size after compression.
      My question is does using the above command change the HighWaterMark ? So that I can directly reduce the datafile size?

      Kindly also mentioned the required steps to be taken.
        • 1. Re: Does compressing a table reset the highwater mark
          Justin_Mungal
          Bhavi Savla wrote:
          Hi,
          I want to compress a table/partition using the following command:
          alter table <table_name> move compress;
          I want to reduce the datafile size after compression.
          My question is does using the above command change the HighWaterMark ? So that I can directly reduce the datafile size?

          Kindly also mentioned the required steps to be taken.
          ALTER TABLE...MOVE creates a new segment for the table. So, yes, you can change the HWM with it, among other things.

          http://docs.oracle.com/cd/E14072_01/server.112/e10595/tables006.htm#i1106606

          -Justin
          • 2. Re: Does compressing a table reset the highwater mark
            Helios-GunesEROL
            Hi

            I suggest please review:
            Optimizing Database disk space using Alter table shrink space/move compress [ID 1173241.1]

            Regard
            Helios
            • 3. Re: Does compressing a table reset the highwater mark
              rp0428
              >
              I want to reduce the datafile size after compression.
              My question is does using the above command change the HighWaterMark ? So that I can directly reduce the datafile size?
              >
              Whether you can reduce the datafile size after the 'move' will depend on at least to things:

              1. Do any other objects exist in the datafile AFTER the table/partition? If so you won't be able to reduce the datafile to make up the difference unless yo ufirst move those other objects.

              2. Did you compress the table/partition into the same tablespace? If so then the new compressed version may now reside at the end of a datafile for the tablespace. See problem #1.

              So IF (possibly a big if) the table/partition is the last object in its datafile and you 'move'/'compress' it to a different datafile then yes, you will be able to resize the original datafile to recover the (now unused) space.

              If you compress into the same tablespace/datafile you will be using MORE space since you will not be able to recover the original space and are now using a second set of space.
              • 4. Re: Does compressing a table reset the highwater mark
                TSharma-Oracle
                Yes it does. It creates a new segment and with compression it is going to take less space BUT whether you can shrink the file, it depends upon what is at the end of the datafile.
                You can move the table in a different tablespace and than you can shrink the file.

                Create new tablespace
                Move all objects to new tablespace and rebuild all indexes into new tabelspace
                Drop old tablespace OR Shrink that old tablespace (depends what you want)

                But by just moving 1 table, it is not guranteed that you will be able to shrink the datafile.
                • 5. Re: Does compressing a table reset the highwater mark
                  Justin_Mungal
                  TSharma wrote:
                  Yes it does. It creates a new segment and with compression it is going to take less space BUT whether you can shrink the file, it depends upon what is at the end of the datafile.
                  You can move the table in a different tablespace and than you can shrink the file.

                  Create new tablespace
                  Move all objects to new tablespace and rebuild all indexes into new tabelspace
                  Drop old tablespace OR Shrink that old tablespace (depends what you want)

                  But by just moving 1 table, it is not guranteed that you will be able to shrink the datafile.
                  Tim Hall wrote a pretty good article covering some various options:
                  http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php

                  -Justin
                  • 6. Re: Does compressing a table reset the highwater mark
                    Bhavi Savla
                    Thank you everyone for your responses. This is very much clear now!

                    My next question is I have a table with 20 partitions. Do I have to compress individual partitions separately or compressing the table will compress the partitions automatically?
                    • 7. Re: Does compressing a table reset the highwater mark
                      Justin_Mungal
                      Bhavi Savla wrote:
                      Thank you everyone for your responses. This is very much clear now!

                      My next question is I have a table with 20 partitions. Do I have to compress individual partitions separately or compressing the table will compress the partitions automatically?
                      It's all in the documentation my friend...
                      http://docs.oracle.com/cd/E11882_01/server.112/e17120/tables002.htm

                      Changing the Compression Level

                      You can change the compression level for a partition, table, or tablespace. For example, suppose a company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free disk space.

                      If a table is partitioned, then the DBMS_REDEFINITION package can change the compression level of the table. This package performs online redefinition of a table by creating a temporary copy of the table that holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level of the existing table and the new table. Ensure you have enough hard disk space on your system before using the DBMS_REDEFINITION package.

                      If a table is not partitioned, then you can use the ALTER TABLE...MOVE...COMPRESS FOR... statement to change the compression level. The ALTER TABLE...MOVE statement does not permit DML statements against the table while the command is running.

                      To change the compression level for a partition, use the ALTER TABLE...MODIFY PARTITION statement. To change the compression level for a tablespace, use the ALTER TABLESPACE statement.

                      -Justin
                      • 8. Re: Does compressing a table reset the highwater mark
                        rp0428
                        >
                        My next question is . . .
                        >
                        Your next question should be posted in a new thread.