I was looking through Oracle's OLTP Table Compression (11g onwards) documentation as well as online resources to find the syntax and came across two different versions:
COMPRESS FOR ALL OPERATIONS
COMPRESS FOR OLTP
The documentation I looked through didn't mention any alternative syntax, so i was wondering if anyone here might know the difference.
Oracle 11.1 introduced FOR ALL OPERATIONS, 11.2 introduced for OLTP and there are several other options as well such as FOR QUERY and FOR ARCHIVE, but those are only available for Exadata.
Basically FOR OLTP replaces FOR ALL OPERATIONS.
Table Compression Enhancements in Oracle Database 11G Rel1 as as follows:
The compression clause can be specified at the tablespace, table or partition level with the following options:
•NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
•COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
•COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
•COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher.