Oracle Advanced Compression and LONG RAW

[Former Member]

I am trying to test table compression. I have an 11.2.0.1 database. The table I am trying to compress is DBTABLOG which is 15gb. The table has a LONG RAW (LRAW) field LOGDATA.

Currently DBTABLOG is in tablespace PSAPPROTD. I created an ASSM tablespace PSAPDBTABLOG. Note 1426979 states" it is recommended to convert existing long data directly into securefile lobs".

I used the following:

brspace -u / -f tbreorg -a lob2lob -o sapr3 -s psapprotd -t DBTABLOG -n PSAPDBTABLOG -c ctablob -lc medium -SCT

I am receiving:

R0280I BRSPACE time stamp: 2010-11-17 12.10.10

BR1108I Checking tables for reorganization...

BR1110W Table SAPR3.DBTABLOG has a LONG (RAW) column

BR1111I Reorganization of table SAPR3.DBTABLOG will be skipped

What step am I missing? Thank you in advance for an help.

[former_member204746 · Accepted solution]

try this instead:

brspace -u / -f tbreorg -a long2lob -o sapr3 -s psapprotd -t DBTABLOG -n PSAPDBTABLOG -c ctablob -lc medium -SCT

[Former Member · Accepted solution]

Beauty. Thank you. It worked. I have another question if you dont mind along the same lines.

1. I noticed the table is compressed when looking via DB02; I assume that would include an new data? (e.g. inserts).

2. I also noticed that the indexes got moved to the same tablespace - which is fine and what I wanted - but I was surprised they too were not compressed. Is that a bug?

[Former Member · Accepted solution]

Oops. I awarded points to Eric... I hope it did not close question. Still wanted to know:

Beauty. Thank you. It worked. I have another question if you dont mind along the same lines.

1. I noticed the table is compressed when looking via DB02; I assume that would include an new data? (e.g. inserts).

2. I also noticed that the indexes got moved to the same tablespace - which is fine and what I wanted - but I was surprised they too were not compressed. Is that a bug?

[former_member524429 · Accepted solution]

Hi,

1. I noticed the table is compressed when looking via DB02; I assume that would include an new data? (e.g. inserts).

Yes. It will include all new data which are inserted during Online reorganization of selected table. During reorganization process Materialized views are created in parallel to actual source table (for which the reorganization is in process..). The changes to the table data are recorded in that materialized views and the same are applied at the last steps of table reorganization process completion. Such materialized viewed are then cleaned up after successful completion of Table Reorganization. Refer How does an online reorganization with BRSPACE work? in [SAP Note 541538 - FAQ: Reorganization|https://service.sap.com/sap/support/notes/541538].

2. I also noticed that the indexes got moved to the same tablespace - which is fine and what I wanted - but I was surprised they too were not compressed. Is that a bug?

In Table reorganization command with -c ctablobind option you can achieve Table, LOB & Index Compression.

You can perform explicit Index compression by rebuilding the Index(s) by using option -c cind or -c cind_only with -ic ora_proc. Refer [SAP Note 1464156 - Support for index compression in BRSPACE 7.20|https://service.sap.com/sap/support/notes/1464156] to get more detailed information.

I hope the above information will be helpful to you.

Regards,

Bhavik G. Shroff

[Former Member · Accepted solution]

I would have not expected the below:

Uncompressed:

SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK" from sapr3.dbtablog group by dbms_rowid.rowid_block_number(rowid);

AVERAGE RECORDS PER BLOCK

-

---

127.592698

Compressed:

SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK" from sapr3.dbtablog group by dbms_rowid.rowid_block_number(rowid);

AVERAGE RECORDS PER BLOCK

-

---

112.093692

[Former Member · Accepted solution]

Well, i would have to look it up, but while compressing lobs there is two things to consider:

- some lobs already hold compressed data (SAP does compress the data before storing it), if you compress the data on oracle side, there won't be any additional benefit

- we need to check if the lob has "STORAGE IN ROW" specified or not, in the first case the first 4k of data will be stored in the datablock, otherwise everthing will be stored in the lob segment. I am pretty positive, that DBTABLOG indeed has storage in row, because the data is way below 4k.

Cheers Michael

EDIT: i did a quick check, the lob field is a blob -> i bet it is already compressed and storage is in row. And the lob segment is probably empty

SQL> select bytes from dba_segments
where segment_name = ( select segment_name from dba_lobs where table_name = 'DBTABLOG');

     BYTES
----------
     65536

BTW: i really wonder if this "full inline" stored data is compressed, when doing lob compression....

EDIT2: i did a bunch of tests, it looks like the inline part of a lob cannot be compressed -> would be nice if anybody could confirm or deny this

I am coming to the conclusion that such small data shouldn't be put in a lob after all

SQL> select avg(dbms_lob.getlength(logdata) from dbtablog;

AVG(DBMS_LOB.GETLENGTH(LOGDATA))
--------------------------------
                      86.9968825

[former_member524429]

Hi,

Refer Topic 3 of [SAP Note 646681 - Reorganizing tables with BRSPACE|https://service.sap.com/sap/support/notes/646681] along with SAP Notes [835552|https://service.sap.com/sap/support/notes/835552] && [500340|https://service.sap.com/sap/support/notes/500340] to get more information.

Regards,

Bhavik G. Shroff