| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unique(ish) sequence question
Try this two ideas and tellme if this improved please,
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
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
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.
 |
 |