SQL Query to Get Tables Which are Not Having Index on Foreign Key(FK) Column

advertisements

_____________________________________________________________________________________________________________________

To identify tables in an Oracle database that do not have an index on their foreign key columns, you can use the USER_CONSTRAINTS and USER_INDEXES views. Here's a SQL query to retrieve such information 

 We are querying the USER_CONSTRAINTS view to get information about constraints in the current user's schema. Specifically, we focus on constraints with CONSTRAINT_TYPE = 'R', which indicates foreign key constraints. 

 We perform a LEFT JOIN with the USER_INDEXES view, which contains information about indexes in the current user's schema. 

 The join condition is based on the R_CONSTRAINT_NAME from USER_CONSTRAINTS and the INDEX_NAME from USER_INDEXES. 

 The WHERE clause filters the results to show only foreign key constraints (CONSTRAINT_TYPE = 'R') that do not have an associated index (UI.INDEX_NAME IS NULL).

advertisements

 

SELECT
  UC.TABLE_NAME AS table_name,
  UC.CONSTRAINT_NAME AS foreign_key_name
FROM
  USER_CONSTRAINTS UC
LEFT JOIN
  USER_INDEXES UI
ON
  UC.R_CONSTRAINT_NAME = UI.INDEX_NAME
WHERE
  UC.CONSTRAINT_TYPE = 'R'
  AND UI.INDEX_NAME IS NULL;

Sample Output

TABLE_NAME   | FOREIGN_KEY_NAME
-------------|-----------------
EMPLOYEES    | FK_DEPT_ID
ORDERS       | FK_CUSTOMER_ID

In the sample output, the query identifies two tables, EMPLOYEES and ORDERS, that have foreign key constraints without an associated index on their respective foreign key columns (FK_DEPT_ID and FK_CUSTOMER_ID).

_____________________________________________________________________________________________________________________

Website Stats