Being just a little bit unique

A few weeks back, I saw a mention of an old Tanel Poder post about the uniqueness of ROWIDs.

This is an interesting subject as a lot of people expect ROWIDs to be unique, but don't think about how unique that is. While you can't be a little bit pregnant, you can be a little bit unique. Or at least unique only within a specific subgroup.

The only complaint I have about the piece is that it is very DBAish. Lots of moving files around the OS and all those other things that DBAs like to do.

I'm a developer, and like to stick with SQL. And it is pretty easy to demonstrate the non-uniqueness of a ROWID without leaving SQL*PLUS.


SQL> CREATE CLUSTER emp_dept
  2     (dept_id NUMBER(4));


SQL> CREATE TABLE dept_c
  2     (dept_id number(4) primary key, dept_name varchar2(20))
  3     CLUSTER emp_dept (dept_id)
  4     ;


SQL> CREATE TABLE emp_c
  2     (emp_id number primary key,  name varchar(20), dept_id number(4))
  3     CLUSTER emp_dept (dept_id)
  4     ;


SQL> CREATE INDEX idx_emp_dept ON CLUSTER emp_dept;


SQL> insert into dept_c values (10,'Accounts');


SQL> insert into emp_c values (1,'Scott',10);


SQL> select rowid from dept_c
  2  union
  3  select rowid from emp_c;


ROWID
------------------
AAAF/4AAEAAACX9AAA



SQL> select rid, count(*), min(type), max(type)
  2  from
  3    (select 'D' type,  rowidtochar(rowid) rid from dept_c
  4    union all
  5    select 'C' type, rowidtochar(rowid) rid from emp_c)
  6  group by rid;


RID                  COUNT(*) M M
------------------ ---------- - -
AAAF/4AAEAAACX9AAA          2 C D



Of course a DBA will still argue that the ROWID is unique and this one simply doesn't belong to a table.

But the takeaway is that a ROWID is not tied to a single row in a single table in a database.