Re: Sorting a column
Date: Wed, 13 Aug 2008 21:26:08 +0200
"joel garry" <joel-garry_at_home.com> schreef in bericht
On Aug 12, 10:55 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "DA Morgan" <damor..._at_psoug.org> schreef in
> > Shakespeare wrote:
> >> "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> >>> Shakespeare wrote:
> >>>> "digory" <dig..._at_gmx.net> schreef in bericht
> >>>>> 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
> >> 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
> So there is still a SLIGHT possibility two tables may have rows with a
> duplicated rowid (by coincidence) after all?
I still don't think so. Look at what you can find out about a rowid:
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.
-- @home.com is bogus. Number one on the McCain ipod list: Dancing Queen by ABBA ======================================================== So I guess this guy is talking crap? (see http://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> ShakespeareReceived on Wed Aug 13 2008 - 14:26:08 CDT