| compression [message #558968] |
Wed, 27 June 2012 14:08  |
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 #559219 is a reply to message #558968] |
Fri, 29 June 2012 20:36  |
 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
