Index tablespace increasing...does Alter indexes rebuild help to reduce it??

[ngwh]

ok guys....so what's the conclusion??

Should I sit and do nothing about indexes that are consuming up my index tablespaces OR is there something I can do like COALESCE or COMPRESSION ...etc??

kindly enlighten a poor chap here

[aspdba]

hello gurus,

Isnt dropping and recreating the objects the best possible solution for fragmentation(if possible)?

Thank you

[nabaig]

All that t.Kyte's posting tells me is if you use initial and next extent then the next extent sizes increase by the pctincrease factor.
I notices Tom's message is dated July 2000 for Oracle 7.3? Does it apply today? No. Why?

With 9i when we have LMT's and segment space management AUTO options, initital, next, pctincrease is history. It sure does makes sense to upgrade right?

Well when you rebuild, you do need to keep next in mind.

And finally, why I think a rebuild is necessary is that given I/O is in blocks and not in rows, then a index block with a 90% free space and one index row sure eats up my cache.

And finally from the Oracle manual:

When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE
INDEX statement, re-creating an existing index offers better performance.


What I mean is T. Kyte is not the end in himself. Different times, different technologies, think about different solutions.

Nizar

[pando]

ngwh, as I said what´s the point if you dont delete data, you will have to rebuild an index if you delete data otherwise it becomes screwed

nabaig dont tell me you rebuild index every month or every week?!?! without deleting data?

[nabaig]

Pando,

My point is moot only if data is deleted. My environment is dynamic; lotsa inserts, deletes etc.

And I have noticed that if I dont rebuild my index regularly; then full table scans take over.

If there are no deletes then why rebuild? Agreed.

Nizar

[adewri]

Originally posted by nabaig
Pando,

My point is moot only if data is deleted. My environment is dynamic; lotsa inserts, deletes etc.

And I have noticed that if I dont rebuild my index regularly; then full table scans take over.

If there are no deletes then why rebuild? Agreed.

Nizar

No i disagree on this also. Deletes does not always need rebuild, only few exceptional cases.

Consider the following example.

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 26 19:13:03 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> create user test identified by test default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,create procedure to test;

Grant succeeded.

SQL> alter user test quota unlimited on users;

User altered.

SQL> grant select on dba_tablespaces to test;

Grant succeeded.

SQL> grant select on dba_segments to test;

Grant succeeded.

SQL> conn test/test

create or replace
procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
authid current_user
as
    l_free_blks                 number;

    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              p_num );
    end;
begin
    for x in ( select tablespace_name
                 from dba_tablespaces
                where tablespace_name = ( select tablespace_name
                                            from dba_segments
                                           where segment_type = p_type
                                             and segment_name = p_segname
                                             and owner = user
                                  and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
             )
    loop
    dbms_space.free_blocks
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
      partition_name    => p_partition,
      freelist_group_id => 0,
      free_blks         => l_free_blks );
    end loop;

    dbms_space.unused_space
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
          partition_name    => p_partition,
      total_blocks      => l_total_blocks,
      total_bytes       => l_total_bytes,
      unused_blocks     => l_unused_blocks,
      unused_bytes      => l_unused_bytes,
      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
      LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Free Blocks', l_free_blks );
    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

Procedure created.

SQL> create table t (x int,constraint t_pk primary key(x));

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (9999999999);

1 row created.

SQL> commit;

Commit complete.

SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................5
Last Used Ext BlockId...................96
Last Used Block.........................4

PL/SQL procedure successfully completed.

So according to you if the index entries are never reused upon deletes and if i keep inserting and deleting and never reuse a value, this index should grow like crazy.
Let's see.

SQL> begin for i in 2 .. 999999
  2  loop
  3  delete from t where x = i;
  4  commit;
  5  insert into t values (i+1);
  6  commit;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................5
Last Used Ext BlockId...................96
Last Used Block.........................4

PL/SQL procedure successfully completed.

SQL>

So, this shows that the space in the index was reused. So no need to rebuild even if there is deletions.

Nabaig just try the above method on your indexes and see what it shows

HTH

PS: The above procedure (works only for 9i LMT tablespaces) and example is given in "expert one on one oracle" by thomas kyte, i have done these tests and shown here to prove the point that deletes also does not always require rebuild.

[tamilselvan]

"ALTER INDEX indexname COALESCE" statement can be used in a 24x7 env where REBUILD is not possible. What it does is: Merges empty and nearly empy (based on pctfree value) blocks into a single block, frees up the blocks (totally empty) for future inserts.
Here is my test:

create table Tamil tablespace psdeveloper
storage (initial 10m next 10m pctincrease 0)
as select rownum runseq , a.* from dba_objects a;

14:54:56 H8DEVW3>select count(*) from tamil ;

COUNT(*)
----------
39367

create index tamil_idx on tamil(runseq)
tablespace psindex storage (initial 256k next 256k pctincrease 0);

Index created.


insert into tamil select (40000+rownum) runseq , a.* from dba_objects a;

39368 rows created.

select count(*) from tamil ;

COUNT(*)
----------
78735


select segment_name, trunc(sum(bytes/1024/1024)) from dba_extents
where segment_name = 'TAMIL'
group by segment_name;

SEGMENT_NAME TRUNC(SUM(BYTES/1024/1024))
-------------------------------- ---------------------------
TAMIL 10


select segment_name, trunc(sum(bytes/1024/1024)) from dba_extents
where segment_name = 'TAMIL_IDX'
group by segment_name;

SEGMENT_NAME TRUNC(SUM(BYTES/1024/1024))
-------------------------------- ---------------------------
TAMIL_IDX 2

analyze index tamil_idx validate structure ;

select BLOCKS , NAME, lf_rows, lf_blks, br_blks, used_space from index_stats;

BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS USED_SPACE
---------- ----------------- ---------- ---------- ---------- ----------
260 TAMIL_IDX 78735 165 1 1250817

select sum(blocks) from dba_extents where segment_name = 'TAMIL_IDX';

SUM(BLOCKS)
-----------
260

delete from tamil where runseq between 5001 and 15000 ;

10000 rows deleted.

delete from tamil where runseq between 35001 and 45000 ;

9367 rows deleted.

delete from tamil where runseq between 65001 and 75000 ;

10000 rows deleted.

commit;

select count(*) from tamil;

COUNT(*)
----------
49368


select blocks,name, lf_rows, lf_blks, br_blks, del_lf_rows, used_space, btree_space
from index_stats;

BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ---------- ---------- ---------- ----------- ---------- -----------
260 TAMIL_IDX 78735 165 1 29367 1250817 1327368


alter index tamil_idx coalesce ;


analyze index tamil_idx validate structure ;

select blocks,name, lf_rows, lf_blks, br_blks, del_lf_rows, used_space, btree_space
from index_stats;

BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ---------- ---------- ---------- ----------- ---------- -----------
260 TAMIL_IDX 49368 106 1 0 785542 855604

Note: After the coalesce on Index the number of Leaf Blocks have come down to 106 from 165.

[Orca777]

Hi, what tom said could be right for many cases but if you have a technical growing id in your index
and your applications deletes older entries from time to time
you NEED to rebuild from time to time if you want not to waste to much disk-storage.

And why is it not so bad to have big holes in the index?, how effective is it to read air (empty blocks) instead of hard index-data?

Orca

[adewri]

Originally posted by Orca777
if you have a technical growing id in your index
and your applications deletes older entries from time to time

Well, that's what i exactly showed above, deleted space will be reused.

But yes if we had deleted every other row (even rows) from 1 to 999999, then yes it would leave 499999 holes which would be reused only if we reinsert data that will fit into those blocks. Here in this case rebuild and coalesce is unavoidable.

But if we delete all the rows below 499999 then the blocks that were cleaned out of the index will be put back onto the FREELIST for the index. This space can be totally reused.