Home » RDBMS Server » Server Administration » How to compress index (11.2.0.1.0 Windos XP)
How to compress index [message #528251] Sun, 23 October 2011 21:20 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
How to compress a index?

SQL> Create Table tb_compress_test
  2  As
  3  Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id);

Index created.

SQL> Alter Index idx_object_id Move Compress;
Alter Index idx_object_id Move Compress
                          *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option


Re: How to compress index [message #528252 is a reply to message #528251] Sun, 23 October 2011 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+How+to+compress+index

Are you unwilling or incapable to use GOOGLE yourself?

Is SEARCH broken for you?

http://www.oracle.com/pls/db112/search?remark=quick_search&word=compress+index

[Updated on: Sun, 23 October 2011 21:31]

Report message to a moderator

Re: How to compress index [message #528254 is a reply to message #528252] Sun, 23 October 2011 22:03 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
I was confused by index compress,before compress,the size of the index idx_object_id is 2097152 bytes,after compress it is 3145728,why? I think the size of index will be reduce after compressed.

SQL> Create Table tb_compress_test
  2  As
  3  Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id,owner);

Index created.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  In
  4  (
  5  'IDX_OBJECT_ID'
  6  );

SEGMENT_NAME    BYTES
IDX_OBJECT_ID   2097152


SQL> Alter Index idx_object_id rebuild Compress 2;

Index altered.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  In
  4  (
  5  'IDX_OBJECT_ID'
  6  );

SEGMENT_NAME     BYTES
IDX_OBJECT_ID   3145728
Re: How to compress index [message #528255 is a reply to message #528254] Sun, 23 October 2011 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
>I think the size of index will be reduce after compressed.
Really? Why?
post URL to documentation that supports statement above.
Re: How to compress index [message #528272 is a reply to message #528255] Mon, 24 October 2011 01:16 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
Just as the flowing statement: the index are compressed,and it can save in space.
Quote:

Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improving performance.

[Updated on: Mon, 24 October 2011 01:19] by Moderator

Report message to a moderator

Re: How to compress index [message #528276 is a reply to message #528272] Mon, 24 October 2011 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OBJECT_ID is unique, you can't have any compression.

I couldn't reproduce your test case, for me the size does not change (but it is 10.2.0.4).

Post "show user".

Regards
Michel
Re: How to compress index [message #528279 is a reply to message #528276] Mon, 24 October 2011 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I get the same thing than you on 11.2.0.1.
The increase seems a bug to me.

Regards
Michel
Re: How to compress index [message #528351 is a reply to message #528279] Mon, 24 October 2011 07:22 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
I have changed the column position of index,the column owner is the first,and it is not unique,but when the index have been compressed, i found the size of index have no any change,why?
SQL> show user;
USER is "HXL"
SQL> Create Table tb_compress_test
  2  As
  3  Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(owner,object_id);

Index created.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  In
  4  (
  5  'IDX_OBJECT_ID'
  6  );

SEGMENT_NAME    BYTES
IDX_OBJECT_ID   2097152


SQL> Alter Index idx_object_id rebuild Compress 1;

Index altered.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  In
  4  (
  5  'IDX_OBJECT_ID'
  6  );

SEGMENT_NAME    BYTES
IDX_OBJECT_ID   2097152



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Re: How to compress index [message #528353 is a reply to message #528351] Mon, 24 October 2011 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COMPRESS does not SHRINK.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  = 'IDX_OBJECT_ID'
  4  /
SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_OBJECT_ID                     2097152

1 row selected.

SQL> alter index IDX_OBJECT_ID shrink space;

Index altered.

SQL> Select a.segment_name,a.BYTES From dba_segments a
  2  Where a.segment_name
  3  = 'IDX_OBJECT_ID'
  4  /
SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_OBJECT_ID                     1703936

1 row selected.


Regards
Michel
Re: How to compress index [message #528444 is a reply to message #528353] Mon, 24 October 2011 21:38 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: control file block size - db_block size?
Next Topic: create view with parameters
Goto Forum:
  


Current Time: Thu Oct 23 06:49:24 CDT 2014

Total time taken to generate the page: 0.09588 seconds