table compression works when you do direct path operations (insert append, direct path load, alter table move... ) not during normal insert/update/delete.

It works by removing repetitive data and storing it once per block instead of once per occurrence per block.

we have had that since version 9ir2.

It is called a compressed table. Any column, in fact any set of columns, that have repeated values are compressed.

If you have a table with:

first_name
middle_name
last_name
street
city

and you have Allen as a first_name, middle_name, last_name, street, city - either on the same row or across rows - we'll store Allen once.

it depends on the release.

And the data didn't have to be decompressed if you use CREATE TABLE AS SELECT as demonstrated above.

you can:

create table new_table COMPRESSED
as
select t.*, <new column here> from old_table t;

drop old_table;
rename new_table to old_table;


that'll be about it in 9i, or upgrade to 10g - you are already 6 patchsets off of supported software as it is...

ops$tkyte%ORA9IR2> create table t1 as select * from all_users;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table t2
  2  compress
  3  as
  4  select cast(username as varchar2(40)) username,
  5         cast(user_id  as number(10,2)) user_id,
  6             created
  7    from t1
  8  /

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> desc t1
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 USERNAME                                 NOT NULL VARCHAR2(30)
 USER_ID                                  NOT NULL NUMBER
 CREATED                                  NOT NULL DATE

ops$tkyte%ORA9IR2> desc t2
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 USERNAME                                          VARCHAR2(40)
 USER_ID                                           NUMBER(10,2)
 CREATED                                  NOT NULL DATE

same answer - use cast????

ops$tkyte%ORA9IR2> create table t2
  2  compress
  3  as
  4  select cast(username as varchar2(40)) username,
  5         cast(user_id  as number(10,2)) user_id,
  6             created,
  7             cast( null as char(3) ) new_column ,
  8             cast( null as varchar2(3) ) new_column2
  9    from t1
 10  /

Table created.

Elapsed: 00:00:00.05
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> desc t1
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 USERNAME                                 NOT NULL VARCHAR2(30)
 USER_ID                                  NOT NULL NUMBER
 CREATED                                  NOT NULL DATE

ops$tkyte%ORA9IR2> desc t2
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 USERNAME                                          VARCHAR2(40)
 USER_ID                                           NUMBER(10,2)
 CREATED                                  NOT NULL DATE
 NEW_COLUMN                                        CHAR(3)
 NEW_COLUMN2                                       VARCHAR2(3)

and of course, you can always:

a) create table using just "create table ( .... ) compress"

b) then - insert /*+ append */ into


to load it.

you would add any indexes to you wanted - you would not "lose" any - indexes stay with a table, if you want T2 to have some indexes, just create them

it doesn't really - you just need to use cast at the top level

ops$tkyte%ORA9IR2> create table testNOTOK as
  2  select cast(c as number(38)) c
  3    from ( select cast (1 as number(38)) c from dual
  4            union all
  5           select cast (2 as number(38)) c from dual
  6         )
  7  /

Table created.

ops$tkyte%ORA9IR2> desc testNOTOK;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 C                                                 NUMBER(38)

you would have to explicitly put that into the create table as select - the partition details.

ops$tkyte%ORA10GR2> create table t1
  2  PARTITION BY RANGE (dt)
  3  (
  4    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
  5    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
  6    PARTITION junk VALUES LESS THAN (MAXVALUE)
  7  )
  8  COMPRESS
  9  as
 10  select * from t;

Table created.

big_table%ORA10GR2> select count(*) from big_table;

  COUNT(*)
----------
  25000000

big_table%ORA10GR2> set timing on
big_table%ORA10GR2> alter table big_table add x number;

Table altered.

Elapsed: 00:00:00.06
big_table%ORA10GR2>

it should be instantaneous - if not, you must be using a "default" value in which case you can:

a) add column without default
b) alter table t modify newcol default <value>; -- for new inserts
c) update it where it is null (this will still permit concurrent inserts into the table, but of course - the update will prevent updates and deletes to the existing rows)
d) commit;

looks like they withdrew that for 10g, but it is in 11g

ops$tkyte%ORA11GR1> CREATE TABLE t COMPRESS AS SELECT * FROM all_users;

Table created.

ops$tkyte%ORA11GR1> ALTER TABLE t ADD (v VARCHAR2 (30));

Table altered.

ops$tkyte%ORA11GR1> ALTER TABLE t SET UNUSED (v);

Table altered.

ops$tkyte%ORA11GR1> ALTER TABLE t DROP unused columns;

Table altered.

ops$tkyte%ORA11GR1> SELECT * FROM v$version WHERE ROWNUM = 1;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

it is sort of "not necessary" of course in this case - in fact, I'd say dropping a column from a compressed table probably should be done via 'alter table t move' anyway (in order to, well, compress the data on the block again...)

not until 11g with advanced compression option - this is true, but, if you have a compressed table (save space), dropping a column without a reorg (waste space) seems "not sensible"

just make it unused and if you want to reclaim the space - reorganize those segments, you should probably have just been using set unused from the get go in this case.

correct, 11g Release 1 introduced the advanced compression option which permits compression for inserts and updates using conventional path.


so, as with many things, it means "it is not true anymore"

well, there would be a couple of cases to be considered - lots of them actually

there is direct path compression and conventional path compression.

direct path:
o insert (not direct path insert) will not be affected
o delete will not be
o update might be. If you update compressed data, it'll likely decompress and migrate the row. If you update non-compressed data, no affect

conventional path:

o most inserts will not be impacted, only the insert that takes the block over "pctfree" and would normally take it off the freelist would. Instead of just taking it off freelist, it first compresses the block and if that freed enough space - it just inserts into the block. If that did not free enough space - the block is taken off the freelist. So, everynow and then and insert will "feel the pain"

o deletes should not be affected

o updates - see insert in this section. If the update would cause the block to exceed pctfree and be removed from the freelist, the compression is done first.




In general, compression negatively impacts loading - in the hopes of reducing storage needs and improving the speed of retrieval.

no, there is not.

well, not truly, you could do something like using dbms_rowid.rowid_block_number to get an ESTIMATE of the number of rows/block and then bin them up in some ranges to get an idea of how many blocks have 0..100 rows, 101..200 and so on.

The ones with "high rows/block" would likely be compressed data blocks

Not 100% accurate by any means, but a good guess.


In 11gr2 - there is a tool to analyze a table and estimate how well it would compress, that is new.

actually, I think you are speaking of columnar compression ala sybase IQ, vertica - but not teradata.

oh, and of exadata 2 from Oracle as well.

http://www.google.com/search?hl=en&safe=off&q=hybrid+columnar+compression+site%3Aoracle.com&aq=f&oq=&aqi=

... Customers using Teradata compression in V2R4 have experienced up to 50% capacity savings and scan performance increases up to 35%....

we meet or exceed that with block level compression. We have columnar compression (exadata version 2.0) and block level compression (since 9iR2) - and it meets or exceeds that savings...


... In Teradata V2R4, one value in each column can be compressed out of the row body....

that doesn't seem to generally useful, unless you have a column with one value that is very very very wide and repeats a heck of a lot.

but their statements about block level compression - are not really true. If they were - I'd expect that they'd have numbers somewhere demonstrating them.

It sounded like you were talking columnar compression to me, especially what the reader wrote - the original poster.

And if you slow down and read what I wrote it is:

we meet or exceed that with block level compression. We have columnar compression (exadata version 2.0) and block level compression (since 9iR2) - and it meets or exceeds that savings...


where did I say "first you said that oracle had column level compression since 9R2,"

And your ordering of the timeline is quite strange.


You say "first you said" - however, I wrote that AFTER you said column compression.

Then you said I said "I don't know what your talking about" - the only way you get that order is if you read the page from the bottom up.


You wrote "column compression - like teradata" (and remember, I know nothing about you, your background, your level of experience with Oracle, teradata, anything - you know about me - I know *nothing* about you. without references (links, context, something) I have to guess many times at what people are referring to and the levels of people that come here range from - well, the entire spectrum is represented)

I wrote "I think it was columnar compression like Sybase IQ, Vertica, and Exadata" (given that this is what the industry has been working on for a while, this is where the real compression comes from )

You wrote "No, this <link to tersdata, which I read>"

And I wrote "Ok, we meet or best those numbers with block compression - it doesn't see to be worth it, we would achieve at least that level with our block compression" and "exadata goes a step further" - so I don't see your point, it doesn't seem to me to have any advantages over what we already had, what we've had for many releases.


Yes, this is Tom



And so what about teradata, I've already said we meet or beat that in ever case? Can you tell us mathematically how this is not true?




True, I misunderstood what you meant when you said column compression - but only because the columnar compression is what really achieves massive compression and the column compression that teradata has there, frankly, doesn't seem to be very impressive.

Maybe I'm missing something, feel free to point it out.

that is correct, this ability is part of the OLTP table compression - sorry, they fixed it in 11gR1, it would be:

ops$tkyte%ORA11GR1> CREATE TABLE t COMPRESS for all operations AS SELECT * FROM all_users;

Table created.

ops$tkyte%ORA11GR1> ALTER TABLE t ADD (v VARCHAR2 (30));

Table altered.

ops$tkyte%ORA11GR1> ALTER TABLE t SET UNUSED (v);

Table altered.

ops$tkyte%ORA11GR1> ALTER TABLE t DROP unused columns;

Table altered.

ops$tkyte%ORA11GR1> SELECT * FROM v$version WHERE ROWNUM = 1;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

give example.

I would need the step by steps to reproduce that exact error.

I saw this:

... I am getting the same error in the partitioned table too. ...

and looked at the example and said to myself "hmm, no partitioning - partitioning question - missing information". In other words, the red herring you threw in there confused me.


It is the compress.

ops$tkyte%ORA10GR2> create table t compress as select * from all_objects where rownum=1;

Table created.

ops$tkyte%ORA10GR2> alter table t set unused column status;

Table altered.

ops$tkyte%ORA10GR2> alter table t drop unused columns;
alter table t drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

the error is wrong in your older release, but the cause is the same:

$ oerr ora 39726
39726, 00000, "unsupported add/drop column operation on compressed tables"
// *Cause:  An unsupported add/drop column operation for compressed table
//          was attemped.
// *Action: When adding a column, do not specify a default value.
//          DROP column is only supported in the form of SET UNUSED column
//          (meta-data drop column).

Also, if your goal is to COMPRESS, dropping a column doesn't make sense - setting unused is as good as you get.

Since you do not insert and reuse space in a compressed table like that - you don't need to drop the column.

If you want to reclaim the space, you'd have to REORGANIZE the object, just alter table move it.

it is pretty consistent for me.

ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

ops$tkyte%ORA10GR2> create table my_ob compress as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2> insert into my_ob select * from all_objects;

50080 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> alter table my_ob set unused column status;

Table altered.

ops$tkyte%ORA10GR2> alter table my_ob drop unused columns;
alter table my_ob drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

every single time. If you can reproduce your issue, please utilize support.


But again, it doesn't really matter, if you went out of your way to compress a table your goal must be to minimize space - hence you would be doing a full reorganization - not a drop column - in order to repack the compressed data on blocks.

you would want to create table new as select ...., 'x' x from old;

alter x to be default 'x'
drop old
rename new


think about it, for basic compression which ONLY works during direct path operations - if we added the column to the compressed table - every row would have to be uncompressed, migrated to a new block, have 'x' added to it, and then stored uncompressed. You'd end up with an uncompressed table with every row migrated and lots of apparently empty blocks where the compressed data used to be.


you just want to do a direct path operation here - create table new as select from old.

I'm not sure, but I would not drop unused columns like that anyway (assuming your table is large).

I would do:

create table new_copy compress as select ...

A 'drop unused' is a very expensive operation.

https://connormcdonald.wordpress.com/2013/01/20/exchange-partition-those-pesky-columns/

https://connormcdonald.wordpress.com/2016/02/16/exchange-partition-revisited/

This is all much better in 12.2 where you just do:

"create table T for exchange"

I suspect that is a limitation that will be lifted

19c
====
SQL> create table t
  2      COMPRESS
  3      as
  4      select * from all_objects
  5      where rownum <=10;

Table created.

SQL>
SQL> alter table t add X number default 55 not null;

Table altered.

SQL>
SQL> alter table t add Y timestamp default systimestamp not null;
alter table t add Y timestamp default systimestamp not null
                  *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

21c
=======
SQL> create table t
  2      COMPRESS
  3      as
  4      select * from all_objects
  5      where rownum <=10;

Table created.

SQL>
SQL> alter table t add X number default 55 not null;

Table altered.

SQL>
SQL> alter table t add Y timestamp default systimestamp not null;

Table altered.