I use the following query to fetch the index names and thier status from user_objects table. I'm using the subquery to avoid those index names which are created when the primary / unique key is created. But this query takes a long time to execute. Is there any way to optimize it ?
SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX' AND GENERATED='N' AND OBJECT_NAME NOT IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS)