11/27/2021

How To Find Duplicate Records In Oracle Using Rowid

  1. Oracle Sql Get First Row From Duplicates
  2. How To Eliminate Duplicate Rows In Oracle Without Using Rowid
  3. Oracle Duplicate Row
(Last Updated On: January 8, 2020)

Finding rows with duplicate keys is easy. Knowing WHICH of those rows to delete isn't. Simply because the keys occur twice doesn't necessarily mean the entire rows are duplicate. You could easily delete the data you want to keep with a simple statement like the one posted. 5 ways to delete duplicate records Oracle Oracle PLSQ SQL. Here you can use both rank and densrank since both will give unique records when order by rowid. Consider the following table with rows as an example: Table Name: Products ProductId Price - 1 10 1 10 2 20 3 30 3 30 Here assume that productId column should be unique after deleting. Now we see how to delete the duplicate records from the products table in different ways. You want to write a common procedure to find and remove duplicates from any table and columns in Oracle. Solution: We can use Oracle’s internal ROWID value for uniquely identifying rows in a table along with the OLAP function rownumber with partition clause. The sample syntax to acheive this would like below. Delete from table where rowid in.

Learn the about the logical columns that are not exist in the table, but how can be used to select the data from the table, with explanation and example queries.

Rowid, Rownum are the Pseudo columns in oracle used to select the data from tables.

Rowid

  • ROWID is a pseudo column in a table which store and return row address in HEXADECIMAL format with database tables.
  • ROWID is the permanent unique identifiers for each row in the database.
  • ROWID consists of 18 character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle.

Below is the example query to distinguish rowid address.

Example query:

Output

PL/SQL Developer ExportUsing
ROWIDEMPNO
AADZmhABAAAAck0AAA1
AADZmhABAAAAck0AAB2
AADZmhABAAAAck0AAC3
AADZmhABAAAAck0AAD4
AADZmhABAAAAck0AAE5
AADZmhABAAAAck0AAF6
AADZmhABAAAAck0AAG7
AADZmhABAAAAck0AAH8
AADZmhABAAAAck0AAI9
AADZmhABAAAAck0AAJ10
AADZmhABAAAAck0AAK11
AADZmhABAAAAck0AAL12
AADZmhABAAAAck0AAM13
AADZmhABAAAAck0AAN14

Notes:

  1. ROWID is representative of the allocation of physical memory.
  2. Oracle automatically generates a unique ROWID at the time of insertion of a row.
  3. ROWID is the permanent identity or address of a row which one can easily access(retrieve data from a row) the rows from the table.
  4. A user can access a row quickly and easily using its row ID.
  5. ROWID can also be used to delete normal records and the duplicate records from a table.

Below are the example queries to demonstrate the point no 4 and 5 from the Note points.

Example query for point number 4

Query to access a row quickly and easily using its row ID.

Example query for point number 5

Query to delete the records from the table using rowid;

Query to delete the duplicate records using rowid.

for duplicate row delete please click on delete duplicate rows link.

When a rowid changes?

A rowid is assigned to a row upon insert and is immutable(never changing), changes happen only inthe following situations.

  1. when you update a partition key and row moves the partition.
  2. Flashback a table,since the flash back table command really issues a DELETE + INSERT to put the data back the way it was.
  3. When you move the table with alter command would change the rowids.

For point number 3 below is the example for better understanding purpose.

ROWIDROWNUMEMPNO
AADZmhABAAAAck0AAA11
AADZmhABAAAAck0AAB22
AADZmhABAAAAck0AAC33
AADZmhABAAAAck0AAD44
PL/SQL Developer Export
ROWIDROWNUMEMPNO
AADazGAAdAAAInTAAA11
AADazGAAdAAAInTAAB22
AADazGAAdAAAInTAAC33
AADazGAAdAAAInTAAD44
PL/SQL Developer Export
ROWIDROWNUMEMPNO
AADazKAAdAAAInrAAA11
AADazKAAdAAAInrAAB22
AADazKAAdAAAInrAAC33
AADazKAAdAAAInrAAD44

If we observe the above MYEMP table result, the rowid has changed after moving the table.

ROWNUM:

  • Oracle engine maintains the number of each record inserted by users in table.
  • Rownum generates sequence number for each record each record inserted by users in table.
  • By the help of ROWNUM clause we can access the data according to the record inserted.
  • Rownum is used to limit the number of records to fetch from the table.

if we run the below query, only first 4 records will be fetched from the table.

  • Order of rown num in the select query will change depeds on the order by clause.

for example,

ROWNUMEMPNOENAMEMGRHIREDATESALDEPTNAMESAMPLE
77CLARK6/9/19812450.00SERVICES
44JONES4/2/19812975.00INSURANCE
33WARD2/22/19811250.00BANKING
1212JAMES712/3/1981950.00SERVICES
66BLAKE75/1/19812850.00SERVICES
22ALLEN42/20/19811600.00INSURANCE
1414MILLER41/23/19821300.00INSURANCE
88SCOTT412/9/19823000.00INSURANCE
1313FORD412/3/19813000.00INSURANCE
1010TURNER49/8/19811500.00INSURANCE
55MARTIN49/28/19811250.00INSURANCE
1111ADAMS31/12/19831100.00BANKING
11SMITH312/17/1980800.00BANKING
99KING311/17/19815000.00BANKING

Notes:

  1. row num is a sequential number, allocated to each return row during query executuion.
  2. row num is a numeric number and is temporary(row num changes depends on the where condition in select query).
  3. using row num we can count the number of records.
  4. The row numbers of the records might change if an order by clause is used in the query(refer above example query).

ROW_NUMBER()

  1. ROW_NUMBER is an analytical function that assigns a number to each row according to its ordering within a group of rows.
  2. Row_Number generates sequence of numbers after performing sorting operation on specific column(order by clause on a column)
  3. Row_Number cannot be used in where clause.It can used to filter only after sorting by using outer query.
  4. ROW_NUMBER can contain duplicates when we use partition by on column.

for example queries please click on Oracle analytical functions.

Difference between ROWID, ROWNUM, ROW_NUMBER

SRNOROW_IDROW_NUMROW_NUMBER
1ROWID is a pseudocolumnROWNUM is a pseudocolumnROW_NUMBER is a analytical function.
2Represents the physical address of rows.ROWNUM is the sequential number, allocated to each returned row during query exectuion.ROW_NUMBER assigns a number to each row according to its ordering within a group of rows.
3Rowid is permanentROWNUM is temporaryROW_NUMBER is temporary
4ROWID is 18 character stringROWNUM is numericROW_NUMBER is a function that returns numeric value.
5ROWID gives the address of rows or records.ROWNUM gives the count of recordsROW_NUMBER gives the rank of records.
6ROWID is automatically generated unique id at the time of insertion of row in the table.ROWNUM is a dynamic ROW_NUMBER is dynamic
7ROWID is the fastes means of accesing data.ROWNUM is retreived along with the select statement.ROW_NUMBER is retreived along with the select statement.
8ROWIDs are unique identifiers for the any row in the table.ROWNUM is the sequential number in which oracle has retreived the row(ROWNUM generated before sorting).ROW_NUMBER is the sequential number in which oracle has retreived the row. ROW_NUMBER may contains duplicate if it performs partion by operation.(ROW_NUMBER generated after sorting operation)
9ROWID can be used in where clause for selecting,updating and deleting the rows.ROWNUM can be used in where clause for selecting,updating and deleting the rows.ROW_NUMBER can be used in where clause for selecting,updating and deleting the rows.
10Can delete duplicate rowsCan delete duplicate rowsCan delete duplicate rows

Below are the example query for point number 8 in difference between rowid , rownum, row_number()

Query example 1:

Output

PL/SQL Developer Export
EMPNOROWIDROWNUMROW_NUMBER()OVER(ORDERBYMGR)
19AADZmhABAAAAck0AAI91
21AADZmhABAAAAck0AAA12
311AADZmhABAAAAck0AAK113
414AADZmhABAAAAck0AAN144
58AADZmhABAAAAck0AAH85
62AADZmhABAAAAck0AAB26
715AADZmhABAAAAck0AAM137
810AADZmhABAAAAck0AAJ108
95AADZmhABAAAAck0AAE59
1012AADZmhABAAAAck0AAL1210
116AADZmhABAAAAck0AAF611
124AADZmhABAAAAck0AAD412
133AADZmhABAAAAck0AAC313
147AADZmhABAAAAck0AAG714

The above output clearly shows that rownum generated the sequence number before sorting(order by clause) and Row_number() function generated the sequence number after sorting(order by clause)

Query example 2:

Below query show the duplicate number generation for row_number() using partition by clause.

Output

PL/SQL Developer Export
EMPNOROWIDROWNUMROWNUMBER
19AADZmhABAAAAck0AAI91
21AADZmhABAAAAck0AAA12
311AADZmhABAAAAck0AAK113
414AADZmhABAAAAck0AAN141
58AADZmhABAAAAck0AAH82
62AADZmhABAAAAck0AAB23
715AADZmhABAAAAck0AAM134
810AADZmhABAAAAck0AAJ105
95AADZmhABAAAAck0AAE56
1012AADZmhABAAAAck0AAL121
116AADZmhABAAAAck0AAF62
124AADZmhABAAAAck0AAD41
133AADZmhABAAAAck0AAC32
147AADZmhABAAAAck0AAG73

Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

Oracle Sql Get First Row From Duplicates

  • Subquery to identify duplicate rows
  • Use RANK to find and remove duplicate table rows
  • Use self-join to remove duplicate rows
  • Use analytics to detect and remove duplicate rows
  • Delete duplicate table rows that contain NULL values

Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
table_name A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);

Use RANK to delete duplicate rows

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, many shops do not use RI because they need the flexibility.


Use self-join to delete duplicate rows

Get
The most effective way to detect duplicate rows is to join the table against itself as shown below.
select
book_unique_id,
page_seq_nbr,
image_key
from
page_image a
where
rowid >
(select min(rowid) from page_image b
where
b.key1 = a.key1
and
b.key2 = a.key2
and
b.key3 = a.key3
);

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below:
delete from
table_name a
where
a.rowid >
any (select b.rowid
from
table_name b
where
a.col1 = b.col1
and
a.col2 = b.col2
)
;

Use analytics to delete duplicate rows

You can also detect and delete duplicate rows using Oracle analytic functions:

How To Eliminate Duplicate Rows In Oracle Without Using Rowid


delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);

Oracle Duplicate Row


As we see, there are several ways to detect and delete duplicate rows from Oracle tables