3 Replies Latest reply on Nov 6, 2012 5:26 PM by LaserSoft

    Can I compress existing data

    719190
    719190 Dec 2, 2010 1:17 AM
      Can I compress data that is already in a table/partition that is not already compressed, INLINE, and not have to unload/reload?

      Thanks,

      Rich
        • 1. Re: Can I compress existing data
          mrmessin
          mrmessin Dec 2, 2010 2:41 PM (in response to 719190)
          You will have to reload the data in some fashion. You can compress an individual partition at a time through the move of a partition or table as well
          • 2. Re: Can I compress existing data
            916290
            916290 Jul 6, 2012 5:41 PM (in response to mrmessin)
            yes use following command to compress existing data in partition...
            alter table TEST_TAB move partition TEST_TAB_2011 compress for oltp;

            Following only modifies new data.
            alter table TEST_TAB modify partition TEST_TAB_2011 compress for oltp;
            • 3. Re: Can I compress existing data
              LaserSoft
              LaserSoft Nov 6, 2012 5:26 PM (in response to 719190)
              Hi

              To compress an existing table, the following 3 approaches can be used:

              Method 1:

              Option 1 :

              SQL> select TABLE_NAME,COMPRESSION from USER_TABLES where table_name='TEST';
              SQL> ALTER TABLE T1 move COMPRESS;
              SQL> select TABLE_NAME,COMPRESSION from USER_TABLES where table_name='TEST';

              Option 2 :

              SQL> select table_name, partition_name, compression from user_tab_partitions where table_name='SALES';
              SQL> ALTER TABLE sales MOVE PARTITION southwest TABLESPACE TS COMPRESS;
              SQL> select table_name, partition_name, compression from user_tab_partitions where table_name='SALES';


              NOTE: All constraints, triggers or other dependent objects stay the same but the indexes are marked as unusable. To mark the indexes as usable :

              Rebuild the indexes

              OR

              Specify UPDATE INDEXES as part of the ALTER TABLE statement

              Method 2 :

              CREATE TABLE AS SELECT

              This method allows for creation of a new compressed table without the indexes or constraints.
              Constraints,Indexes if required can be created on the newly created table.


              Method 3 :

              3, DBMS_REDEFINITION:

              ALTER TABLE and MOVE statements takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes.If this is not acceptable, the Oracle Database online redefinition utility (the DBMS_REDEFINITION PL/SQL package) can be used:

              Thanks
              LaserSoft