Rebuild index in Production
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rebuild index in Production

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    I am going to rebuild all Production Database indexes with a batch job. I want to know that do I have to run one of these
    commands after rebuild finish :

    ANALYZE INDEX COMPUTE STATISTICS;

    ANALYZE TABLE COMPUTE STATISTICS FOR ALL INDEXED COLUMNS


    Also I want to know because couple of tables are really big is it any problem can happen? For example I think I need a big Temp Tablespace !! Please advice what safety cution I have to take into effect ?
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You don't need to run analyze command separately.

    In the "CREATE INDEX ...." script add compute statistics ;
    This is faster than running the analyze command.


  3. #3
    Join Date
    Nov 2000
    Posts
    416
    I don't use create index , I want to user "ALTER" INDEX ....REBUILD . Also what I have to choose for next and first extent ( Rule of thunmb ? )
    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Ensure you have created a big temp tablespace for sorting, if you are using CREATE INDEX statement.

    Example for ALTER INDEX:


    ALTER INDEX SALES_IDX REBUILD
    TABLESPACE TS_IDX
    STORAGE ( INITIAL 20M NEXT 20M PCTINCREASE 0)
    PARALLEL NOLOGGING COMPUTE STATISTICS;

    ALTER INDEX SALES_IDX LOGGING;


    The INITIAL and NEXT value depend upon the table size.

  5. #5
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23

    Post Guidelines for rebuilding indexes

    Guidelines for rebuilding Index.

    Find out indexes that are fragmented

    1) select TABLESPACE_NAME , SEGMENT_NAME , COUNT(*)
    from DBA_EXTENTS
    where SEGMENT_TYPE = 'INDEX'
    group by TABLESPACE_NAME , SEGMENT_NAME HAVING COUNT(*) > 5
    ORDER BY 3 DESC

    this SQL gives you indexs that are fragmented. Change the
    group predicate condition if you wish


    2) analyze index validate structure
    will analyze the index structure

    3) select * from index_stats

    will display the computed statistics. Some significant fields are
    HEIGHT, BLOCK ,
    computed value for DEL_LF_ROWS /decode( LF_ROWS,0,1,LF_ROWS ))*100

    ( According to Oracle for any index, if HEIGHT > 4 or the computed ratio is greater than 25 then the index is a candidate for rebuild.)

    4 )select name , value from v$parameter
    where name = 'db_block_size' . This will give the value of
    db_block_size.

    5 ) Rebuild the index

    alter index rebuild tablespace
    strorage (initial comp_val1 , next comp_val2 , pctincrease 0 )

    comp_val1 = BLOCK ( generated in step 3 ) * value of db_block_size
    comp_val2 = comp_vale1/2


    6 ) If needed create a PL SQL script to autmatically perform all these
    steps and run it when the work load on the server is low.
    Oracle DBA
    National Bank Of Kuwait

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