1

Probably asking very stupid question:

Can table in Oracle db has fully duplicate records. I.e.:

DUP_TABLE:
 NUMBER(8,0) field_A
 NUMBER(8,0) field_B

Has this data:

field_A field_B
123     456 
123     456

How in this case DB engine will distinguish those records?

Vladimir
  • 12,753
  • 19
  • 62
  • 77
  • 1
    Oracle assigns implicit row numbers to distinguish rows internally, but it is usually a good idea to add a unique or primary key to tables to achieve uniqueness. Look into Oracle Sequences. – ron tornambe Apr 28 '13 at 17:46

2 Answers2

4

Oracle (and most relational databases,) will allow you to create tables that can have duplicate rows. If you want to exclude duplicates, simply add a UNIQUE constraint that spans all of the columns of the table.

If duplicate rows are what you desire, when you query the table you will get back as many copies as there are in the table unless you have a DISTINCT clause or some kind of grouping that unifies the duplicates to a single instance.

Oracle stores each record at a separate location that it keeps track of internally. Users can SELECT rowid FROM a_table; to get a pointer to that location, but one should think carefully about why you are doing so first as it may be a sign of bad database design - if the two records are supposed to be distinguishable, perhaps there needs to be another column with the information that distinguishes them? Or, a database managed sequence number can provide unique numbers that are more readily communicated to users than rowids.

Example of duplicates and rowids.

Discussion of issues with rowids changing over time.

Community
  • 1
  • 1
gwaigh
  • 1,182
  • 1
  • 11
  • 23
3

Oracle adds a rowid named column implicitly to each row. You can query this rowid:

select rowid, field_A, field_B from DUP_TABLE;

Extracting from rowid you can get more info about the physical storage of the queried record: 

select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
       DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
from DUP_TABLE;
Donato Szilagyi
  • 4,279
  • 4
  • 36
  • 53