How to Implement Oracle index Reconstruction

Oracle re-indexing is often used. The following describes the knowledge of Oracle re-indexing for your reference. If you are interested in this, take a look.

If some application projects under the Oracle database you manage have a large number of modification and deletion operations, the data index needs to be rebuilt periodically.

It not only improves query performance, but also increases the free space of the index tablespace. after a large number of records are deleted in ORACLE, the data block space occupied by tables and indexes is not released. oracle re-indexing can release the data block space occupied by the deleted record index. to transfer data, you can rename the data in the table.

The following is an SQL script that generates the ORACLE re-Indexing Based on the 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('|', 33 - (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 after spool:

Spool c: \ oracle \ rebuild _ & username. SQL;

If you only want to re-index An index greater than max_bytes, you can modify the preceding SQL statement:

Add a restriction AND bytes> & max_bytes after AND owner = '& username'

If you want to modify the index storage parameters, re-create the index rebuild _ & username in Oracle. SQL can also be changed. for example, change the value of pctincrease not equal to zero. generated rebuild _ & username. for SQL files, we need to analyze whether they have reached the level of reconstruction:

Analyze the index to see if the fragmentation is serious.

 
 

  
  
SQL>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 deletion ratio is greater than 15-20%, the index must be rebuilt. The deleted rebuild _ & username. SQL file can be stored in the scheduled ORACLE job:

For example, a month or two runs during non-busy hours. if a ORA-00054 error occurs, it indicates that the index has a lock information on the table and the index cannot be rebuilt. ignore this error and check whether it is successful next time. for tables that are particularly busy, we need to treat them differently. Instead of using the method described here, we need to take their indexes from rebuild _ & username. delete in SQL.
 

Complete Oracle File System Parsing

How to query the current Oracle system time

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module

Detailed explanation of four categories of Oracle index Scanning