oracle compress

create table a(.....) compress for oltp;

create table a(.....) compress for all operations;

---------------------------------------------------------------

• 壓縮表不能超過255列。

• 不能刪除列，只能把列設定不null,或者不用

• 列遷移不能被壓縮.

• 外部表、族表不能被壓縮

• 表不能包含long欄位

• You cannot specify hybrid columnar compression on the following object-relational features: object tables, XMLType tables, columns with abstract data types, collections stored as tables, or OPAQUE types, including XMLType columns stored as objects.

• When you update a row in a table compressed with hybrid columnar compression, the ROWID of the row may change.

• In tables compressed with hybrid columnar compression, updates to a single row may result in locks on multiple rows. Concurrency for write transactions may therefore be affected.

• If a table compressed with hybrid columnar compression has a foreign key constraint, and you insert data using INSERT with the APPEND hint, then the data will be compressed using OLTP. To compress the data with hybrid columnar compression, disable the foreign key constraint, insert the data using INSERT with the APPEND hint, and then reenable the foreign key constraint.

[@more@]

table_compression

The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.

• When you enable table compression by specifying either COMPRESS or COMPRESS BASIC, you enable basic table compression. Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format.

  Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause.

  In earlier releases, this type of compression was called DSS table compression and was enabled using COMPRESS FOR DIRECT_LOAD OPERATIONS. This syntax has been deprecated.

  See Also:

  "Conventional and Direct-Path INSERT" for information on direct-path INSERT operations, including restrictions

• When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

  Tables with COMPRESS FOR OLTP or NOCOMPRESS use the PCTFREE default value of 10, to maximize compress while still allowing for some future DML changes to the data, unless you override this default explicitly.

  In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. This syntax has been deprecated.

• When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable hybrid columnar compression. With hybrid columnar compression, data can be compressed during bulk load operations. During the load process, data is transformed into a column-oriented format and then compressed. Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio. Hybrid columnar compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form of compression is recommended for data that is not frequently updated.

  COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.

  COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.

  Tables with COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause. For these tables, PCTFREE has no effect for blocks loaded using direct-path INSERT. PCTFREE is honored for blocks loaded using conventional INSERT, and for blocks created as a result of DML operations on blocks originally loaded using direct-path INSERT.

  See Also:

  Oracle Exadata Storage Server Software documentation for more information on hybrid columnar compression, which is a feature of Oracle Exadata

You can specify table compression for the following portions of a heap-organized table:

• For an entire table, in the physical_properties clause of relational_table or object_table

• For a range partition, in the table_partition_description of the range_partitions clause

• For a composite range partition, in the table_partition_description of the range_partition_desc

• For a composite list partition, in the table_partition_description of the list_partition_desc

• For a list partition, in the table_partition_description of the list_partitions clause

• For a system or reference partition, in the table_partition_description of the reference_partition_desc

• For the storage table of a nested table, in the nested_table_col_properties clause