메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Hybrid Columnar Compression Common Questions

명품관 2016.03.04 14:05 조회 수 : 191

Hybrid Columnar Compression Common Questions

By Gregg Christman-Oracle on Feb 26, 2016

Similar to last week, in this blog we’re going to discuss some of the most common, user asked, questions related to Hybrid Columnar Compression (HCC), these questions include:

  • How to enable Hybrid Columnar Compression for existing tables and partitions?

  • Which applications (OLTP or Data Warehouse) are best for Hybrid Columnar Compression?

  • Does Hybrid Columnar Compression require a separate license?

For existing tables and partitions, there are three best practice approaches to enabling Hybrid Columnar Compression. Both methods enable Hybrid Columnar Compression for existing data and will compress future data loaded via bulk loads, the difference between the methods is whether the table remains accessible while HCC compression is being enabled.

- Online Redefinition (DBMS_REDEFINITION)

Table remains online for both read/write activity while the statement is executing. See here for more information regarding the restrictions that apply to the online redefinition of tables.

- ALTER TABLE … MOVE PARTITION … ONLINE

Partition/subpartition remains online for both read/write activity while the statement is executing.

- ALTER TABLE … MOVE COLUMN STORE COMPRESS FOR …

ALTER TABLE MOVE does not permit DML, against the table, while the statement is executing.

Which method should you choose? Well, it’s really up to you. If you want to keep tables and partitions online and available while compression is being enabled, then using online redefinition or ALTER TABLE MOVE PARTITION ONLINE would be best. If you are taking some downtime to compress, then use ALTER TABLE MOVE – but no matter which is used, all will provide the same level of compression.

The question regarding which type of application is best suited for HCC actually isn’t really a common question, but it should be because it is important to understand the answer to this question. HCC compression can be used in both OLTP and Data Warehouse applications, but, and this is important, it is recommended that HCC be enabled only tables or partitions with no, or infrequent, DML INSERT/UPDATE operations. While data in Hybrid Columnar compressed tables can be modified using conventional Data Manipulation Language (DML) operations - INSERT, UPDATE, DELETE - performing such operations could result in a reduction of the HCC compression ratio – and if performed frequently, could degrade the HCC compression ratio over time (requiring the table/partition be re-compressed). If frequent DML operations are planned on a table or partition, then Advanced Row Compression is better suited for such data.

Whether or not HCC requires a separate license is also often misunderstood by users. To use HCC you must be running Oracle Database Enterprise Edition (11.2.0.3 and above) and, this is also important, you must be using a HCC supported Oracle platform, including Exadata, SuperCluster, ZFS Storage Appliance or FS Flash Storage System.

Much more information regarding Hybrid Columnar Compression best practices is available. Please see this Hybrid Columnar Compression white paper (pages 6 to 8) for more information.

The database storage optimization adventure continues in the next blog, in which we will discuss how Advanced Row Compression and Hybrid Columnar Compression are directly related to Information Lifecycle Management (ILMbest practices.

 

출처 : https://blogs.oracle.com/DBStorage/entry/hybrid_columnar_compression_common_questions

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 600
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 1355
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 384
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 1497
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 521
20 Quick tip on Function Based Indexes 명품관 2016.04.19 191
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 9609
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 352
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 833
» Hybrid Columnar Compression Common Questions 명품관 2016.03.04 191
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 181
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 447
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 263
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 155
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 163
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 2562
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 198
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 777
7 On Invokers and Outs file 명품관 2015.11.23 102
6 Parallel Execution 12c New Features Overview file 명품관 2015.11.23 109
5 How many checkpoints in Oracle database ? [1] file 명품관 2015.11.20 282
위로