Rebuild Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Rebuild Indexes

  1. #1
    Join Date
    May 2001
    Location
    New Zealand
    Posts
    123
    Hello

    Can one Analyze Indexes when users are working ?
    Can one Rebuild the same when users are working and how does one Rebuild Foreign Key primary Key Indexes ......

    Any help would be grateful.....
    Thanks

    Afshin

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by afshin
    Hello

    Can one Analyze Indexes when users are working ?
    Can one Rebuild the same when users are working and how does one Rebuild Foreign Key primary Key Indexes ......

    Any help would be grateful.....
    Thanks

    Afshin
    Hi,
    You may want to rebuild an index when index entries are removed as a
    result of many rows being deleted from the table.

    After using the command "analyze index index_name validate structure"
    you can view the information stored in the session-specific INDEX_STATS
    dictionary view to decide if you need to rebuild the index.

    If the length of all deleted values (stored in INDEX_STATS.DEL_LF_ROWS_LEN)
    is more than 20% of the the sum in bytes of the length of all values (stored
    in INDEX_STATS.LF_ROWS_LEN), then it might be good idea to rebuild the index.

    The following script I made for my DBs accepts as parameters the schema name(s) (and index name(s))
    and rebuilds all indexes which need to be rebuilt.

    accept owner prompt "Schema name for Indexes_to_rebuild (Enter for all): "
    accept index prompt "Index name to validate (Enter for all indexes owned by &owner): "
    --
    set serveroutput on
    set verify off
    prompt
    --
    DECLARE
    --
    w_owner varchar2(30); w_index varchar2(30);
    sql_text varchar2(256); sql_text2 varchar2(256);
    Dynamic_Cursor integer; Dynamic_Cursor2 integer;
    dummy integer; dummy2 integer;
    index_usage pls_integer;
    w_lf_rows_len index_stats.lf_rows_len%TYPE;
    w_broi pls_integer := 0;
    w_red varchar2(300);
    --
    cursor validate is
    select
    OWNER, OBJECT_NAME
    from
    SYS.DBA_OBJECTS
    where
    OWNER like upper('%&owner%') and
    OBJECT_NAME like upper('%&index%') and
    OBJECT_TYPE = 'INDEX'
    order by
    OBJECT_NAME;
    --
    BEGIN
    --
    dbms_output.enable(60000);
    open validate;
    fetch validate into w_owner, w_index;
    while validate%FOUND loop
    Dynamic_Cursor := dbms_sql.open_cursor;
    sql_text := 'analyze index '||w_owner||'.'||w_index||' validate structure';
    dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
    dummy := dbms_sql.execute(Dynamic_Cursor);
    dbms_sql.close_cursor(Dynamic_Cursor);
    -- let us see now if we have to rebuild this index:
    begin
    select LF_ROWS_LEN into W_LF_ROWS_LEN from index_stats;
    select decode(W_LF_ROWS_LEN,0,19,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100)
    into index_usage
    from index_stats;
    select name||' '||to_char(ceil((blocks*8)/1024)) into w_red from index_stats;
    dbms_output.put_line(w_red);
    -- if index_usage > 20, then we have to rebuild the index
    -- you may replace 20 with 30 if you wish
    if index_usage >= 20 then
    begin
    Dynamic_Cursor2 := dbms_sql.open_cursor;
    sql_text2 := 'alter index '||w_owner||'.'||w_index||' rebuild';
    dbms_sql.parse(Dynamic_Cursor2, sql_text2, dbms_sql.native);
    dummy2 := dbms_sql.execute(Dynamic_Cursor2);
    dbms_sql.close_cursor(Dynamic_Cursor2);
    end;
    -- message to the DBA:
    dbms_output.put_line(w_owner||'.'||w_index||' rebuilt for index usage being '||index_usage);
    w_broi := w_broi+1;
    end if;
    end;
    fetch validate into w_owner, w_index;
    end loop;
    close validate;
    if w_broi = 0
    then dbms_output.put_line(CHR(10)||'No indexes need not to be rebuilt (perhaps)'||CHR(10));
    elsif w_broi = 1
    then dbms_output.put_line(CHR(10)||'Only 1 index had to be rebuilt');
    else
    dbms_output.put_line(CHR(10)||w_broi||' indexes had to be rebuilt');
    end if;
    --
    END;
    /



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