 | |
[Q] Sorting a column
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#91
 
| |||
| |||
|
Re: Sorting a column -
08-14-2008
, 02:58 PM
|
On Aug 13, 12:26 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "joel garry" <joel-ga... (AT) home (DOT) com> schreef in berichtnews:8ab2e84c-704f-48f9-8479-46218e64d68e (AT) r35g2000prm (DOT) googlegroups.com... On Aug 12, 10:55 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in berichtnews:1218586746.595453 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in bericht news:1218223583.133026 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "digory" <dig... (AT) gmx (DOT) net> schreef in bericht news:1210a149-d43e-48a8-bbd7-9a688c96fa88 (AT) y38g2000hsy (DOT) 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...@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...@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. |
have been a bit unclear about the two different tables...
Quote:
|
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/B...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/uniont...3cyberwar.html |
#92
| |||
| |||
|
|
On Aug 13, 12:26 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "joel garry" <joel-ga... (AT) home (DOT) com> schreef in berichtnews:8ab2e84c-704f-48f9-8479-46218e64d68e (AT) r35g2000prm (DOT) googlegroups.com... On Aug 12, 10:55 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in berichtnews:1218586746.595453 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in bericht news:1218223583.133026 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "digory" <dig... (AT) gmx (DOT) net> schreef in bericht news:1210a149-d43e-48a8-bbd7-9a688c96fa88 (AT) y38g2000hsy (DOT) 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...@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...@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. |
have been a bit unclear about the two different tables...
Quote:
|
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/B...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/uniont...3cyberwar.html |
#93
| |||
| |||
|
|
On Aug 13, 12:26 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "joel garry" <joel-ga... (AT) home (DOT) com> schreef in berichtnews:8ab2e84c-704f-48f9-8479-46218e64d68e (AT) r35g2000prm (DOT) googlegroups.com... On Aug 12, 10:55 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in berichtnews:1218586746.595453 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "DA Morgan" <damor... (AT) psoug (DOT) org> schreef in bericht news:1218223583.133026 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: "digory" <dig... (AT) gmx (DOT) net> schreef in bericht news:1210a149-d43e-48a8-bbd7-9a688c96fa88 (AT) y38g2000hsy (DOT) 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...@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...@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. |
have been a bit unclear about the two different tables...
Quote:
|
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/B...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/uniont...3cyberwar.html |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.