How to find the top 10 largest indexes in oracle

In Oracle 11g, the method to find the top 10 largest indexes  

1. Using DBA_SEGMENTS (For DBAs)

This query will return the top 10 largest indexes in the database:

SELECT * FROM (
    SELECT owner,
           segment_name AS index_name,
           ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
    FROM dba_segments
    WHERE segment_type = 'INDEX'
    GROUP BY owner, segment_name
    ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;

• Explanation:

• The segment_type = 'INDEX' filter limits the results to indexes.
• ROWNUM <= 10 restricts the result to the top 10 largest indexes by size in MB.
• The SUM(bytes) is used to calculate the total size of each index in the dba_segments view.

2. Using USER_SEGMENTS (For Non-DBA Users)

If you do not have access to DBA_SEGMENTS and are working with your schema, use the USER_SEGMENTS view:

SELECT * FROM (
    SELECT segment_name AS index_name,
           ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
    FROM user_segments
    WHERE segment_type = 'INDEX'
    GROUP BY segment_name
    ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;

• This query will show the top 10 largest indexes in the current user’s schema.

3. Using DBA_INDEXES and DBA_SEGMENTS (For DBAs)

To get additional details like the index type, associated table, and tablespace, you can join DBA_INDEXES and DBA_SEGMENTS:

SELECT * FROM (
    SELECT i.owner,
           i.index_name,
           i.table_name,
           i.tablespace_name,
           ROUND(SUM(s.bytes) / 1024 / 1024, 2) AS size_mb
    FROM dba_indexes i
    JOIN dba_segments s
        ON i.owner = s.owner
        AND i.index_name = s.segment_name
    WHERE s.segment_type = 'INDEX'
    GROUP BY i.owner, i.index_name, i.table_name, i.tablespace_name
    ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;

• This query will provide the index name, table name, tablespace name, and the size of the index in MB.

4. Querying for Indexes in a Specific Schema

If you're only interested in the indexes in a specific schema, you can add a filter for the schema (owner):

SELECT * FROM (
    SELECT owner,
           segment_name AS index_name,
           ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
    FROM dba_segments
    WHERE segment_type = 'INDEX'
      AND owner = '<SCHEMA_NAME>'
    GROUP BY owner, segment_name
    ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;

• Replace <SCHEMA_NAME> with the specific schema name you're interested in.

---

Key Notes:

• In Oracle 11g, use ROWNUM <= 10 instead of FETCH FIRST 10 ROWS ONLY, as the latter is not supported in 11g.
• DBA_SEGMENTS is used for accessing index sizes across the entire database, while USER_SEGMENTS is for accessing indexes only in the current user's schema.
• DBA_INDEXES allows for more detailed information, such as the associated table and tablespace, in addition to the size of the indexes.