Hybrid columnar compression is performed by Exadata only - so it does not apply to "regular Oracle" - just the Exadata Database Machine.

Basically - we store data in what is called a compression unit - a set of blocks. So, unlike basic and advanced compression - which both work block by block and each block is 'self contained', you need only the one block to decompress your data - hybrid columnar compression works on a set of blocks - you need more than one block to decompress the data (we use 4x8k blocks typically).

The hybrid columnar compression (HCC) works with direct path loads only - similar to basic compression. The big difference is how hybrid columnar compression stores the data. Basic and advanced compression store data row by row - so row 1 comes on the block before row 2 and so on. HCC takes the data and before putting it on the block, factors out the data into arrays of columns. So, we'll take a stream of data and store all of the values for column 1 in array 1 and column 2 in array 2 and so on.

Before writing these arrays of column values out - one after the other - we go through a de-duplication process (the odds of repeating data are greatly increased since we are looking at at least 4 times as much data - spread over 4 blocks not just one). We can also do leading edge compression of the data in these arrays. We can also do more compression after the leading edge (we have two levels of compression available - you choose how much time you want to spend compressing data). Once we've compressed the columns as much as possible - we write them out to the compression unit. We write them out column by column now - not row by row.

In order to decompress - you need the compression unit - not just a single block.

index partitions are compressed using index key compression - predates the basic segment compression - it came out in 8.1.5, segment compression (which comes 'basic' or 'advanced') is 9ir2 and 11gr1.


a) none, basic compression is a 100% subset of advanced. If you want to ENABLE advanced (OLTP) compression on a table, you would simply alter it - all SUBSEQUENT DML will be compressed in addition to direct path operations.

If you have tables that are currently full of uncompressed data - you would need to reorganize them to make them have only compressed data.

you would not alter it so much as "reorganize it", the partition would be rewritten using an alter move command. It would not affect the compression rate since the data would in effect be reloaded.

you have to use DIRECT PATH operations such as:

sqlldr direct=y
insert /*+append*/
create table as select
alter table move

any direct path operation would work fine.

as long as it is using direct path - it can use hybrid columnar compression - HCC (it is best to always spell out an acronym the first time, even if you assume the person reading the text 'knows' what it means...)


http://docs.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_overview.htm#SUTIL804

the modified rows will not be columnar compressed after the update.

to get them back, you need to direct path the data again, using alter table t move for example - to rebuild it.

You can certainly look at using index key compression for your non-unique single column indexes OR any multi-column (concatenated) indexes.

You would probably want to use

alter index index_name validate structure;

in a test environment (with a full copy of your data) to identify indexes that would be compressable. For example:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx1 on t(object_id);

Index created.

ops$tkyte%ORA11GR2> create index t_idx2 on t(object_type,owner,object_name);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> analyze index t_idx1 validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select name,  blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

NAME                               BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- -------------- ----------------
T_IDX1                                256              0                0

<b>that shows that t_idx1 is NOT compressable - it wouldn't be useful to use compress on it, however</b>

ops$tkyte%ORA11GR2> analyze index t_idx2 validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select name,  blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

NAME                               BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- -------------- ----------------
T_IDX2                                640              2               28

<b>that index would benefit from compress 2 - it would be at least 28% smaller</b>

ops$tkyte%ORA11GR2> alter index t_idx2 rebuild compress 2;

Index altered.

ops$tkyte%ORA11GR2> analyze index t_idx2 validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select name,  blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

NAME                               BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- -------------- ----------------
T_IDX2                                384              2                0

<b>and it is...</b>

bear in mind that validate structure LOCKS the index - do NOT do this in your live OLTP system!

trick question - both.


the database server can process cached HCC blocks. So if we processed the data and compressed it 50:1, you'd be able to cache 50 times as much of that data in the cache since the compressed block would be cached in the SGA.

the storage array can process those blocks as well - it can cache them in the flash cache - it can read and process them with query offloading.


If we smart scan it - then the data is read compressed by the storage server, it is decompressed on the storage server, the rows and columns of interest are discovered and sent to the database layer.

If we read it "conventionally" (no smart scan) then the storage array just transmits the entire compressed block to the database layer.

sorry, you are correct (i have to verify on 11.2.0.4 however). It is in 12.1 though for sure:

ops$tkyte%ORA12CR1> declare
  2          l_string clob := 'create table t ( x int';
  3  begin
  4          for i in 1 .. 999
  5          loop
  6                  l_string := l_string || ', c' || to_char(i) || ' varchar2(20) default rpad( ''x'', 20, ''x'' )';
  7          end loop;
  8          l_string := l_string || ' )';
  9          execute immediate l_string;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into t (x) select rownum from all_users;

37 rows created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> exec show_space( 'T' );
Unformatted Blocks .....................              62
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              34
Full Blocks        .....................             147<b>
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2</b>
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,456
Last Used Block.........................             128

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> alter table t compress for oltp;

Table altered.

ops$tkyte%ORA12CR1> alter table t move;

Table altered.

ops$tkyte%ORA12CR1> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             108
Total Blocks............................             120<b>
Total Bytes.............................         983,040
Total MBytes............................               0</b>
Unused Blocks...........................               2
Unused Bytes............................          16,384
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,808
Last Used Block.........................               6

PL/SQL procedure successfully completed.

well, compression is going to be harder with char(2000).

right now, you are getting a max if 3 rows per block. that is the most that can fit (assume 8k block).

Now you employ compression.

IF you insert two rows into a block with the same char(2000) value, you'll be able to get two more rows at least on that block (so four). If the third row on that block is the same - you'll be able to get probably five rows.

but - you'd have to be inserting the same value over and over for this to work. and then move onto the next value.

that is - if three people inserted one after the other and they inserted three different values - the block they fill would be full - we'd move onto the next block. There would be NO compression effectively.


however, if all three inserted the same value - that would compress nicely. But this isn't real world. there might be duplication in that field - but it is unlikely it would realistically compress.



Lets get to the FUD - let me help you there. I have a rule "never ever ever ever - as in never - use CHAR, never, not even for CHAR(1)". I'll defeat any FUD they can conjure up.