| 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
David,
Here's an ugly solution using row_number(). I'm not sure it's any better than what you already have.
SQL> select * from mike;
COL1 COL2 KEY
---------- ---------- ----------
a a a a a a b b c c c c
6 rows selected.
update mike b
set key = (select distinct a.key
from (select col1, col2,
row_number()
over (order by col1, col2) key
from (select distinct col1, col2 from mike)) a
where a.col1 = b.col1
and a.col2 = b.col2)
where exists (select 1
from (select col1, col2,
row_number()
over (order by col1, col2) key
from (select distinct col1, col2 from mike)) a
where a.col1 = b.col1
and a.col2 = b.col2);
SQL> select * from mike;
COL1 COL2 KEY
---------- ---------- ----------
a a 1 a a 1 a a 1 b b 2 c c 3 c c 3
6 rows selected.
With no indexes, AUTOTRACE shows 3 full scans:
Execution Plan
0 UPDATE STATEMENT Optimizer=CHOOSE 1 0 UPDATE OF 'MIKE'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MIKE'
4 2 VIEW
5 4 VIEW
6 5 SORT (UNIQUE)
7 6 TABLE ACCESS (FULL) OF 'MIKE'
8 1 SORT (UNIQUE)
9 8 VIEW
10 9 WINDOW (BUFFER)
11 10 VIEW
12 11 SORT (UNIQUE)
13 12 TABLE ACCESS (FULL) OF 'MIKE'
Mike
Lord David wrote:
> 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
> -----------------------------------------------------------------
-- 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 - 14:27:39 CST
 |
 |