You can use the analytical function to build a single SQL for update
and they blaze. Here is my example:
DROP TABLE temp;
DROP SEQUENCE temp_seq;
CREATE SEQUENCE temp_seq CACHE 10;
CREATE TABLE temp (id INTEGER, distinct_row_id integer, x
varchar2(10), y varchar2(10), z varchar2(10),
CONSTRAINT temp_pk PRIMARY KEY(id));
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'1','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'1','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'2','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'3','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'3','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'1','2','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'1','2','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'1','2','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'4','1','1');
insert into temp(id,x,y,z) values (temp_seq.NEXTVAL,'4','1','1');
COMMIT;
SELECT *
FROM temp;
ID DISTINCT_ROW_ID X Y Z
1 1 1 1
2 1 1 1
3 2 1 1
4 3 1 1
5 3 1 1
6 1 2 1
7 1 2 1
8 1 2 1
9 4 1 1
10 4 1 1
UPDATE temp
SET distinct_row_id = (
SELECT row_num
FROM (
SELECT x,y,z, row_number() OVER(order by x,y,z)
row_num
FROM temp
GROUP BY x,y,z
) temp_in
WHERE temp.x = temp_in.x
AND temp.y = temp_in.y
AND temp.z = temp_in.z
);
COMMIT;
SELECT *
FROM temp;
ID DISTINCT_ROW_ID X Y Z
1 1 1 1 1
2 1 1 1 1
3 3 2 1 1
4 4 3 1 1
5 4 3 1 1
6 2 1 2 1
7 2 1 2 1
8 2 1 2 1
9 5 4 1 1
10 5 4 1 1
- Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com> wrote:
> I don't think but you can analyze if is possible to use "bulk
> collect" in
> your update
> ( see pl/sql user and reference guide)
> ----- Original Message -----
> From: "Lord David" <DLord_at_ironmountain.co.uk>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
sundeep_maini_k_at_cat.com
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Feb 20 2004 - 16:53:10 CST
