Methods for rebuilding indexes by user name in an Oracle database

If you manage some application projects under the Oracle database that have a large number of modifications to delete operations, data indexing needs to be periodically rebuilt.

It can not only improve query performance, but also increase the amount of free space in index table space.

After a large number of records were deleted in Oracle, the block space occupied by the tables and indexes was not released.

Rebuilding an index frees up the data block space used by the deleted records index.

Transfer data, renaming methods can rearrange the data in the table.

The following is a SQL script that can generate a rebuild index by Oracle user name:

SET ECHO off;
SET FEEDBACK off;
SET VERIFY off;
SET PAGESIZE 0;
SET termout on;
SET HEADING off;
ACCEPT username CHAR PROMPT ' Enter the index username: ';
spool/oracle/rebuild_&username.sql;
SELECT
' REM +-----------------------------------------------+ ' | | Chr (10) | |
' REM | INDEX NAME: ' | | Owner | | '.' || Segment_name
|| Lpad (' | ')-(length (owner) + 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 ' | | max_extents | | Chr (10) | |
' Pctincrease ' | | Pct_increase | | Chr (10) | |
');' || Chr (10) | | Chr (10)
From Dba_segments
WHERE segment_type = ' INDEX '
and owner= ' &username '
Order by owner, bytes DESC;
Spool off;

If you are using a Windows system and want to change the storage directory of the output file, modify the path following the spool:

Spool c:oraclerebuild_&username.sql;

If you only want to rebuild the index for an index larger than max_bytes, you can modify the SQL statement above:

After and owner= ' &username ', add a restriction condition and bytes> &max_bytes

If you want to modify the index's storage parameters, you can rebuild_&username.sql the rebuild index.

For example, the value of pctincrease not equal to zero is changed to zero.

Generated rebuild_&username.sql files We need to analyze whether they are up to the degree that they need to be rebuilt:

Analyze indexes to see if fragmentation is seriousSQL>ANALYZE INDEX &index_name VALIDATE STRUCTURE;
col name heading 'Index Name' format a30
col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
col lf_rows_used heading 'Used|Leaf Rows' format 99999999
col 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') ratio
FROM index_stats where name = upper('&index_name');

When the rate of deletion is greater than 15-20%, it is definitely necessary to rebuild the index.

After the deleted Rebuild_&username.sql file we can put it into Oracle's timed work:

For example, one months or two months running during off-peak hours.

If you encounter a ORA-00054 error that indicates that the index has lock information on the table, the index cannot be rebuilt.

Then ignore the mistake and see if it succeeds next time.

For those particularly busy tables to be treated differently, not in the way described here,

They also have to delete their index from the Rebuild_&username.sql.