5 Replies Latest reply: Apr 27, 2011 1:42 PM by jhall RSS

    Help to enable Advanced Compression

    818314
      Hi,
      I'm trying to create a table using COMPRESS FOR OLTP option but when I run "alter table myTable compress for oltp;" an "invalid ALTER TABLE option" occurs.
      I'm on 11gR2, my compatible value in gv$parameter is 11.2.0.0.0.
      Any ideas?

      Thank you.
        • 1. Re: Help to enable Advanced Compression
          damorgan
          Where did you see DDL indicating you could use ALTER TABLE to turn a non-compressed table into a compressed table?

          Please provide the link.

          What you want to do is create the table with compression enabled then insert your existing data into it using DIRECT LOAD.
          • 2. Re: Help to enable Advanced Compression
            pdegroot
            Hi Seph,

            Are you using 11gR2 Enterprise Edition? Table compression is only available in EE, any other edition might tell you that the statement is invalid. The flavour you want (for OLTP) is an extra option on top of EE, which you'll have to be willing to pay extra for, if you want to use it for real.

            See the [URL http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB]Oracle Database Licensing Information docs for details.

            Peter.
            • 3. Re: Help to enable Advanced Compression
              rbalila-Oracle
              My understanding is that you can turn on compression for a non-compressed table and that only data inserted afterwards will get compressed, existing non-compressed data will remain uncompressed.
              • 4. Re: Help to enable Advanced Compression
                858097
                Hi Dan, how about here:

                http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/tablecls.htm#CNCPT89136

                Compression Declaration

                You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in this tablespace are compressed by default.

                The following statement applies OLTP compression to the orders table:

                ALTER TABLE oe.orders COMPRESS FOR OLTP;

                Whoops, I'll have to update my username, Andy Rivenes

                Edited by: user12090872 on Apr 27, 2011 8:31 AM
                • 5. Re: Help to enable Advanced Compression
                  jhall
                  damorgan wrote:
                  Where did you see DDL indicating you could use ALTER TABLE to turn a non-compressed table into a compressed table?

                  Please provide the link.

                  What you want to do is create the table with compression enabled then insert your existing data into it using DIRECT LOAD.
                  What you want to do is create the table with compression enabled then insert your existing data into it using DIRECT LOAD.



                  This is 1 option to compress a table, but you don't have to do it like this. I have compressed tables as follows:

                  - First, alter the table to enable compression on it
                  ALTER TABLE "SCHEMA"."TABLE" COMPRESS FOR OLTP;

                  - Second, a reorg is required because existing table data is not compressed
                  ALTER TABLE "SCHEMA"."TABLE" MOVE;

                  I usually reorg large tables in parallel to speed up this process.