SEO

#1 (**permalink**) 04-19-2008, 08:37 PM

Hi,

does anyone have some background information
about the ROWID column?

Can I used it as primary key
for temporary result sets on the client side?
Why are the nummbers not successive?

TIA
Joachim Engel

#2 (**permalink**) 04-19-2008, 08:37 PM

Joachim Engel wrote:
> Hi,
>
> does anyone have some background information
> about the ROWID column?
>
> Can I used it as primary key
> for temporary result sets on the client side?
> Why are the nummbers not successive?
>
> TIA
> Joachim Engel
>
>
The manual has a very good description about it: Don't use it! Don't rely on it! It's an internal engine id.
It's not successive because the inserts aren't necesseraly successive.

And then there are fragmented tables... the rowid is only guaranteed to be unique within a fragment... unless you use the "WITH ROWIDS" clause...

Regarding IN PLACE ALTER TABLE, when you do an update in a table wich has a pending in place alter table the row ROWID may change (the row may be re-allocated).

So, forget about it... This is my personal advice and is more or less the official advice.

Regards.

#3 (**permalink**) 04-19-2008, 08:37 PM

"Joachim Engel" <joachim.engel@engel-edv.de> wrote in message news:<c21rd3$tv7$06$1@news.t-online.com>...
> Hi,
>
> does anyone have some background information
> about the ROWID column?
>
> Can I used it as primary key
> for temporary result sets on the client side?
> Why are the nummbers not successive?
>
> TIA
> Joachim Engel

The rowid is a pseudo column with describes the logical address of the
row with respect to the tablespace. It consists of three bytes for
the relative page number and one byte for the slot number within the
page.

No - since it is not a real column, you can not build an index on it
and thus can not use it as a primary key.

The rowid is not consecutive because the incremental value of it
depends on how many rows are currently on a page and how many rows can
physically fit on a page.

You should not consider the rowid as persistant. It is possible for
the rowid for a row to change, as in the case of an inplace alter or
reclustering of an index column.

#4 (**permalink**) 04-19-2008, 08:37 PM

Hi,

thank you very much for your informative answers.

Never fear!
We don't want use ROWID as primary key
for manipulations on the database.
We just need a unique id for each row
in our client result set in a grid or in memory.
No any other use! <g>

Kind regards
Joachim Engel

#5 (**permalink**) 04-19-2008, 08:38 PM

Joachim Engel wrote:
> Hi,
>
> thank you very much for your informative answers.
>
> Never fear!
> We don't want use ROWID as primary key
> for manipulations on the database.
> We just need a unique id for each row
> in our client result set in a grid or in memory.
> No any other use! <g>
>
> Kind regards
> Joachim Engel
>
>
Then to reiterate what has already been said:

* Do not use the ROWID
* If you really want to use it be aware that it changes
* whenever a row moves on disk - alter table, update of varchar, ...
* be aware that it is not unique in fragmented tables (unless WITH ROWID
is specified)

I'm not proud but I've successfully used them.

--
Rob Wilson
rob_wilson_at_ameritech.net (replace _at_ with @)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Description of ROWID