To find duplicate value we need the sql group by & having clause as below.
SELECT V.FIELD_NAME1, COUNT(*) FROM TABLE_NAME V GROUP BY V.FIELD_NAME1 HAVING COUNT(*)>1
How query works?
- First it will group the same value by non-clustered index i.e. virtual indexing by oracle in-built.
- After grouping of same value, it will calculate the count of rows which is repeatedly.
- Query performance will be slower when you use Having clause because it will filter all data from actual output.