COMPRESSING INDEXES

--IN THIS DEMO, WE WILL CALCULATE THE REQUIRED SPACE FOR AN INDEX THAT CONTAINING 3 COLUMNS OF A TABLE
--TOTAL BLOCK SIZE = TOTOL BLOCKS - UNUSED BLOCKS
--I FOUND THIS EXAMPLE FROM "EXPERT ONE-TO-ONE ORACLE" BOOK OF "TOMS KYTE"
--WE CAN SEE THAT TO COMPRESS A INDEX GAINS US MORE FREE SPACE :)SQL>
SQL> set serverout on
SQL> create or replace procedure show_space
2 (
3 p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL
7 )
8 as
9 l_free_blks number;
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_last_used_block number;
17
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,'.') || p_num );
22 end;
23 begin
24
25 dbms_space.free_blocks
26 ( segment_owner => p_owner,
27 segment_name => p_segname,
28 segment_type => p_type,
29 partition_name => p_partition,
30 freelist_group_id => 0,
31 free_blks => l_free_blks );
32
33 dbms_space.unused_space
34 ( segment_owner => p_owner,
35 segment_name => p_segname,
36 segment_type => p_type,
37 partition_name => p_partition,
38 total_blocks => l_total_blocks,
39 total_bytes => l_total_bytes,
40 unused_blocks => l_unused_blocks,
41 unused_bytes => l_unused_bytes,
42 last_used_extent_file_id => l_LastUsedExtFileId,
43 last_used_extent_block_id => l_LastUsedExtBlockId,
44 last_used_block => l_last_used_block );
45
46 p( 'Free Blocks', l_free_blks );
47 p( 'Total Blocks', l_total_blocks );
48 p( 'Total Bytes', l_total_bytes );
49 p( 'Unused Blocks', l_unused_blocks );
50 p( 'Unused Bytes', l_unused_bytes );
51 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
52 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
53 p( 'Last Used Block', l_last_used_block );
54
55 end;
56 /
Procedure created.

SQL>
SQL>create table table123 as select * from all_objects;

Table created.

SQL>
SQL> create index t_idx on table123(owner,object_type,object_name);

Index created.

SQL> exec show_space('T_IDX',user,'INDEX');
Free Blocks.............................0
Total Blocks............................80
Total Bytes.............................655360
Unused Blocks...........................7
Unused Bytes............................57344
Last Used Ext FileId....................1
Last Used Ext BlockId...................43265
Last Used Block.........................1

PL/SQL procedure successfully completed.

--TOTAL BLOCK SIZE = 80 - 7 = 73

SQL>
SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on table123(owner,object_type,object_name) compress 1;

Index created.

SQL> exec show_space('T_IDX',user,'INDEX');
Free Blocks.............................0
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................1
Last Used Ext BlockId...................43249
Last Used Block.........................7

PL/SQL procedure successfully completed.

--TOTAL BLOCK SIZE = 64 - 1 = 63

SQL>
SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on table123(owner,object_type,object_name) compress 2;

Index created.

SQL> exec show_space('T_IDX',user,'INDEX');
Free Blocks.............................0
Total Blocks............................56
Total Bytes.............................458752
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................1
Last Used Ext BlockId...................42897
Last Used Block.........................4

PL/SQL procedure successfully completed.

--TOTAL BLOCK SIZE = 56 - 4 = 52

SQL> spool off

--FINALLY WE CAN OBVIOUSLY SEE THAT WE CAN HOLD THE SAME INDEX DATA IN LESS MEMORY SPACE BY COMPRESSING IT..

Advertisement