I did most of my testing on a 11g database, but as far as i know the compression is very similar to that in 10g. The main difference is, that you can use the compression FOR ALL OPERATIONS, thus it is called OLTP compression. With 10g you can only compress for direct-path operations.
I did a test in a BW system too, the main difference of the data is, that we have mainly number fields. So if you have a lot of similar numbers in one block, the compression will be very good. I did a test on a fact table (/BIC/E...) and got a compression factor of ~5.
Did you check the problem with adding colums with an initial value / dropping columns
to a compressed table in 11g ?
No, i did not test that, but according to the documentation, the restriction is no more for ACO compression:
Adding a Column to a Compressed Table
If you enable compression for all operations on a table, you can add columns to that table with or without default values. If you enable compression for direct-path inserts only, you can add columns only if you do not specify default values.
Just to mention, i only did some testing out of curiosity. As either 11g nor ACO compression is not officially supported by SAP, we do not have any plans so far to use them in production.
> Just to mention, i only did some testing out of curiosity. As either 11g nor ACO compression is not officially supported by SAP, we do not have any plans so far to use them in production.
>
> Regards, Michael
Did you use it also under 10g?
1289494 - FAQ: Oracle compression
13. What extent of table compression is permitted in the SAP environment?
Due to the restrictions described above, table compression in the SAP
environment is permitted only for certain table classes in BI. See Notes 701235 and 1231895 for more information.
Up to now we use a "CTAS with compression"- approach to write into partitioned customer tables.
Did you use BRSPACE for uncompress and wich problems do you encounter ?
In 10g :
1289494 - FAQ: Oracle compression states:
* Online reorganization of a table with BRSPACE:
brspace -f tbreorg -t <table_name> -DBD
BRSPACE uses the "-DBD" option to deactivate compression for the target table.
Could their be an issue with "Bug 6512811 - ORA-39726 adding column to table after it is uncompressed"
when the online reorg tries to add a column in "redefinition ROWID" - mode ?
deactivating compression with BRSPACE (online reorganization): Runs fine in 10.2.0.2
activating compression with BRSPACE (online reorganization):
error thrown based on
"Bug 6268371 ORA-12996 / ORA-12998 / corruption from ALTER TABLE DROP UNUSED COLUMNS CHECKPOINT"
The reason is, that the online redefinition process can't use it's primary key mode (SAP tables doesn't add this constraints to the table) and hence must switch to ROWID mode adding a column storing the ROWID into the interim table for synchronizing the DML gone into the table to be redefined.
This column is set to UNUSED by the process later and should be dropped by
alter table Tab_redefined drop unused columns;
IF the table was redefined to NOCOMPRESS the column can be dropped without a problem,
IF the table was redefined to COMPRESS the column can't be dropped and runs into the bug
mentioned.
The metalink note says this should be fixed in 10.2.0.4 and 11g (wich I have to test )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.