Re: Sorting a column

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 in
http://www.jlcomp.demon.co.uk/book_8i/ch_08.html#Complex%20Transportable%20tablespaces . 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