Re: Sorting a column

On Aug 13, 12:26 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "joel garry" <joel-ga..._at_home.com> schreef in berichtnews:8ab2e84c-704f-48f9-8479-46218e64d68e_at_r35g2000prm.googlegroups.com...
> On Aug 12, 10:55 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
>
>
>
>
> > "DA Morgan" <damor..._at_psoug.org> schreef in
> > berichtnews:1218586746.595453_at_bubbleator.drizzle.com...
>
> > > Shakespeare wrote:
> > >> "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> > >>news:1218223583.133026_at_bubbleator.drizzle.com...
> > >>> Shakespeare wrote:
> > >>>> "digory" <dig..._at_gmx.net> schreef in bericht
> > >>>>news:1210a149-d43e-48a8-bbd7-9a688c96fa88_at_y38g2000hsy.googlegroups.com...
> > >>>>> Hi
>
> > >>>>> I have a table T with two columns NAME (VARCHAR2) and POS (NUMBER).
> > >>>>> I
> > >>>>> want to write an UPDATE query, which updates the column POS such
> > >>>>> that
> > >>>>> its values correspond to the alphabetical order of NAME.
>
> > >>>>> UPDATE
> > >>>>> T t1
> > >>>>> SET pos = (SELECT ROWID FROM T t2 WHERE t1.NAME = t2.NAME ORDER BY
> > >>>>> NAME)
>
> > >>>>> That does not work, because the WHERE clause returns a single row,
> > >>>>> which will always have a ROWID of 1.
>
> > >>>>> How do I do this? (It's possible with a PROCEDURE, of course, but I
> > >>>>> want to avoid them.)
> > >>>> ROWID? Don't you mean rownum?
> > >>>> I don't think tables will ever have rows with the same rowid (except
> > >>>> by
> > >>>> coincidence)
>
> > >>>> Shakespeare
> > >>> Not even by coincidence ... it is a technical impossibility.
> > >>> --
> > >>> Daniel A. Morgan
> > >>> Oracle Ace Director & Instructor
> > >>> University of Washington
> > >>> damor..._at_x.washington.edu (replace x with u to respond)
> > >>> Puget Sound Oracle Users Group
> > >>>www.psoug.org
>
> > >> Just by curiousity (and too lazy to look in the manuals...): does
> > >> Oracle
> > >> change ROWID's when a transportable tablespace is moved from one system
> > >> to an other?
>
> > >> Shakespeare
>
> > > I can't check it right now but it seems to me it would have no choice
> > > if it found a conflict. Whether it checks or just changes them I can
> > > not say without looking.
> > > --
> > > Daniel A. Morgan
> > > Oracle Ace Director & Instructor
> > > University of Washington
> > > damor..._at_x.washington.edu (replace x with u to respond)
> > > Puget Sound Oracle Users Group
> > >www.psoug.org
>
> > So there is still a SLIGHT possibility two tables may have rows with a
> > duplicated rowid (by coincidence) after all?
>
> > Shakespeare
>
> I still don't think so. Look at what you can find out about a rowid:
>
> DBMS_ROWID.ROWID_INFO (
> rowid_in IN ROWID,
> ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE',
> rowid_type OUT NUMBER,
> object_number OUT NUMBER,
> relative_fno OUT NUMBER,
> block_number OUT NUMBER,
> row_number OUT NUMBER);
>
> All those OUT's would have to be the same for a duplication. How
> could you have a table in two different tablespaces? desc
> dba_tables. The relative file number makes it unique within that
> tablespace.
>
> Another clue may be found inhttp://www.jlcomp.demon.co.uk/book_8i/ch_08.html#Complex%20Transporta...
> . Unless there is some way to fool Oracle into importing various
> files from different same-named tablespaces at different times... but
> then, you could use an editor on the datafiles when Oracle is shut
> down if you are that devious, no need to bother with transport at
> all. I wonder what happens when you try to rebuild the index.
>
> jg
> --
> @home.com is bogus.
> Number one on the McCain ipod list: Dancing Queen by ABBA
>
> ========================================================
> So I guess this guy is talking crap?
> (seehttp://www.adp-gmbh.ch/ora/concepts/rowid.html)
> <quote>
> A rowid identifies a row in a table
> A rowid is a pseudo column (like versions_xid), that uniquely identifies a
> row within a table, but not within a database. It is possible for two rows
> of two different tables stored in the same cluster to have the same rowid.
>
> <unquote>
>
> Shakespeare

I'm sorry, I misread your earlier post, missed the part about two tables. I'm not sure why it matters if different tables have the same rowid? Since O8, the extended rowid has the object number.

Of course, the concepts manual defines a rowid as a globally unique identifier in the database, while the part about physical rowid's mentions that two tables in the same block of a cluster can have the same rowid. I suspect that is only true with restricted rowid's (read: Oracle7), or maybe it just means the two tables share the same columns, but we'll just have to wait for someone to actually try it to see. See http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3921

jg

--
@home.com is bogus.
If the New York Times splashed across their front page about the first
cyberattack that coincided with a shooting war, would anyone care?
http://www.signonsandiego.com/uniontrib/20080813/news_1n13cyberwar.html