Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at email@example.com. Please include your name and telephone number in your email.
This month’s question comes to us from Stephanie in Albuquerque, NM: How does Oracle’s table compression feature work? In what situations should table compression be used, and how does it affect performance?
Hamid Minoui of the Database Specialists team responds: Oracle introduced the table compression feature in Oracle 9i Release 2. Table compression in Oracle is accomplished by eliminating duplicate values within a database block and replacing them with short references to entries in a symbol table. A symbol table, also known as a dictionary, is automatically created in each database block where data has been compressed.
This design makes each database block of a compressed table self-contained, meaning that all information needed to uncompress a database block is available within the block itself. Individual partitions of a partitioned table can be compressed, as can materialized views.
A symbol table is only created in database blocks that have been compressed, and not all blocks of a compressed table are necessarily compressed. Blocks that contain values with little repetition, or very short values, are not compressed by Oracle’s table compression algorithm.
For tables with a lot of duplicate values, Oracle’s table compression feature can dramatically reduce the size of the table segment. This can reduce disk space usage and buffer cache requirements, which in turn can reduce misses in the buffer cache. In many cases improvements in I/O efficiency are gained as a result of accessing considerably fewer database blocks for the same amount of data. Full table scans, exports, and database backups are among the operations that benefit the most from table compression.
There is a small CPU overhead involved in reconstructing the compressed blocks. However, the I/O performance gains typically more that make up for it.
Table compression is targeted primarily for data warehouse environments, where the tables are read-only or read-mostly. However, tables in others environments may benefit from compression as well.
Table compression is not appropriate for tables that participate in DML operations because of the overhead associated with them and limitations on the table compression feature. Only new data that is inserted into a table via a bulk load operation is eligible for compression. Bulk loads are done using SQL*Loader’s direct path load option, INSERT statements that use the APPEND hint, and the CREATE TABLE AS SELECT statement. Compression only applies to the new data being loaded—the compression status of existing database blocks remains unchanged.
Two ways to compress previously loaded data in existing tables are:
ALTER TABLE t1 MOVE COMPRESS;
This moves table t1’s data into a new segment that is compressed. After the move operation, the indexes become unusable and need to be rebuilt. To compress one partition of a partitioned table, the statement ALTER TABLE t1 MOVE PARTITION p1 COMPRESS is used instead.
CREATE TABLE t2 COMPRESS AS SELECT * FROM t1;
This creates a compressed copy of an existing table. Indexes and constraints are left on the original table and need to be created on the new table manually. Other table dependencies such as views, triggers, and foreign keys need to be considered as well.
Note that there are some limitations to the table compression feature. As we already mentioned, only data inserted using a bulk load mechanism gets compressed. Also, LOB columns and index-organized tables cannot be compressed. In addition, tables with bitmap indexes cannot be compressed. (However, bitmap indexes can be dropped and re-created after the table has been compressed.)
Another issue to be aware of is that in Oracle 9i it is not possible to add or drop columns from a compressed table. This limitation has been lifted in Oracle 10g. (Refer to MetaLink note 281472.1 for more information.)
Oracle’s table compression feature can significantly reduce the amount of space needed to store certain types of data. This can lead to reduced disk storage and buffer cache sizing requirements, and improved performance for certain types of operations. Although primarily intended for data warehouse environments, table compression can be beneficial in many situations where data is usually loaded in bulk and used primarily read-only after that.
We came across a resource recently that’s worth taking a look at. Tim Gorman, a popular speaker and active participant in the Rocky Mountain Oracle Users Group, maintains an Oracle library with a collection of both presentations and scripts for your review and download. Some recent presentations and white papers at http://www.evdbt.com/papers.htm include:
Managing space in a data warehouse environment is often one of the most challenging tasks for data warehouse database administrators. We have just published a new white paper and presentation entitled: Practical Space Management in Data Warehouse Environments.
Authored by Staff Consultant Hamid Minoui, this detailed white paper addresses best practices for efficient use of available space, performance, and manageability in very large databases and data warehouse environments. Not only are there many data files and tablespaces to manage in data warehouse databases, but the data load volumes and the size of individual segments tend to be very large. Applying these best practices will help save space, reduce or eliminate space-related issues, and enhance query performance.
To view the white paper and presentation, see http://dbspecialists.wpengine.com/presentations.html#dw_space.
As 2005 draws to a close, we’d like to take a moment to thank all of you for the continued success of The Specialist. We hope you have enjoyed the news and resources we have shared pertaining to Oracle technology. Highlights include white papers on wait event enhancements in Oracle Database 10g, failover procedures in non-Dataguard environments, and a case study on upgrading to 10g. They’re all free and available at http://dbspecialists.wpengine.com/resources.html.
We’d like to extend warm holiday greetings to our clients and colleagues in the Oracle arena. We thank our clients for entrusting us with your mission-critical systems, whether you look to us for all of your database administration and Oracle support needs, or just for special projects. We are thrilled to contribute to your success in positive ways, and we are proud to be an active part of the Oracle user group community.
To our colleagues, thank you for your questions, participation, and feedback on our various Oracle-related resources. We encourage your input and suggestions. And we look forward to sharing more with you in 2006! Best wishes for the upcoming New Year!
- The Utility of Logon Triggers
- Materialized Views in Action
- Scaling to Infinity—Partitioning in Oracle Data Warehouses
- Supercharging STAR Transformations
At http://www.evdbt.com/tools.htm, you’ll find dozens of useful scripts, along with an alphabetical listing and description of each script. The categories are:
- General SQL*Plus scripts
- SQL*Plus scripts related to STATSPACK
- UNIX shell scripts for Oracle DBAs