From: Dan Nelson
Date: November 8 2003 7:39am
Subject: Re: ROWID
List-Archive: http://lists.mysql.com/mysql/153359
Message-Id: <20031108073922.GA36548@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Nov 07), Jeremy Zawodny said:
> > In Oracle for example, a ROWID is the unique address of a row in
> > the database. Every row, unique key or not has a unique address. Is
> > there such a thing in MySQL?
> 
> No.  If there was it'd be documented.  But at least with MyISAM
> tables, MySQL cannot guarantee the "address" (or row number) of a
> row.  An ALTER/REPAIR/OPTIMIZE may reorder the rows.

A ROWID is actually a packed representation of datafile, segment, block
and row, so anything that rebuilds a table in Oracle changes ROWIDs
too.  They're not meant to be a permanent pointer.  MySQL could
probably do the same thing by returning a 4 or 8-byte offset into the
.MYI file for MyISAM tables.  You would have to return the primary key
for BDB tables, and I don't know enough about InnoDB's layout to guess. 
Basically, whatever pointer non-primary indexes use to locate records
can be called a ROWID.
 
> > ROWIDs are extremely useful for guaranteeing that you are
> > manipulating the exact row that you think you are.
> 
> So I've been told.

They've got an O(1) access time, so they're also useful for remembering
sets of rows in a multi-query operation; pull the ROWIDs once, then use
"WHERE ROWID in ( rowidlist )" later.

-- 
	Dan Nelson
	dnelson@stripped