I speak at a number of Oracle User Group Conferences throughout the year on the topic of Advanced Compression best practices. But not all Oracle users have the opportunity to attend these user group meetings, so I thought it would be useful to bring together many of the best practices discussed into a single blog.
Best practice suggestions (insights) for Advanced Row Compression, Hybrid Columnar Compression, Advanced LOB Compression and Index Compression includes:
- Advanced Row Compression, and Hybrid Columnar Compression, is NOT supported for use with tables that have LONG data types
- Space usage reduction with Advanced Row Compression gives the best results where the most duplicate data is stored (low cardinality). Sorting data (on the columns with the most duplicates) prior to bulk loads may increase the compression ratio (at the expensive of additional load preparation)
- Larger blocks do not always ensure higher Advanced Row Compression ratios. Testing with your own data is highly suggested if you want to determine if larger/smaller block sizes will have an impact on your compression ratio and to understand any potential performance impact
- LOBs over 4k are best stored using SecureFiles. Typically, Advanced LOB Compression (used with SecureFiles) will compress LOBs better than LOBs stored in-line
- Although CPU overhead is typically minimal, implementing compression is ideal on systems with available CPU cycles, as compression will have additional, although minor overhead for some DML operations
- The best test environment for each compression capability is where you can most closely duplicate the production environment– this will provide the most realistic (pre- and post- compression) performance and functionality comparisons
- The general recommendation is to compress all of the application related tables in the database with one exception: if the table is used as a queue. That is, if the rows are inserted into the table, then later most or all of the rows are deleted, then more rows are inserted and then again deleted. This type of activity is not a good use case for compression due to the overhead to constantly compress rows that are transient in nature
- Index Key Compression (also known as prefix compression) can be very beneficial when the prefix columns of an index are repeated many times within a leaf block. However, if the leading columns are very selective, or if there are not many repeated values for the prefix columns, then Index Key Compression would not be beneficial
- Advanced Row Compression, and Hybrid Columnar Compression, work well with tablespace-level encryption. Tables are compressed before encryption, so the compression ratio is not affected by the encryption. With column-level encryption, the encryption is done before compression, which will negatively impact the compression ratio
- Compression Advisor is a PL/SQL package that is used to estimate potential storage savings for Advanced Row Compression, Advanced Index Compression, Advanced LOB Compression and Hybrid Columnar Compression. It provides a good estimate of the actual compression ratio that will be obtained after implementing compression. Compression Advisor (DBMS_COMPRESSION) is built in to Oracle Database 11g Release 2 and above
- Index-Organized Tables (IOT's) are essentially indexes, so they cannot be compressed with Advanced Row or Basic Compression. IOT’s can be compressed with Prefix Compression
- Data Pump data compression is completely independent of Advanced Row Compression. The Data Pump dump file is uncompressed inline during the import process, and the data is then imported into the target table based on the compression characteristics of the table
Hopefully in next month’s blog I’ll be able to announce a new Oracle Technical White Paper that discusses the role Oracle features, which includes Database In-Memory, Heat Map, Automatic Data Optimization, Advanced Row Compression, Hybrid Columnar Compression (and many more) play in regards to ensuring that an organizations data usage (transactional, reporting and/or analytical), compression and storage is best suited to where the data is in its lifecycle… stay tuned for more information.
Also, if you are attending the Rocky Mountain User Group Training Days (#TD2019) in February or the Oracle User Group Norway Spring Conference (#OUGN18) in March, please stop by my compression and/or Flashback Data Archive sessions. More information about these events next month.
For a more detailed discussion about many of the best practices discussed above, please see this Advanced Compression White Paper
The database storage optimization adventure continues.