Friday, September 07, 2007

Oracle and RowID

Someone in class today asked why you would ever use Rowid. If you ever needed the speed which rowid grants you, then shouldn't you have an index on that column?
Now, the fact that indexes don't always help aside, my response was that rowid is useful in very large tables.
This is not actually the answer I meant to give.
What I have found it really useful for is deduping, especially in ETL situations.
If, somehow, you've loaded data in a way that has dupes (or perhaps the data itself has dupes) and you didn't assign a sequence to each record, then you can end up in a very bad place.
row1: Joel,Smith, 617-862-5147
row2: Joel,Smith, 617-862-5147
There is no way to tell one row from another, so getting rid of a dupe is difficult, if not out-and-out impossible.
Unless you use rowid.
Each of the rows above has a unique rowid, and that can be used to select a specific row, regardless of the data (no, you can't use rownum)
Yes, this is reasonably basic, but it is worth keeping in mind.
It isn't a speed issue, its an identity issue.


1 comment:

Sandeep said...

i do agree on your point. ; if you want to discuss on oracle shoot an email; i am just trying to learn...