RE: Unique(ish) sequence question
- From: Lord David <DLord@xxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 09:54:20 -0000
I'm not sure how you would do that. Would it be any easier than doing it in
oracle?
I'm thinking whether selecting all rows, sorting them on all columns and
then comparing the current row with the last would help. The fts, sort and
comparison of each column would be no different to the distinct query but at
least I would be able to return the rowid to do the update.
--
David Lord
> -----Original Message-----
> From: Niall Litchfield [mailto:n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx]
> Sent: 20 February 2004 09:34
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Unique(ish) sequence question
>
>
> Why not generate it in the spreadsheet?=20
>
> > -----Original Message-----
> > From: DLord@xxxxxxxxxxxxxxxxxx=20
> > Sent: 20 February 2004 08:58
> > To: DLord@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> > Subject: Unique(ish) sequence question
> >=20
> >=20
> > Hi
> >=20
> > I need to add a sequence number to a table that is unique for=20
> > *distinct*
> > rows. I can generate the number like this: -
> >=20
> > select rownum, x, y, z from (select distinct x, y, z from blah);
> >=20
> > I could do it in plsql a bit like this (in practice I would=20
> > have to use
> > dbms_sql as both the table name and the list of columns is generated
> > dynamically): -
> >=20
> > i :=3D 1
> > for rec in (select distinct x, y, z from blah) loop
> > update blah set id =3D i where x =3D rec.x and y =3D
> rec.y and z =3D =
> rec.z
> > i :=3D i + 1
> > end loop;
> >=20
> > The main problem is the expense of the update (a full table=20
> > scan per value
> > of i). The tables are basically uploads from spreadsheets,=20
> > so the list of
> > columns may be quite long (10-100) and each table is only=20
> > likely to be used
> > a few times. Hence, I cannot see that there is any point in=20
> > adding indexes.
> >=20
> > Any ideas would be greatly appreciated.
> >=20
> > --
> > David Lord=20
> >=20
> >=20
> >=20
> > *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***=20
> > *** *** ***=20
> > This e-mail and its attachments are intended for the
> > author's addressee only and may be confidential.=20
> >=20
> > If they have come to you in error you must take no=20
> > action based on them, nor must you copy or show=20
> > them to anyone; please reply to this e-mail and =20
> > highlight the error.=20
> >=20
> > Please note that this e-mail has been created in the
> > knowledge that Internet e-mail is not a 100% secure=20
> > communications medium. We advise that you=20
> > understand and observe this lack of security when=20
> > e-mailing us. Steps have been taken to ensure this=20
> > e-mail and attachments are free from any virus, but=20
> > advise the recipient to ensure they are actually virus=20
> > free.=20
> >=20
> > The views, opinions and judgments expressed in this=20
> > message are solely those of the author. The message=20
> > contents have not been reviewed or approved by Iron=20
> > Mountain.
> >=20
> > *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***=20
> > *** *** ***=20
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >=20
> >=20
>
>
>
> **********************************************************************
> This email contains information intended for
> the addressee only. It may be confidential
> and may be the subject of legal and/or
> professional privilege. Any dissemination,
> distribution, copyright or use of this
> communication without prior permission of
> the sender is strictly prohibited.
> **********************************************************************
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> This email and its attachments are confidential under
> applicable law and are intended for use of the sender's
> addressee only, unless the sender expressly agrees otherwise,
> or unless a separate written agreement exists between Iron
> Mountain and a recipient company governing communications
> between the parties and any data that may be so transmitted.
> Transmission of email over the Internet is not a secure
> communications medium. If you are requesting or have
> requested the transmittal of personal data, as defined in
> applicable privacy laws, by means of email or in an
> attachment to email, you may wish to select a more secure
> alternate means of transmittal that better supports your
> obligations to protect such personal data.
>
> If the recipient of this message is not the recipient named
> above, and/or you have received this email in error, you must
> take no action based on the information in this email. You
> are hereby notified that any dissemination, misuse or copying
> or disclosure of this communication by a recipient who has
> received this message in error is strictly prohibited. If
> this message is received in error, please return this email
> to the sender and immediately highlight any error in
> transmittal. Thank you.
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: