List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 8 2003 7:39am
Subject:Re: ROWID
View as plain text  
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
ROWIDWenhua Wan6 Nov
  • Re: ROWIDJeremy Zawodny6 Nov
    • RE: ROWIDMike Brando6 Nov
      • RE: ROWIDDathan Vance Pattishall6 Nov
      • Re: ROWIDChris Boget6 Nov
      • RE: ROWIDDaniel Kiss6 Nov
      • Re: ROWIDJeremy Zawodny8 Nov
        • Re: ROWIDDan Nelson8 Nov
    • Re: ROWIDPatrick Sherrill8 Nov