DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: index rebuild

  1. #1
    Join Date
    Mar 2003
    Posts
    34

    index rebuild

    I have an index named idx_1 which i want to rebuilding.
    First I used the query "Select * from dba_indexes where index_name='idx_1'; ". It returned blank row. Then I gave these 2 stms "Analyze index idx_1 compute statistics; Analyze index idx_1 validate structure; ". After that I again checked the status in dba_indexes where the row was now populated. After that I gave the cmd to rebuild "Alter index idx_1 rebuild". Now when I checked the status in dba_indexes the row returned the same values before rebuilding. Why so. Who will I know that the index is rebuild.

    What is the purpose of Analyze compute statistics & Analyze validate structure here.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    go RTFM

    tahiti.oracle.com

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi,

    The purpose of "Analyze index" and "validate structure" is collecting statistics on indexes like actual usage of blocks within an index.
    After "Analyze index" you can query dba_indexes to collect the statistics and after "validate structure" you can query index_stats.
    To check if index is rebuilt ,you don't have to issue those commands.
    You can examine the rebuilding of the index by quering from dba_segments after changing its storage or changing its tablespace.
    e.g:
    set linesize 1000
    col seg_nam format a15
    col part_name format a10
    col type format a15
    col tbs format a12
    col EXTS format 99999
    col blks format 999999
    col next format 9999 heading 'next|(in Mb)'
    col ext format 9999
    col int format 9999 heading 'int|(in Mb)'
    col MIN format 99
    col MAX format 9999
    select SEGMENT_NAME "seg_nam",partition_name "part_name",
    SEGMENT_TYPE "type",TABLESPACE_NAME "tbs",
    EXTENTS "exts",blocks "blks",INITIAL_EXTENT/1024/1024 "int",
    NEXT_EXTENT/1024/1024 "next",
    MIN_EXTENTS "min",MAX_EXTENTS "max"
    from dba_segments
    where owner='&owner'
    and SEGMENT_NAME = '&index_name';

    Now rebuild the index with different storage:
    Alter index rebuild tablespace storage (initial xxx next yyy);

    Now issue again the above query to see all the changes.

    If the index was unusable ,after the rebuilding examine its status:
    select status
    from dba_indexes
    where index_name='';

    The result before the rebuilding: UNUSABLE
    The result after the rebuilding should be: VALID

    Regards,
    Nir

  4. #4
    Join Date
    Mar 2003
    Posts
    34

    index rebuild

    What if I am rebuilding the index for deleted row entries & not for storage or ts. Than how will I check the status if the index is rebuild. Why the status in dba_indexes remains same (i.e, as when you analyzed the index) even after rebuilding it.

    When the STATUS will be UNUSABLE in the dba_indexes.

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... and for another point of view, try this thread.

    Don't get confused between rebuild and coalesce. See Oracle documentation for details.

    The (rather rare) situation described in the peasland article would be well addressed either by coalescing the index, or by using a reverse index (which would be my own preferred method).
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width