Oracle SQL Script to Check Index Fragmentation and Maintenance

advertisements

_____________________________________________________________________________________________________________________

1. Check Index Fragmentation and Determine Level 

This script checks the index fragmentation level for indexes on a specific table. It queries the all_indexes data dictionary view based on the schema name and table name, and displays the index name, table name, B-level (branching factor), number of leaf blocks, distinct keys, and clustering factor. Higher clustering factor values indicate potential fragmentation.

 

-- Query to check index fragmentation and determine its level
SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME' AND table_name = 'YOUR_TABLE_NAME'
ORDER BY index_name;

-- Sample Output:
-- INDEX_NAME         TABLE_NAME     BLEVEL  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR
-- -----------------  -------------  ------  -----------  -------------  ----------------
-- IDX_CUSTOMER_ID    CUSTOMERS      2       50           1000           2000
-- IDX_ORDER_ID       ORDERS         1       30           500            600

advertisements

 

2. Identify Heavily Fragmented Indexes for Maintenance 

This script identifies heavily fragmented indexes that may require maintenance. It queries the all_indexes data dictionary view based on the schema name and filters indexes with a clustering factor greater than 1000. The output includes the index name, table name, B-level, number of leaf blocks, distinct keys, and clustering factor.

 

-- Query to identify heavily fragmented indexes for maintenance
SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME' AND clustering_factor > 1000
ORDER BY clustering_factor DESC;

-- Sample Output:
-- INDEX_NAME         TABLE_NAME     BLEVEL  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR
-- -----------------  -------------  ------  -----------  -------------  ----------------
-- IDX_CUSTOMER_ID    CUSTOMERS      2       50           1000           2000
-- IDX_ORDER_ID       ORDERS         1       30           500            1500

3. Rebuild or Reorganize Fragmented Indexes 

This script rebuilds a fragmented index using the ALTER INDEX ... REBUILD statement. Replace 'YOUR_INDEX_NAME' with the name of the index you want to rebuild or reorganize. This operation helps eliminate fragmentation and optimize index performance. Note that rebuilding an index requires appropriate privileges and may cause temporary unavailability of the index during the rebuilding process. Please customize 'YOUR_SCHEMA_NAME' and 'YOUR_TABLE_NAME' with the appropriate schema and table names in the scripts. Adjust the queries as needed for schema-specific information or additional details.

 

-- Query to rebuild or reorganize fragmented indexes
ALTER INDEX YOUR_INDEX_NAME REBUILD;

-- Sample Output: (No output is generated for this statement)

_____________________________________________________________________________________________________________________

Website Stats