Wednesday, June 10, 2020

Query to list Unindexed Foreign Keys



Query to get Unindexed Foreign Keys 


undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
column fk_columns format a30
column index_name format a30
column index_columns format a30


select
      case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
      ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
              within group (order by column_name) as fk_columns
      from
              dba_constraints a
             ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
      and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
      group by
              a.table_name
             ,a.constraint_name
    ) c
left outer join
  (
    select  table_name
           ,index_name
           ,cr
           ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
        (
          select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
                  dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
         )
    group by table_name
            ,index_name, cr
  ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name;





SQL> undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
SQL> SQL> SQL> SQL> SQL> SQL> column fk_columns format a30
column index_name format a30
column index_columns format a30


select
SQL> SQL> SQL> SQL> SQL>   2        case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
  3    4    5    6    7    8        ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
  9   10   11   12   13  from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
 14   15   16   17   18                within group (order by column_name) as fk_columns
      from
              dba_constraints a
 19   20   21               ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
 22   23   24   25        and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
 26   27        group by
 28                a.table_name
             ,a.constraint_name
    ) c
 29   30   31  left outer join
  (
    select  table_name
           ,index_name
           ,cr
 32   33   34   35   36             ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
 37   38   39          (
 40            select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
 41   42   43   44   45   46   47                    dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
 48           )
    group by table_name
            ,index_name, cr
 49   50   51   52   53   54   55   56    ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name; 57   58
Enter value for schema_owner: HR
old  26:       and     a.owner           = '&&schema_owner'
new  26:       and     a.owner           = 'HR'
old  49:                   index_owner               = '&&schema_owner'
new  49:                   index_owner               = 'HR'

STATUS     TABLE_NAME                     FK_NAME                        FK_COLUMNS                     INDEX_NAME                     IN                       DEX_COLUMNS
---------- ------------------------------ ---------------  ------- ------------------------------ ----------------       ---------------        --------------                    
indexed    EMPLOYEES                      EMP_JOB_FK                     JOB_ID                         EMP_JOB_IX                     JO                       B_ID
indexed    EMPLOYEES                      EMP_MANAGER_FK                 MANAGER_ID                     EMP_MANAGER_IX                 MA                       NAGER_ID
indexed    EMPLOYEES                      EMP_DEPT_FK                    DEPARTMENT_ID                  EMP_DEPARTMENT_IX              DE                       PARTMENT_ID
indexed    LOCATIONS                      LOC_C_ID_FK                    COUNTRY_ID                     LOC_COUNTRY_IX                 CO                       UNTRY_ID
indexed    DEPARTMENTS                    DEPT_LOC_FK                    LOCATION_ID                    DEPT_LOCATION_IX               LO                       CATION_ID
indexed    JOB_HISTORY                    JHIST_JOB_FK                   JOB_ID                         JHIST_JOB_IX                   JO                       B_ID
indexed    JOB_HISTORY                    JHIST_EMP_FK                   EMPLOYEE_ID                    JHIST_EMPLOYEE_IX              EM                       PLOYEE_ID
indexed    JOB_HISTORY                    JHIST_DEPT_FK                  DEPARTMENT_ID                  JHIST_DEPARTMENT_IX            DE                       PARTMENT_ID
indexed    JOB_HISTORY                    JHIST_EMP_FK                   EMPLOYEE_ID                    JHIST_EMP_ID_ST_DATE_PK        EM                       PLOYEE_ID
unindexed  DEPARTMENTS                    DEPT_MGR_FK                    MANAGER_ID
unindexed  COUNTRIES                      COUNTR_REG_FK                  REGION_ID

11 rows selected.

SQL>

No comments:

Post a Comment

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

PostgreSQL Process Architecture Diagram and Details

PostgreSQL Process Architecture Diagram                            +------------------+                            |    POSTMASTER    |     ...