In this blog we’re going to discuss Row Level Locking with Hybrid Columnar Compression, one of the many lesser-known capabilities included with Oracle Advanced Compression.
Hybrid Columnar Compression technology utilizes a combination of both row and columnar methods for storing data. A logical construct, called the compression unit (CU), stores a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows is compressed, it is stored in a compression unit.
Figure 1: Conceptual Illustration of a Logical Compression Unit (CU)
By default, Hybrid Columnar Compression uses one lock per CU. Optionally; users can choose to enable row level locking for compression units. The default with HCC is NO ROW LEVEL LOCKING, ROW LEVEL LOCKING is explicitly specified during a CREATE TABLE or ALTER TABLE operation.
The following is an example of HCC Row Level Locking syntax:
CREATE/ALTER TABLE … COMPRESS FOR [compression_type] [ROW LEVEL LOCKING | NO ROW LEVEL LOCKING]
Hybrid Columnar compressed data can be modified using conventional Data Manipulation Language (DML) operations, such as UPDATE and DELETE – but HCC is typically best suited for applications with no, or very limited DML operations. With limited (or no) DML operations, CU-level locking can often be utilized without concurrency concerns during normal query-mostly processing.
New in Oracle Database 12.2, HCC automatically compresses new data from SQL INSERT ... SELECT statements, without the APPEND hint and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI). With this new INSERT compression users may want to evaluate whether Row-Level Locking is a better solution, in regards to concurrency, than CU-level locking. As always, testing with your own data, and applications, will provide the most realistic performance and functionality comparisons.
Row level Locking, with Hybrid Columnar Compression, is available on Oracle Cloud as well as the following Oracle Engineered Systems and Oracle Storage: Exadata, SuperCluster, ZFS and FS1.
For more information about HCC, please visit http://www.oracle.com/technetwork/database/database-technologies/hybrid-col-compression/overview/hccoverviewpage-2403631.html
For now, the database storage optimization adventure continues my next blog.