Rebuild Indexes

Quote:

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;
/