| Team Fly | |
scrambling for more space in order to load new data. In addition to the partitioning abilities discussed in the previous section, Oracle Database 10g has the ability to compress your data and indexes to further address the concerns of performance and maintenance.
Compression can be performed at the data or index levels. In this section, we will discuss the options available with Oracle Database 10g and their impacts.
With data compression, duplicate values in a database block are removed, leaving only a reference to the removed value, which is placed at the beginning of the block. All of the information required to rebuild the data in a block is contained within the block.
By compressing data, physical disk space required is reduced, and disk I/O and memory usage are also reduced, thereby improving performance. However, there are some cases when data compression is not appropriate. The following should be considered when looking at whether or not to compress data:
Does the table exist in an OLTP or data warehousing environment? Data compression is best suited for data that is updated infrequently or, better yet, is read-only. Since most data in a data warehouse is considered read-only, data compression is more compatible with this type of environment.
Does the table have many foreign keys? Foreign keys result in a lot of duplicate values in data. Tables with these structures are ideal candidates for data compression.
How will data be loaded into the table? Even when compression is enabled, data is only compressed during bulk loading (for example, SQL*Loader). If data is loaded using a standard insert into statement, the data will not be compressed.
Compression can be specified for various data-related objects using the create or alter object commands. Table 9-13 identifies these objects and their first-level parent object, from which default compression properties are inherited if not specified for the base object. For example, if no compression property is specified for a table, it will inherit the property from its tablespace. The same applies to a data partition—if not specified at the partition level, the default property from the table will be used.
| Team Fly | |