If you manage a large number of modified deletions under the Oracle database, the data index is a periodic reconstruction. It not only improves query performance, but also adds the index table space to free space size. Deluted in Oracle After the record, the data block space occupied in the table and index is not released. Reconstruction Index can release data block space that has been removed from the deleted record index. Transfer data, renamed methods can reorganize the data in the table. Below you can press Oracle Username Generates SQL scripts that rebuild index: --------------------------------------- --------------------------------- set echo off; set feedback OFF; set veryness; set pagesize 0; SET TERMOUT ON; SET Heading Off; Accept Username Char Prompt 'ENTER THE INDEX Username:'; spool/racle/rebuild_&username.sql;select 'Rem -------------------------------------------------------------------------------------------------------- ---------------------------- '|| CHR (10) ||' Rem | index name: '|| Owner | | '.' || segment_name || LPAD ('|', 33 - (length (outner) length (segment_name))) || CHR (10) || 'Rem | BYTES:' || Bytes || LPAD '|', 34- (Length (bytes))) || CHR (10) || 'Rem | Extents:' || EXTENTS || LPAD ('|', 34- (Length (Extents)) || CHR (10) || 'REM -------------------------------------------------------------------------------------------------------------------- ----- '|| CHR (10) ||' ALTER INDEX '|| Owner ||'. '|| Segment_name || CHR (10) ||' Rebuild '|| CHR (10) ||' TableSpace '|| TableSpace_name || CHR (10) || 'Storage (' || CHR (10) || 'INITIAL' || Initial_Extent || CHR (10) || 'Next' || Next_extent || CHR (10) || 'MineXtents' || Min_extents || CHR (10) || 'MaxExtents' || CHR (10) || 'Pctincrease' || PCT_INCREASE || CHR (10) || ');' || CHR (10) || CHR (10) from dba_segmentswhere segment_type = 'index'and owner =' & username'Rder by Owner, Bytes desc; spool off;
-------------------------------------------------- ---------------------------
If you use a Windows system, you want to change the storage directory of the output file, modify the path behind the spool into: spool c: /oracle/rebuild_&username.sql; if you only want to rebuild the index of the index greater than Max_bytes, you can modify the SQL above Statement: After And Owner = '& username', add a restriction condition and bytes> & max_bytes
If you want to modify the storage parameters of the index, you can change the index rebuild_ & username.sql. For example, the value of Pctincrease does not equal to zero is zero.
The generated rebuild_ & username.sql file We need to analyze if they reach the degree of reconstruction: analysis index, see if the fragment is serious SQL> Analyze Index & index_name validate structure;
col name heading 'Index Name' format a30col del_lf_rows heading 'Deleted | Leaf Rows' format 99999999col lf_rows_used heading 'Used | Leaf Rows' format 99999999col ratio heading '% Deleted | Leaf Rows' format 999.99999
SELECT NAME, DEL_LF_ROWS, LF_ROWS - DEL_LF_ROWS LF_ROWS_USED, TO_CHAR (DEL_LF_ROWS / (LF_ROWS) * 100, '999.99999') Ratiofrom Index_stats where name = Upper ('& index_name');
When the ratio of the deletion is greater than 15 - 20%, it is definitely necessary to reconstruct it. After the deleted rebuild_ & username.sql file we can put it in the Timing operation of Oracle: such as a month or two months running in non-busy time. If In the event of an ORA-00054 error, it indicates that the index is locked in the table, which cannot be reconstructed. Then ignore this error, see if the next time is successful. For those special busy tables, it cannot be used herein, Also delete their index from rebuild_ & usrname.sql.
