Re: Unique(ish) sequence question
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Feb 2004 08:52:05 -0400
Try this two ideas and tellme if this improved please,
1) this helpme in some updates
where several rows had the proper value
for rec in (select distinct x, y, z from blah) loop
update blah set id = i where x = rec.x and y = rec.y and z = rec.z
AND NOT ID=I
end loop;
2)
definetively if you are doing a SELECT UNIQUE x, y, z and index could help
because you are sorting data and after searching that records, try creating
an index on x,y,z
----- Original Message -----
From: "Lord David" <DLord@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 20, 2004 4:58 AM
Subject: Unique(ish) sequence question
Hi
I need to add a sequence number to a table that is unique for *distinct*
rows. I can generate the number like this: -
select rownum, x, y, z from (select distinct x, y, z from blah);
I could do it in plsql a bit like this (in practice I would have to use
dbms_sql as both the table name and the list of columns is generated
dynamically): -
i := 1
for rec in (select distinct x, y, z from blah) loop
update blah set id = i where x = rec.x and y = rec.y and z = rec.z
i := i + 1
end loop;
The main problem is the expense of the update (a full table scan per value
of i). The tables are basically uploads from spreadsheets, so the list of
columns may be quite long (10-100) and each table is only likely to be used
a few times. Hence, I cannot see that there is any point in adding indexes.
Any ideas would be greatly appreciated.
--
David Lord
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
This e-mail and its attachments are intended for the
author's addressee only and may be confidential.
If they have come to you in error you must take no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure
communications medium. We advise that you
understand and observe this lack of security when
e-mailing us. Steps have been taken to ensure this
e-mail and attachments are free from any virus, but
advise the recipient to ensure they are actually virus
free.
The views, opinions and judgments expressed in this
message are solely those of the author. The message
contents have not been reviewed or approved by Iron
Mountain.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
