| How to compress index [message #528251] |
Sun, 23 October 2011 21:20  |
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 #528254 is a reply to message #528252] |
Sun, 23 October 2011 22:03  |
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 #528272 is a reply to message #528255] |
Mon, 24 October 2011 01:16 |
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 #528351 is a reply to message #528279] |
Mon, 24 October 2011 07:22 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
