RE: Unique(ish) sequence question
- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 09:33:44 +0000
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
-----------------------------------------------------------------
Other related posts:
