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.

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.

Peter de Groot

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

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

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

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.