How to find duplicate records in a table

A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked.  Now we want to find and delete those duplicates.

In this article, we’ll see how to achieve this.

Consider the table dup_emp with these values:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select *   2  from dup_emp   3  order by empno;        EMPNO ENAME                 SAL ---------- -------------- ----------          1 Adam                  400          2 Sandy                 300          2 Ted                   450          3 Mark                  450          4 Alan                  450          4 Carol                 200          4 Peter                 250          5 David                 350   8 rows selected.

As you can see, there are two entries for empno 2 and three entries for empno 4. We want each empno to correspond to a single employee only.

The query below finds all duplicate records i.e. all empnos with more than one entry in the table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select *   2  from   3  (select d.*   4        , count(*) over   5          (partition by empno) cnt   6   from dup_emp d   7  )   8  where cnt > 1;        EMPNO ENAME   SAL        CNT ---------- ------ ---- ----------          2 Sandy   300          2          2 Ted     450          2          4 Alan    450          3          4 Carol   200          3          4 Peter   250          3

Deleting the duplicate records

The usual need is to delete all but one such row. The first thing is to know the deciding factor -out of the duplicates, which one is to be retained?

The next SQL retains the row with the minimum rowid:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> delete from dup_emp a   2  where rowid >   3    (select min(rowid)   4     from dup_emp b   5     where a.empno = b.empno);   3 rows deleted.   SQL> select *   2  from dup_emp;        EMPNO ENAME   SAL ---------- ------ ----          1 Adam    400          2 Sandy   300          3 Mark    450          4 Alan    450          5 David   350

Change the WHERE condition according to your needs.

The Difference Between Views and Materialized Views

Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion to beginners.

Here’s a summary of the key differences between views and materialized views, to put an end to all mix-ups. By the end of the article, you should be in a position to decide if your specific scenario needs a view, a materialized view, or neither.

1. Moment Of Execution

A view’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.

A materialized view (called snapshot in older Oracle versions) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the database and the query only browses the result.

2. Space

A view occupies no space (other than that for its definition in the data dictionary).

A materialized view occupies space. It exists in the same way as a table: it sits on a disk and could be indexed or partitioned.

3. Freshness of Output

A view’s output is built on the fly; it shows real-time data from the base tables being queried.

A materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.

4. Where To Use

A view is best used when:

• You want to hide the implementation details of a complex query
• You want to restrict access to a set of rows/columns in the base tables

A materialized view is best used when:

• You have a really big table and people do frequent aggregates on it, and you want fast response
• You don’t mind the result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)

Caution!

Are you creating a materialized view to avoid the pain of tuning a query? Don’t do it! A materialized view brings with it the overhead of maintaining extra DB objects that need regular refresh besides giving you out-of-date data, all for something that might have been fixed by writing better code.

Whether your query runs directly on tables, on views or via materialized views, it must be the most efficient query possible.

Materialized views are NOT a quick fix for bad code.

Inline Views

An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery was a table name.

A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select *   2  from   3    -- Inline view starts   4    (   5      select ename   6            ,sal   7            ,dense_rank() over (order by sal desc) dr   8      from   emp   9    )  10    -- Inline view ends  11  where dr = 4 -- Replace 4 with any value of N;   ENAME             SAL         DR ---------- ---------- ---------- BLAKE            2850          4 CLARK            2850          4

Did you notice the structure of the queries to pick odd rows or even rowsfrom a table? They use inline views too.

Inline views have the word "view" in them, they behave like views, but they are different from views in one crucial aspect: inline views are not database objects. They do not occupy storage.

Why use inline views?  Why not just create the view?

Prior to the introduction of inline views in Oracle 7.1, we had no choice but to create a database view for queries like the Top-N query.

Creating a view implies another database object to maintain. Why have that, when all you need is query results on the fly?

By placing the view within the main SELECT, you have all the code needed for the query in one place.