Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle column level compression

IT Tips by Donald BurlesonSeptember 5,  2015

Column oriented data storage for Oracle

In traditional relational theory, the internal representation of the data on the physical blocks is not supposed to matter, but in the real world, the placement of the data on blocks is critical.  Oracle provides tools like sorted hash clusters to group related rows together and row-sequencing can dramatically improve the performance of SQL queries by placing all information on a single data block.

Using Oracle cluster table, you can even group related tables together on the same data block.  For example, if you have a busy OLTP database where millions of people query customer and related order rows all day long:


An un-clustered table has high I/O overhead

If you use Oracle cluster tables to put the customer and order rows together on a single data block, greatly reducing the number of trips to the database to fetch the desired result set.


The placement of rows on physical data blocks makes a huge performance difference


See my note here on Oracle 11g table compression and note the tradeoff between run-time performance of the SQL, vs. the processing overhead of compressing and de-compressing the rows. 


Source: Oracle Corporation

The point is that this type of compression requires overhead, and the less volatile the table, the better the overall performance.

11gR2 column level data storage for Exadata servers

These column oriented database have another significant advantage, because they store adjacent column data, they can use compression algorithms to detect patterns in the columns and achieve very high rates of data compression.  This packs more data onto each data block, making data warehouse queries run even faster.

But what about applications like DSS that want data stored with related columns on the data blocks?  While traditional database system wants to group related data items together, data warehouse applications prefer to see related columns of data grouped together on the data blocks.

In a OLAP or DSS system, we are analyzing "facts", individual column values, and we rarely needs row-oriented data display.  Hence, it does not make sense to physically store data in rows format.  Instead, we break-apart the rows and store the column values adjacent to one another on the data blocks.

Oracle guru Guy Harrison of Quest Software  has this great illustration of rows storage vs. column storage on Oracle data blocks:

 


Source:  Guy Harrison

This is very foreign to most relational databases, and column oriented database are similar to indexes, in that they store ROWID and column value pairs together on the data blocks.  These column  oriented databases were first implemented of guru's like professor Michael Stonebreaker at MIT with their C-store column-oriented database.

While the 11g compression is row-level compression we now see a Oracle 11g Release 2 new feature called 11g column-level compression. 

This option is only available on the Oracle/HP Exadata storage devices, million dollar firmware disks that are optimized for data warehouse data. 

The column level compression for a table has syntax like this:

create table
   tot_sales_compressed
compress for
   archive level=3 
as
select
   sales_facts
from
   sales s;

Note the archive level syntax.  According to the Oracle documentation, the archive level arguments s[specifies the amount of compression, and this is directly related to the processing overhead at compress-decompress time.

Oracle guru Guy Harrison of Quest Software  performed a benchmark on the 11g Release 2 compression with these results:

Today, it appears that this new column-level data storage is only available on the million dollar Exadata storage boxes being sold by Oracle and HP.  If you attempt to define column-level compression on a non Exadata server to get this error message:

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage

Cause: An attempt was made to use hybrid columnar compression on unsupported storage.

Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational