Comparing Indexes Across Oracle Databases with DB Links and Custom SQL Query

In Oracle database management, optimizing performance is paramount, and one key aspect of this optimization is efficient indexing. Indexes play a crucial role in speeding up query execution by providing quick access to data. However, managing indexes across different environments, such as development, QA, and production, can be challenging. In this blog post, we’ll explore a method for comparing indexes between different Oracle databases using a “poor man’s” approach with DBLink and SQL queries.

Understanding the Setup: For our comparison, we’ll focus on three environments: Development (DEV), Quality Assurance (QA), and Production (PROD). Each environment has its own schema containing tables and indexes. We’ll utilize a DBLink to query index information from one environment to another.

Querying Index Information: We begin by creating views in each environment to extract index details. These views (DBADEEDS_DEV_IND, DBADEEDS_QA_IND, and DBADEEDS_PROD_IND) retrieve index metadata such as owner, index name, table name, and column name, excluding system indexes.

Index Comparison Query: Next, we construct a query to compare indexes across environments. Using common table expressions (CTEs), we retrieve index data from DEV and QA environments (DevData and QaData). Then, we join these datasets to identify matches, differences, and missing indexes.

Output and Analysis: The comparison query generates a comprehensive report highlighting the status of each index: matched, not in QA, not in DEV, or not matched. This output provides valuable insights into index consistency across environments, aiding in performance tuning and troubleshooting.


CREATE DATABASE LINK SCH_DV_LINK from prod to dev or prod to qa or vicevesa

CREATE OR REPLACEVIEW DBA.DBADEEDS_DEV_INDAS
select
    b.owner,
    a.index_name,
    a.table_name,
    a.column_name fromall_ind_columnsa,
    all_indexesb wherea.index_name = b.index_name andb.owner = upper('DBADEEDS_TD1') ANDa.column_nameNOTLIKE 'SYS_%' orderbya.table_name,
    a.index_name,
    a.column_positionASC;

CREATE
OR REPLACE VIEW DBADEEDS_QA_IND AS
select
    b.owner,
    a.index_name,
    a.table_name,
    a.column_name fromall_ind_columnsa,
    all_indexesb wherea.index_name = b.index_name andb.owner = upper('DBADEEDS_TQ1') ANDa.column_nameNOTLIKE 'SYS_%' orderbya.table_name,
    a.index_name,
    a.column_positionASC;

CREATE
OR REPLACE VIEW DBADEEDS_PROD_IND AS
select
    b.owner,
    a.index_name,
    a.table_name,
    a.column_name fromall_ind_columnsa,
    all_indexesb wherea.index_name = b.index_name andb.owner = upper('DBADEEDS_TP1')
    AND a.column_name NOT LIKE 'SYS_%' order by a.table_name,
    a.index_name,
    a.column_position ASC;

WITHDevDataAS(
    SELECTDISTINCTOWNERASDEV_OWNER,
    TABLE_NAMEASDEV_TABLE_NAME,
    INDEX_NAMEASDEV_INDEX_NAME,
    COLUMN_NAMEASDEV_COLUMN_NAME FROM DBA.DBADEEDS_DEV_IND @SCH_DV_LINKWHERETABLE_NAME = 'emp'
),
QaDataAS(
    SELECTDISTINCTOWNERASQA_OWNER,
    TABLE_NAMEASQA_TABLE_NAME,
    INDEX_NAMEASQA_INDEX_NAME,
    COLUMN_NAMEASQA_COLUMN_NAME FROM DBA.DBADEEDS_QA_INDWHERETABLE_NAME = 'emp'
)
SELECT
    DevData.DEV_OWNER,
    QaData.QA_OWNER,
    DevData.DEV_TABLE_NAME,
    QaData.QA_TABLE_NAME,
    DevData.DEV_INDEX_NAME,
    QaData.QA_INDEX_NAME,
    DevData.DEV_COLUMN_NAME,
    QaData.QA_COLUMN_NAME,
    CASE
        WHENDevData.DEV_TABLE_NAME = QaData.QA_TABLE_NAMEANDDevData.DEV_INDEX_NAME = QaData.QA_INDEX_NAMETHEN 'MATCHED' WHEN DevData.DEV_TABLE_NAME = QaData.QA_TABLE_NAMEANDDevData.DEV_INDEX_NAMEISNULLTHEN 'NOT_IN_QA' WHENDevData.DEV_TABLE_NAMEISNULLANDDevData.DEV_INDEX_NAME = QaData.QA_INDEX_NAMETHEN 'NOT_IN_DEV'
        ELSE 'NOT_MATCHED' END AS RESULT FROMDevData FULLOUTERJOIN QaDataONDevData.DEV_TABLE_NAME = QaData.QA_TABLE_NAMEANDDevData.DEV_INDEX_NAME = QaData.QA_INDEX_NAME;

Example Output:

DEV_OWNER | QA_OWNER | DEV_TABLE_NAME | QA_TABLE_NAME | DEV_INDEX_NAME | QA_INDEX_NAME | DEV_COLUMN_NAME | QA_COLUMN_NAME | RESULT
------------------------------------------------------------------------------------------------------------------------------
DBADEEDS_TD1 | DBADEEDS_TQ1 | emp | emp | IDX1_emp | IDX1_emp | COLUMN1 | COLUMN1 | MATCHED
DBADEEDS_TD1 | DBADEEDS_TQ1 | emp | emp | IDX1_emp | IDX1_emp | COLUMN2 | COLUMN1 | NOTMATCHED
DBADEEDS_TD1 | NULL | emp | NULL | IDX2_emp | NULL | COLUMN2 | NULL | NOT_IN_QA
NULL | DBADEEDS_TQ1 | NULL | emp | NULL | IDX3_emp | NULL | COLUMN3 | NOT_IN_DEV

Conclusion: Efficient index management is critical for maintaining optimal database performance. By leveraging DBLink and SQL queries, we can perform index comparisons across multiple Oracle databases. This “poor man’s” approach offers a practical solution for identifying discrepancies and ensuring consistency in index configurations across different environments.n this output, we can see the comparison results for indexes on table emp between DEV and QA environments. The RESULT column indicates whether indexes are matched, missing in QA, missing in DEV, or not matched.

DBADEEDS