Home » RDBMS Server » Server Administration » compression (11.2.0.2, Windows 2003)
compression [message #558968] Wed, 27 June 2012 14:08 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi friends,

We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out? Would really appreciate any suggestions or recommendations on this.

Thanks a lot
Re: compression [message #558970 is a reply to message #558968] Wed, 27 June 2012 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

http://www.lmgtfy.com/?q=oracle+index+data+compression
Re: compression [message #558975 is a reply to message #558968] Wed, 27 June 2012 14:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I recorded some short tutorials about compression that might help, numbers 7,8,9, and 10 here
http://skillbuilders.com/Oracle/Oracle-Consulting-Training.cfm?tab=exadata-tutorials
Re: compression [message #559219 is a reply to message #558968] Fri, 29 June 2012 20:36 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
In past releases, compression did not allow you to modify columns with "alter table". Here is an example of how to turn compression on.
1) Current sizes of test table and index.

ENWEBP1P > @what_alan_size

   EXTENTS       MEG OBJECT_NAME
---------- --------- ------------------------------------------------
        54      38.5 ENWEBP1P.ALAN_INDEX
        41      26.0 ENWEBP1P.ALAN
           ---------
sum             64.5

ENWEBP1P > list
  1  select
  2  extents,a.bytes/1024/1024 meg,a.owner||'.'||a.segment_name object_name
  3  from dba_segments a
  4* where a.segment_name in ('ALAN','ALAN_INDEX')

ENWEBP1P > select count(*) from alan;

  COUNT(*)
----------
   2097152

2) Squeeze out any empty buffers.

ENWEBP1P > alter table alan move ;

Table altered.

ENWEBP1P > alter index ENWEBP1P.ALAN_INDEX rebuild online;

Index altered.

ENWEBP1P > @what_alan_size

   EXTENTS       MEG OBJECT_NAME
---------- --------- ------------------------------------------------
        54      39.0 ENWEBP1P.ALAN_INDEX
        41      26.0 ENWEBP1P.ALAN
           ---------
sum             65.0

3) Resize table and index down to highwater mark.

ENWEBP1P > alter table ENWEBP1P.ALAN deallocate unused keep 0k;

Table altered.

ENWEBP1P > alter index ENWEBP1P.ALAN_INDEX deallocate unused keep 0k;

Index altered.

ENWEBP1P > @what_alan_size

   EXTENTS       MEG OBJECT_NAME
---------- --------- ------------------------------------------------
        54      38.5 ENWEBP1P.ALAN_INDEX
        41      25.4 ENWEBP1P.ALAN
           ---------
sum             63.9

4) Turn Compression on.

ENWEBP1P > alter table ENWEBP1P.ALAN move compress;

Table altered.

ENWEBP1P > alter index ENWEBP1P.ALAN_INDEX rebuild compress;

Index altered.

ENWEBP1P > @what_alan_size

   EXTENTS       MEG OBJECT_NAME
---------- --------- --------------------------------------------------
        38      23.0 ENWEBP1P.ALAN
        41      26.0 ENWEBP1P.ALAN_INDEX
           ---------
sum             49.0

5) Cut table and index down to higwater mark.

ENWEBP1P > alter table ENWEBP1P.ALAN deallocate unused keep 0k;

Table altered.

ENWEBP1P >  alter index ENWEBP1P.ALAN_INDEX deallocate unused keep 0k;

Index altered.

ENWEBP1P > @what_alan_size

   EXTENTS       MEG OBJECT_NAME
---------- --------- -------------------------------------------------
        41      25.8 ENWEBP1P.ALAN_INDEX
        38      22.9 ENWEBP1P.ALAN
           ---------
sum             48.6


In this case compression caused the table and index to drop from 63.9 megabytes to 48.6 megabytes. This is not always the case, and I have seen compression "increase" the size of both the table and the index. You can always make a copy of a table with "create table test as select * from table_name;" and then turn compression on and off to see the benefits.
Previous Topic: High water mark
Next Topic: Would like to know, how to find the size of individual folder in ASM 10g !
Goto Forum:
  


Current Time: Thu Mar 28 16:55:25 CDT 2024