Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Are rowids unique among tables?

Re: Are rowids unique among tables?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 7 Oct 2000 10:03:18 +0100
Message-ID: <970911641.26929.1.nnrp-04.9e984b29@news.demon.co.uk>

David is correct.
The 'restricted rowid' as stored in most indexes is still a 6-byte: 'file id , block id , row array subscript'.

However, the 'file id' has changed from the absolute file id to the 'relative' file id within tablespace. For reasons of backward compatibility, this is not easy to spot, as Oracle defaults to cheat when deciding which 'relative' file id to use so that for the first 1024 files, the relative file id appears to be the absolute file id.

e.g. in the default install, you get 5 tablespaces and five files. You might expect tablespace 5 to be made from file 5, but have 'relative file' 1 as this is the first file of that tablespace. However, Oracle skips the relative file numbers 1-4, and will eventually use them if you add a further 1024 files to that tablespace.

When searching for a row after acquiring a rowid, Oracle is (usually) aware of the object (data segment) that it needs to search from the parse time information. The object's id tells Oracle the object's tablespace, and the object's tablespace combined with the relative file number tells Oracle the absolute file.

There are a few cases where this algorithm cannot work, and only in these cases (such as global indexes on partitioned tables) does oracle store the 'extended' rowid in the index - the extended rowid includes the target object's permanent id as an extra 4 bytes.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Barbara Kennedy wrote in message ...

>I don't believe you are correct. We are talking rowid not object_id.
>Object_id is unique, but rowid is unique within a tablespace. Since The
>realative file number is unique within a tablespace the file number is
>assumed and not actually part of the rowid (it was in version 7). So they
>made this change so they could have something like 1024 files in a
>tablespace and 1024 tablespaces which together with the largest block size
>etc. somehow gets you 50 petabytes. I remember this question coming up
>during a beta training meeting at Oracle on Oracle 8. (I was with a
company
>that was a beta tester for Oracle 8 so we got a preview of the training
with
>the instructor's notes.)
>Jim
><oratune_at_aol.com> wrote in message news:8rkosm$b08$1_at_nnrp1.deja.com...
>> In article <%8lD5.20828$XV.1132383_at_nntp3.onemain.com>,
>> "Barbara Kennedy" <barbken_at_teleport.com> wrote:
>> > Actually in Oracle 8 rowid is unique within a tablespace. It is
possible to
>> > have the same rowid in different tablespaces. This is a change from
Oracle
>> > 7 and why the rowid changed. This is so they could have adatabase up
to a
>> > total of 50 petabytes in size.
>> > Jim
>> > <pku12345_at_my-deja.com> wrote in message news:8rj6a2
$3nu$1_at_nnrp1.deja.com...
>> > > By definition, a rowid identifies the storage location of a row
within
>> > > a datafile/extent/block. Since no two rows (from the same table or
>> > > otherwise) can physically occupy the same space, rowids are unique
>> > > through out a given database. I don't know why your last query
didn't
>> > > work though -- curious....
>> > >
>> > > Pei Ku
>> > >
>> > > In article <8rj4am$28m$1_at_nnrp1.deja.com>,
>> > > kal121_at_my-deja.com wrote:
>> > > > I know that rowids are unique within a given table, but are they
unique
>> > > > among tables? I tried to verify this myself, but Oracle wouldn't
let me.
>> > > >
>> > > > (works ok)
>> > > >
>> > > > SQL> select rowid from tab1;
>> > > >
>> > > > ROWID
>> > > > ------------------
>> > > > AAAA5bAAHAAAAFrAAA
>> > > > AAAA5bAAHAAAAFrAAB
>> > > >
>> > > > (does not work)
>> > > >
>> > > > SQL> select rowid from tab1 where rowid in (select rowid from
tab2);
>> > > >
>> > > > select rowid from tab1 where rowid in (select rowid from tab2)
>> > > > *
>> > > > ERROR at line 1:
>> > > > ORA-01410: invalid ROWID
>> > > >
>> > > > Sent via Deja.com http://www.deja.com/
>> > > > Before you buy.
>> > > >
>> > >
>> > >
>> > > Sent via Deja.com http://www.deja.com/
>> > > Before you buy.
>> >
>> >
>>
>> Unfortunately that is not quite correct; the relative file number is
>> unique within a tablespace so the relative file number can be
>> duplicated throughout a database between tablespaces, however the
>> object_id (data object number in the extended rowid) is unique
>> throughout the database, thus making each and every ROWID unique
>> throughout.
>>
>> --
>> David Fitzjarrell
>> Oracle Certified DBA
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>
Received on Sat Oct 07 2000 - 04:03:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US