USER_INDEX dove il valore della colonna ‘Partitioned’ è = ‘NO’ mentre quelli locali vengono estratti nel cursore ‘cur_locale’ . Per ogni indice(globale o locale), la procedura genera statistiche utilizzando il comando ANALYZE INDEX con la clausola VALIDATE STRUCTURE; li estrae dalla vista INDEX_STATS, controlla i relativi valori dei campi HEIGHT, LF_ROWS e DEL_LF_ROWS e decide dove ricostruire o meno l’indice. Create or Replace Procedure RicostruzioneIndice (p_max_numero integer:= 3, p_max_delete integer:= 20) is cursor cur_indice is select name, height, lf_rows as num_rec, del_lf_rows as num_rec_del from index_stats; v_indice cur_indice%rowtype; cursor cur_globale is select index_name, tablespace_name from user_indexes where partitioned = 'NO'; cursor cur_locale is select index_name, partition_name, tablespace_name from user_ind_partitions where status = 'USABLE'; v_conta integer := 0; begin -- Indice Globale for v_record in cur_globale loop execute immediate 'analyze index ' || v_record.index_name ||' validate structure'; open cur_indice; fetch cur_indice into v_indice; if cur_indice%found then if (v_indice.height > p_max_numero) or (v_indice.num_rec >0 and v_indice.num_rec_del > 0 and (v_indice.num_rec_del * 100 / v_indice.num_rec) > p_max_delete) then v_conta := v_conta + 1; dbms_output.put_line('Ricostruzione dell''indice ' ||v_record.index_name || '...'); execute immediate 'alter index ' || v_record.index_name || ' rebuild' || ' parallel nologging compute statistics' || ' tablespace ' || v_record.tablespace_name; end if; end if; close cur_indice; end loop; dbms_output.put_line('Indice globale ricostruito: ' || to_char(v_conta)); v_conta := 0; -- Indice Locale for v_record in cur_locale loop execute immediate 'analyze index ' || v_record.index_name || ' partition (' || v_record.partition_name|| ') validate structure'; open cur_indice; fetch cur_indice into v_indice; if cur_indice%found then if (v_indice.height > p_max_numero) or (v_indice.num_rec > 0 and v_indice.num_rec_del > 0 and (v_indice.num_rec_del * 100 / v_indice.num_rec)> p_max_delete) then v_conta := v_conta + 1; dbms_output.put_line('Ricostruzione dell''indice ' ||v_record.index_name || '...'); execute immediate 'alter index ' || v_record.index_name || ' rebuild' || ' partition ' || v_record.partition_name || ' parallel nologging compute statistics' || ' tablespace ' || v_record.tablespace_name; end if; end if; close cur_indice; end loop; dbms_output.put_line('Indice locale ricostruito: ' || to_char(v_conta)); end RicostruzioneIndice;]]>
