10.2版本
以下内容均来自于官方文档。
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. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
When you enable 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. You can specify table compression for the following portions of a heap-organized table:
l For an entire table, in the physical_properties clause of relational_table or object_table
l For a range partition, in the table_partition_description of the range_partitioning clause
l For a list partition, in the table_partition_description of the list_partitioning clause
l For the storage table of a nested table, in the nested_table_col_properties clause
See Also:
"Conventional and Direct-Path INSERT" for information on direct-path INSERT operations, including restrictions
Oracle Database Performance Tuning Guide for information on calculating the compression ratio and to Oracle Data Warehousing Guide for information on table compression usage scenarios
Restrictions on Table Compression Table compression is subject to the following restrictions:
l Table compression is not supported for tables with more than 255 columns.
l LOB data segments are not compressed.
l You cannot specify table compression for an index-organized table, any overflow segment or partition of an overflow segment, or any mapping table segment of an index-organized table.
l You cannot define table compression explicitly for hash partitions or hash and list subpartitions. The table compression attribute for those partitions is inherited from the tablespace, the table, or the table partition setting.
l You cannot specify table compression for external tables or for tables that are part of a cluster.
Oracle Database Performance Tuning Guide
4.2.1 Table Compression
Heap-organized tables can be stored in a compressed format that is transparent for any kind of application. Table compression was designed primarily for read-only environments and can cause processing overhead for DML operations in some cases. However, it increases performance for many read operations, especially when your system is I/O bound.
Compressed data in a database block is self-contained which means that all the information needed to re-create the uncompressed data in a block is available within that block. A block will also be kept compressed in the buffer cache. Table compression not only reduces the disk storage but also the memory usage, specifically the buffer cache requirements. Performance improvements are accomplished by reducing the amount of necessary I/O operations for accessing a table and by increasing the probability of buffer cache hits.
4.2.1.1 Estimating the Compression factor
Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.
Before compressing large tables you should estimate the expected compression factor. The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form divided by the number of blocks necessary for a compressed storage. The compression factor can be estimated by sampling a small number of representative data blocks of the table to be compressed and comparing the average number of records for each block for the uncompressed and compressed case. Experience shows that approximately 1000 data blocks provides a very accurate estimation of the compression factor. Note that the more blocks you are sampling, the more accurate the result become.
4.2.1.2 Tuning to Achieve a Better Compression Ratio
Oracle achieves a good compression factor in many cases with no special tuning. As a database administrator or application developer, you can try to tune the compression factor by reorganizing the records when the compression actually takes place. Tuning can improve the compression factor slightly in some cases and very substantially in other cases.
To improve the compression factor you have to increase the likelihood of value repetitions within a database block. The compression factor that can be achieved depends on the cardinality of a specific column or column pairs (representing the likelihood of column value repetitions) and on the average row length of those columns. Oracle table compression not only compresses duplicate values of a single column but tries to use multi-column value pairs whenever possible. Without a very detailed understanding of the data distribution it is very difficult to predict the most optimal order.
Oracle Data Warehousing Guide
Table Compression
You can save disk space by compressing heap-organized tables. A typical type of heap-organized table you should consider for table compression is partitioned tables.
To reduce disk use and memory use (specifically, the buffer cache), you can store tables and partitioned tables in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a cost in CPU overhead.
Table compression should be used with highly redundant data, such as tables with many foreign keys. You should avoid compressing tables with much update or other DML activity. Although compressed tables or partitions are updatable, there is some overhead in updating these tables, and high update activity may work against compression by causing some space to be wasted.
从以上内容可以看到，10g版本是还是只支持 compress/nocompress这两个属性，即压缩或者不压缩。而如果打算用压缩，还有很多限制，具体可以看以上详细的描述，比如，列不能超过255个，压缩时pctfree为0，场景只适用于read-only，以及一些分区表的限制。
具体压缩率这里就不谈了，毕竟不同的数据压缩率不同，此时还是行级别压缩，即仍是行存储，只不过用符号代替了具体的列值，从此可以得出行数据如果在入库之前先进行了排序，那么压缩率会大很多。
此时还不支持oltp。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-1360942/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10037372/viewspace-1360942/
560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
