Forum Stats

  • 3,853,732 Users
  • 2,264,259 Discussions
  • 7,905,436 Comments

Discussions

Help to enable Advanced Compression

818314
818314 Member Posts: 5
edited Apr 27, 2011 2:42PM in Advanced Compression
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.

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Feb 12, 2011 10:38PM
    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.
  • 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.
  • rbalila-Oracle
    rbalila-Oracle Member Posts: 39 Employee
    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.
  • 858097
    858097 Member Posts: 1
    edited Apr 27, 2011 11:31AM
    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
  • jhall
    jhall Member Posts: 39
    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.
This discussion has been closed.