OregonSouth Washington Oracle Users Group Three Types of

Oregon-South Washington Oracle Users Group Three Types of Table Compression A story about two choices in plain view, and a hidden forgotten third choice… Tim Gorman, Delphix Portland OR – Tue 08 -Nov 2016 Bellevue WA – Wed 09 -Nov 2016 www. Delphix. com oswoug. org 1

Agenda • The story behind the story • Overview of Data Compression • Review of Oracle storage concepts o Internal block and row formats o Table clusters, row-chaining, and direct-path loads • Table compression in Oracle o De-duplication compression (basic and OLTP) o Hybrid Columnar Compression (HCC) o Trailing NULL columns www. Delphix. com oswoug. org 2

The Story Behind The Story • This isn’t a presentation about table compression o It ended up that way, however • Instead, this began as a story about a solution to a specific problem o o o It was a lot of fun I wanted to share it But I had to fill in a lot of background before getting to the punch line Which seems to make this a presentation about compression Please bear with me for the next 59 minutes? www. Delphix. com oswoug. org 3

Data Compression • White paper: Introduction to Data Compression o Guy E Blelloch, Carnegie-Mellon University, 25 -Sep 2010 • http: //www. cs. cmu. edu/afs/cs/project/pscicoguyb/realworld/www/compression. pdf • Lempel Ziv (LZ) lossless compression methods o Simplified generic LZ algorithm • Divides source into fixed-length (i. e. 10 - or 12 -bit) patterns • Stores distinct patterns in lookup table • Replaces patterns in output stream with lookup hash value o Variations on LZ methods • DEFLATE: focuses on speed (zip, gzip, LZO, ZLIB, etc) • Layered compression: focuses on compression ratio, relatively slow, uses several layers of compression techniques (BZIP 2) www. Delphix. com oswoug. org 4

Compression in Oracle • Index compression • Table compression o Basic o OLTP* • • RMAN backup compression* Secure. File (LOB) compression* * Advanced Compression Option Data Pump export compression* * Exadata / ZFS / Pillar storage only Data Guard redo transport Oracle 8 i compression* Oracle 9 i Oracle 10 g • Hybrid Columnar compression* Oracle 11 g. R 1 Oracle 11 g. R 2 www. Delphix. com oswoug. org 5

Compression in Oracle • Index compression • Table compression o Basic o OLTP* • • RMAN backup compression* Secure. File (LOB) compression* * Advanced Compression Option Data Pump export compression* * Exadata / ZFS / Pillar storage only Data Guard redo transport Oracle 8 i compression* Oracle 9 i Oracle 10 g • Hybrid Columnar compression* Oracle 11 g. R 1 Oracle 11 g. R 2 www. Delphix. com oswoug. org 6

Table Compression CREATE TABLE. . . COMPRESS [ FOR DIRECT_LOAD OPERATIONS | BASIC ] FOR ALL OPERATIONS | COMPRESS FOR OLTP FOR QUERY [ LOW | HIGH ] FOR ARCHIVE [ LOW | HIGH ] Key • Oracle 9 i + • Oracle 11 g. R 1 • Oracle 11 g. R 2 + www. Delphix. com oswoug. org 7

COMPRESS [ BASIC ] • Similar in concept to LZ algorithm o Distinct column values stored in symbol table within block o Column values replaced by offset value into symbol table • Initial Oracle table compression implementation o No extra cost with Enterprise Edition, not available in Standard Edition o Enabled with COMPRESS in 9 i and 10 g, COMPRESS [ FOR DIRECT_LOAD OPERATIONS ] in 11 g. R 1, COMPRESS [ BASIC ] from 11 g. R 2 onward o Available only during direct-path bulk-loading operations • Restrictions and limitations o Not supported for: • tables with more than 255 columns • index-organized tables (IOTs) • table clusters o ALTER TABLE. . DROP COLUMN not supported • Can only SET UNUSED www. Delphix. com oswoug. org 8

COMPRESS FOR OLTP • Advanced compression option o Additional licensing required in addition to Enterprise Edition o Enabled with COMPRESS FOR ALL OPERATIONS added in 11 g. R 1 • Later renamed to COMPRESS FOR OLTP in 11 g. R 2 o Allows all types of conventional and direct-path DML • Compression triggered when block FULL encountered • Restrictions and limitations o Not supported for: • tables with more than 255 columns • index-organized tables (IOTs) • table clusters o Migrated chained rows will be compressed • But rows chained due to row-length exceeding block size will not o Required List of Critical Patches • Support note #1061366. 1 www. Delphix. com oswoug. org 9

Block Format • Database block layout illustration Header & Tailer (fixed size) Interested Txn List (ITL) Free space Uncompressed rows www. Delphix. com oswoug. org 10

Block Format • Header o Fixed header (110 bytes) • KCBH: Type, hdr, RDBA, SCN Base/Wrap, Seq, Flag, Chksum, (20 bytes) • KTBBH: Transaction Fixed Header (72 bytes) • KDBH: Data Header Structure (14 bytes) • KDBT: Table Directory Entry (4 bytes) o Interested Transaction List or ITL • XID, UBA, flag, lock, SCN Base/Wrap(23 bytes) • INITRANS <= number of entries <= MAXTRANS • Free space o Header grows outward from beginning, row data grows inward from tail • Tail o Check(4 bytes, fixed) • Row entries www. Delphix. com oswoug. org 11

Row Format • Row-header o • Column-piece o • Flag : : Lock : : column-count [ : : cluster-key-ID [ : : chained-ROWID ] ] • Flag, Lock, column-count = 1 byte each • cluster-key-ID • chained-ROWID (6 -8 bytes) Length : : data • Length <= 254 byres then 1 -byte • Else length > 254 bytes, then 3 -bytes • Data o DATE = 7 bytes o NUMBER = 1 byte exponent plus variable-length mantissa o VARCHAR 2, CHAR = text o NULL data values • Non-trailing placeholder = 0 x. FF • Trailing NULLs are not stored Online References o o Oracle 11 g Concepts, http: //docs. oracle. com/cd/E 14072_01/server. 112/e 10713/logical. htm#i 4894 Graham Thornton http: //www. orafaq. com/papers/dissassembling_the_data_block. pdf www. Delphix. com oswoug. org 12

Row Format Flag Lock Col Cnt Len #3 Payload #3 Len #6 Payload #6 www. Delphix. com Len #1 Payload #1 Len #2 Len #4 Len #7 Payload #2 Payload #4 Len #5 Payload #7 oswoug. org 13

Cluster Tables • Tables which share one or more columns o Known as cluster key columns • Rows from clustered tables reside within the same database block o Physically pre-joined relational tables Header & Tailer (fixed size) Interested Txn List (ITL) Free space Cluster keys (table 0) (table 1) (table 2) www. Delphix. com oswoug. org 14

Cluster Tables tab 0, row 0, @0 x 3 f 87 tl: 25 fb: K-H-FL-- lb: 0 x 0 cc: 1 curc: 6 comc: 6 pk: 0 x 0040 db 0 d. 0 nk: 0 x 0040 db 0 d. 0 col 0: [ 5] c 4 04 04 50 24 tab 0, row 1, @0 x 3 f 6 e tl: 25 fb: K-H-FL-- lb: 0 x 0 cc: 1 curc: 18 comc: 18 pk: 0 x 0040 db 0 d. 1 nk: 0 x 0040 db 0 d. 1 col 0: [ 5] c 4 04 04 50 25 Flag byte showing “cluster key” Key value . . . several hundred lines edited out for brevity. . . tab 1, row 0, @0 x 3 a 1 b tl: 65 fb: -CH-FL-- lb: 0 x 0 cc: 20 cki: 0 col 0: [ 4] c 3 05 45 2 c col 1: [ 2] c 1 02 col 2: [ 2] c 1 08. . . several hundred lines edited out for brevity. . . www. Delphix. com oswoug. org Reference back to cluster key 15

DUMP traces • ALTER SYSTEM DUMP command DATAFILE [ file# | ‘file-name’ ] BLOCK [ block# | MIN block# BLOCK MAX block# ] • Examples in SQL*Plus… SHOW PARAMETER USER_DUMP_DEST ALTER SESSION SET TRACEFILE_IDENTIFIER = DUMP_DBF; ALTER SYSTEM DUMP DATAFILE 11 BLOCK 2378; ALTER SYSTEM DUMP DATAFILE 741 BLOCK MIN 62078 BLOCK MAX 62085; • Finding file# and block# for an object… o View DBA_EXTENTS columns FILE_ID, BLOCK_ID, and (BLOCKS-1) select from where order by www. Delphix. com ‘ALTER SYSTEM DUMP DATAFILE ‘||file_id|| ’ BLOCK MIN ‘||block_id||’ BLOCK MAX ‘||(block_id-1)||’; ’ txt dba_extents segment_name = ‘T 1_PK’ and segment_type = ‘INDEX’ file_id, block_id; oswoug. org 16

Row Chaining • Rows are chained for three reasons o Row migration • An UPDATE increases the length of the row so it can no longer fit • Only the row header is left behind, and chain-ROWID points to the location of the row in a different block o Row chaining across blocks • Row takes more space than database blocks can provide • Row is broken into pieces to fit, and chained across blocks o Chain-ROWID points to the location of the next chunk o Row chaining within blocks • Row has more than 255 columns • Row is broken into 255 -column pieces, and chained within blocks o No Chain-ROWID used, row pieces are adjacent within block www. Delphix. com oswoug. org 17

Row Chaining tab 0, row 0, @0 x 3 c 8 a tl: 766 fb: -----L-- lb: 0 x 1 cc: 255 col 0: [ 2] c 1 10 col 1: [ 2] c 1 11 col 2: [ 2] c 1 12 • Dump of example table with 300 numeric columns . . . several hundred lines edited out for brevity. . . col 253: [ 2] c 1 13 col 254: [ 2] c 1 14 tab 0, row 1, @0 x 3 bfb tl: 143 fb: --H-F--- lb: 0 x 1 cc: 45 nrid: 0 x 06 c 1472 e. 0 col 0: [ 1] 80 col 1: [ 2] c 1 02 col 2: [ 2] c 1 03. . . several dozen lines edited out for brevity. . . col 43: [ 2] c 1 0 e col 44: [ 2] c 1 0 f www. Delphix. com oswoug. org 18

Direct-path loads • Bulk loading feature first introduced in Oracle 6 FASTLOAD utility on MVS only Compete with DB 2 on MVS Incorporated into SQL*Loader DIRECT=TRUE in v 7. 0 Extended to parallel CREATE INDEX in v 7. 1 Extended to CREATE TABLE … AS SELECT in v 7. 2 Extended to INSERT /*+ APPEND */ in v 8. 0 Enhanced in v 8. 1 to leave behind a direct-path log for use by MV “fast” refresh o Not much enhancement since… o o o www. Delphix. com oswoug. org 19

Direct-path loads • Direct-path operations are always INSERT o Never UPDATE or DELETE operations • Loads data outside of “managed space” o During a serial direct-path load operation… • loads data above the “high-water mark” in the segment • After successful completion, high-water mark is raised to include newly-loaded rows in the table o During a parallel direct-path load operation… • Loads data into newly-created TEMPORARY segments • After successful completion, TEMPORARY segments are merged into the original target segment www. Delphix. com oswoug. org 20

Direct-path loads • Formats new database blocks with inserted row data within private process memory (PGA) o Then writes the new and complete database blocks directly to the datafiles • Largely bypasses many SGA mechanisms o Buffer Cache o Log Buffer • Except for changes within data dictionary o Object creation and modification is fully recorded in undo and redo www. Delphix. com oswoug. org 21

Conventional-path loads Allocate d Space HWM Managed Space HWM Table just created www. Delphix. com Managed Space Rows inserted conventionally oswoug. org More rows inserted conventionally 22

Serial direct-path loads Allocate d Space Direct Path Load HWM Allocate d Space HWM Managed Space More rows being inserted using serial direct-path www. Delphix. com Managed Space Serial direct-path load after commit oswoug. org Serial direct-path load after rollback 23

Parallel direct-path loads Allocate d Space HWM PX Worker #1 Direct Path Load PX Worker #2 Direct Path Load PX Worker #3 Direct Path Load PX Worker #4 Direct Path Load Rows being loaded by direct-path with parallelism = 4 www. Delphix. com oswoug. org 24

Parallel direct-path loads Allocate d Space HWM Managed Space Rows loaded by direct-path with parallelism = 4 after commit www. Delphix. com oswoug. org 25

Parallel direct-path loads Allocate d Space HWM Rows loaded by direct-path with parallelism = 4 after rollback www. Delphix. com oswoug. org 26

Direct-path loads • Must lock the table/index segment(s) against any other data modifications o Until COMMIT or ROLLBACK • Parallel direct-path loads are very similar to a distributed-database transaction o Two-phase commit operation o Must COMMIT to resolve in-doubt transactions before the session can do anything else www. Delphix. com oswoug. org 27

COMPRESS • Symbol table is implemented as a 2 nd table in the block o Just like a clustered tables • Each entry in symbol table contains repetitive data values o One or more columns per entry • If two or more rows contain the same data values in the one or more contiguous columns, then this will be represented and replaced by an entry in the symbol table www. Delphix. com oswoug. org 28

COMPRESS • Database block layout illustration o Distinct data values stored once in symbol table o Basic compression only occurs on direct-path INSERT • Conventional INSERT, UPDATE leave NOCOMPRESS rows Header & Tailer (fixed size) Interested Txn List (ITL) Free space Symbol table (table 0) Compressed Rows (table 1) www. Delphix. com oswoug. org 29

Actual row len COMPRESS tab 1, row 0, @0 x 15 f 4 tl: 14 fb: --H-FL-- lb: 0 x 0 cc: 13 Row hdr col 0: *NULL* col 1: [ 5] 56 41 4 c 49 44 Noncol 2: [ 1] 4 e col 3: [ 1] 4 e repeated Noncol 4: [ 1] 4 e value repeated col 5: [ 3] 53 59 53 value col 6: [ 7] 50 41 43 4 b 41 47 45 col 7: [ 7] 78 6 b 0 b 02 16 01 1 f col 8: *NULL* col 9: [ 7] 78 70 09 0 f 04 21 39 col 10: [19] 32 30 30 37 2 d 31 31 2 d 30 32 3 a 32 31 3 a 30 30 3 a 33 30 col 11: [12] 44 42 4 d 53 5 f 57 41 52 4 e 49 4 e 47 col 12: [ 3] c 2 29 4 a bindmp: 2 c 00 08 05 02 38 ff 39 37 3 a cb c 2 29 4 a www. Delphix. com Row hdr oswoug. org 30

Lifecycle for BASIC compression • Data lifecycle with basic compression o Normal DML operations as well as direct-path supported Time A Time B Empty Compressed Rows on direct-path deleted APPEND INSERT www. Delphix. com Time C Time D Time E Rows updated Rows inserted oswoug. org 31

Lifecycle for OLTP compression • Data lifecycle with advanced compression o Normal DML operations as well as direct-path supported Time A Time B Empty Newly Compressed inserted uncompressed data www. Delphix. com Time C Time G Time D Time E Time F Compressed with newly uncompressed data inserted or updated Re-compressed Compressed with newly uncompressed data inserted or updated oswoug. org 32

HCC • Built in to the base database 11 g. R 2 and above o But only available on Oracle storage (i. e. Exadata, ZFS, and Pillar) • Columnar storage pivots the idea of row storage o Each entry represents the values of a column across many rows o Rather than each entry representing values in a row across many columns • Hybrid (not true) columnar storage o Each set of column values does contain values not all the rows in the table • Covers a limited set of rows only • Advantages: o Achieve greater compression ratio • Compressing similar types of data, rather than different types of data • Less metadata, more payload o SELECT and UPDATE operations in SQL are column oriented • Disadvantages: o Relational databases manage generally transactions by row • Row locks exist, but column locks do not exist o INSERT and DELETE operations are row oriented www. Delphix. com oswoug. org 33

HCC • Compression unit (CU) is a logical data structure o Header • Offsets and lengths of column entries o Bitmap • Identifies deleted or updated (migrated) rows o Column entries • Data values for N rows of an individual column • Each column entry compressed separately using specified compression algorithm (LZO, LZIP, or BZIP 2) Col #7 Col #6 oswoug. org Col #5 Col #4 Col #3 Col #2 www. Delphix. com Col #1 Bitmap Header CU 34

HCC • Entire CU is stored as a single chained row entry o CU can be broken into chunks at any point, then chained across rows • Online references: J Lewis http: //jonathanlewis. wordpress. com/2012/07/20/compression_units/ Peter Brink http: //www. slideshare. net/Enkitec/hybrid-columnar-compression-in-anonexadata-system Col #5 oswoug. org Col #6 Col #7 Col #4 Col #5 Col #1 Col #2 Col #3 www. Delphix. com Header Bitmap Col #1 CU 35

HCC • When DML is performed on compressed data o INSERT • Inserted as a new row entry using OLTP compression o UPDATE • Marked as deleted in the bitmap entry of CU, then inserted as a new row entry using OLTP compression o DELETE • Marked as deleted in the bitmap entry of CU • Deleted data is not removed, simply marked “deleted” New row entries Marked deleted OLTP compressed row data Col #5 Chained Col #6 Col #7 Col #4 Col #5 Col #1 www. Delphix. com Col #2 Chained Col #3 Header Bitmap Col #1 CU Chained oswoug. org 36

HCC • More block dump output…. . . data_block_dump, data header at 0 x 2 b 8 bbc 16 e 67 c ======== #tables = 1, #rows = 1 tsiz: 0 x 1 f 80 hsiz: 0 x 1 c pbl: 0 x 2 b 8 bbc 16 e 67 c 76543210 flag=-0 -----Free space begin offset ntab=1 and end offset only 2 nrow=1 bytes apart frre=-1 fsbo=0 x 1 c fseo=0 x 1 f avsp=0 x 3 tosp=0 x 3. . . www. Delphix. com oswoug. org 37

HCC mec_kdbh 9 ir 2=0 x 0 76543210 shcf_kdbh 9 ir 2=----- 76543210 flag_9 ir 2=--R----Archive compression: Y fcls_9 ir 2[0]={ } Length of row = 8033 0 x 16: pti[0] nrow=1 offs=0 0 x 1 a: pri[0] offs=0 x 1 f block_row_dump: Flag “PN”: cont’d from tab 0, row 0, @0 x 1 f Previous, cont’ing to Next tl: 8033 fb: ------PN lb: 0 x 0 cc: 1 nrid: 0 x 04001491. 0 col 0: [8021] Length of CU chunk = 8021 Compression level: 00 (Out of range) Length of CU row: 8021 kdzhrh: ----START_CU: 00 00 1 f 55 00 4 c c 7 01 f 3 9 b 62 b 5 3 f 7 d bc 88 88 86 83 e 1 c 6 4 e 91 01 72. . . www. Delphix. com oswoug. org 38

HCC • COMPRESS FOR QUERY [ LOW | HIGH ] o Faster decompression for more frequent query usage o Lower compression ratio • COMPRESS FOR ARCHIVE [ LOW | HIGH ] o Slower decompression for less frequent query usage o Higher compression ratio Level Algorithm Expected compression ratio Load Method BASIC Dedup 2: 3 1: 4 (60% 25%) Direct-path OLTP Dedup 2: 3 1: 4 (60% 25%) Any QUERY LOW LZO 1: 5 1: 10 (20% 10%) Direct-path QUERY HIGH ZLIB 1: 10 1: 18 (10% 6%) Direct-path ARCHIVE LOW ZLIB 1: 12 1: 20 (8% 5%) Direct-path ARCHIVE HIGH BZIP 2 1: 15 1: 30 (6% 3%) Direct-path www. Delphix. com oswoug. org 39

HCC • Hybrid columnar storage has usage implications o Query: • “select col 2” will perform two (2) LIOs • “select *” will perform six (6) LIOs o Upshot: • let developers and ad-hoc query-writers know that columnar storage implies no wildcards for columns Col #6 Col #3 Col #1 CU CU Hdr www. Delphix. com Col #2 Col #1 Col #5 Col #4 Col #6 Col #7 Col #6 oswoug. org 40

DBMS_COMPRESSION • Procedure GET_COMPRESSION_RATIO o Assists in determining if compression is worthwhile o Creates a temporary table with the specified type of compression, populated with a specified number of rows, returns actual compression statistics • Function GET_COMPRESSION_TYPE o Determines how the specified row is compressed (or not) www. Delphix. com oswoug. org 41

GET_COMPRESSION_R ATIO declare v_blkcnt_cmp number; v_blkcnt_uncmp number; v_row_cmp number; v_row_uncmp number; v_cmp_ratio number; v_comptype_str varchar 2(4000); begin DBMS_COMPRESSION. GET_COMPRESSION_RATIO( scratchtbsname => 'TOOLS', ownname => 'PROD_OWNER', tabname => 'ORDER_ACTIVITY', partname => NULL, comptype => DBMS_COMPRESSION. COMP_FOR_OLTP, /* QUERY_LOW|HIGH, ARCHIVE_LOW|HIGH */ blkcnt_cmp => v_blkcnt_cmp, blkcnt_uncmp => v_blkcnt_uncmp, row_cmp => v_row_cmp, row_uncmp => v_row_uncmp, cmp_ratio => v_cmp_ratio, comptype_str => v_comptype_str); dbms_output. put_line('Blocks compressed: ' || v_blkcnt_cmp); dbms_output. put_line('Blocks uncompressed: ' || v_blkcnt_uncmp); dbms_output. put_line('Rows per block compressed: ' || v_row_cmp); dbms_output. put_line('Rows per block uncompressed: ' || v_row_uncmp); dbms_output. put_line('Compression Ratio: ' || v_cmp_ratio); dbms_output. put_line('Comment: ' || v_comptype_str); end; / www. Delphix. com oswoug. org 42

Trailing NULLCOLs • A form of compression that exists in all current versions of Oracle… o Takes advantage of how columns are stored within rows • Row o Row-header : : column-piece [ : : column-piece … ] o Column-piece • Non-NULL data values o Length : : data • NULL data values o Non-trailing placeholder = 0 x. FF o Trailing NULL values are not stored www. Delphix. com oswoug. org 43

Trailing NULLCOLs • A form of compression that exists in all current versions of Oracle… o Takes advantage of how columns are stored within rows • Row o Row-header : : column-piece [ : : column-piece … ] o Column-piece • Non-NULL data values o Length : : data • NULL data values o Non-trailing placeholder = 0 x. FF o Trailing NULL values are not stored www. Delphix. com oswoug. org 44

Trailing NULLCOLs • Case study o Oracle’s Demantra product • Application for demand management, sales and operations planning, projections, and what-if analysis o Central fact table is named SALES_DATA • Frequently customized with additional columns o SALES_DATA had over 750 columns and 250 m rows • All analytic queries performed FULL table scans on SALES_DATA, over and over… o It turned out that the SALES_DATA table had only 40 -50 out of 750 columns populated on average • DBMS_STATS showed average row length of 766 bytes www. Delphix. com oswoug. org 45

Trailing NULLCOLs • Compress? o Database is 10 g. R 2 • Couldn’t use BASIC compression because SALES_DATA is frequently updated by Demantra application o Even OLTP compression would not work well • More than 255 columns as well • Solution o Rebuild SALES_DATA with columns ordered by NUM_NULLS ascending • Then load all rows into the new table • Average row length dropped from 766 to 102 bytes o 7: 1 compression ratio • Total table size dropped from 190 Gb to about 26 Gb www. Delphix. com oswoug. org 46

Trailing NULLCOLs • OK, but we need a way to determine if a table would benefit from such a rebuild o Without having to test it • Procedure CARL (Calculate Average Row Length) 1. Queries rows and calculates current average row length 2. Sorts columns by NUM_NULLS ASC from DBA_TAB_COLUMNS view 3. Recalculate average row length • Download from http: //Ev. DBT. com/scripts/ o Script “carl. sql” o Prerequisites… • CARL relies on good column statistics • Uses DBMS_OUTPUT package to output results o Enable SERVEROUTPUT ON in SQL*Plus www. Delphix. com oswoug. org 47

Prerequisites and storage • TRAILING NULLCOLS compression o No prerequisites for database version, server platform, or storage prerequisites o The only prerequisite is a large number of frequently-NULL columns and an application that does not perform “blind” SELECT and INSERT statements • BASIC compression o Database version 9 i. R 2 or above, no server platform or storage prerequisites o Support DW/BI applications best • OLTP compression o Database version 11 g. R 1 or above with licensing for Advanced Compression option o No server platform or storage prerequisites o Supports OLTP applications best, DW/BI applications probably less well • HCC compression o Database version 11 g. R 2 or above, only on Oracle storage (Exadata, ZFS, Pillar) o Supports all types of applications, but DW/BI applications most effectively www. Delphix. com oswoug. org 48

Points to ponder… • What happens when you attempt to access HCC data on non-Oracle (a. k. a. non-HCC-enabled) storage? o ORA-64307 “hybrid columnar compression is not supported for tablespaces on this storage type” • Luis Moreno Campos’ blog o http: //ocpdba. wordpress. com/2011/05/06/recover-hcc-compressed-tables-to-nonexadata-storage/ o Testing with RMAN, moving HCC data from Exadata to non-Oracle storage o RMAN backup and restore operations are successful. Why? o INSERTs are successful. Why? o SELECTs, UPDATEs, and DELETEs fail. Why? o ALTER TABLE … MOVE is successful. Why? www. Delphix. com oswoug. org 49

Summary • Multiple ways to compress table data o Two ways are provided and supported by Oracle • in certain versions, some need patching o One way is possible by understanding how Oracle stores data • Compression can improve performance o Understand each and every type of compression and how they work • Compression is primarily intended for dormant data o But Oracle has done a good job to handle volatile data well also o Please note how uncompressed data within compressed segments are handled • BASIC/OLTP: uncompressed row data is preceded by flag bytes • HCC: modifications to compressed data handled as OLTP compress www. Delphix. com oswoug. org 50

References • • Jonathan Lewis http: //jonathanlewis. wordpress. com/2012/07/20/compression_units/ http: //jonathanlewis. wordpress. com/2011/10/04/hcc/ Peter Brink - http: //www. slideshare. net/Enkitec/hybrid-columnarcompression-in-a-nonexadata-system Graham Thornton http: //www. orafaq. com/papers/dissassembling_the_data_block. pdf Uwe Hesse - http: //uhesse. com/2011/09/12/dbms_compressionexample/ www. Delphix. com oswoug. org 51

Oregon-South Washington Oracle Users Group Questions? Email: tim. gorman@delphix. com Additional questions? http: //community. Delphix. com Tim’s blog: http: //Ev. DBT. com Mobile: +1 (303) 885 -4526 Thank you for your attention! www. Delphix. com oswoug. org 52