Re: Unique(ish) sequence question

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sat, 21 Feb 2004 01:21:49 +0100
Message-ID: <02d001c3f810$b3e52550$2600000a@JARAWIN>

Hi,

the analytic function you seek is dense_rank
if you intend to update the whole table, the best update is CTAS, drop and RENAME.

SQL> select col1, col2,
2 dense_rank() over(order by col1, col2) as i 3 from y
4 ;

COL1       COL2                I
---------- ---------- ----------
a          a                   1
a          a                   1
a          a                   1
b          a                   2
c          c                   3
c          c                   3

6 Zeilen ausgewählt.

SQL> using RANK() only, you get also unique numbers but with gaps

create table y_new as
select col1, col2,
dense_rank() over(order by col1, col2) as i from y;

drop table y;

rename y_new to y1;

The create table statemens makes only one full scan with a sorting window:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY) 2 ;

PLAN_TABLE_OUTPUT

| 0 | CREATE TABLE STATEMENT | | 6 | 12 | 4 |
| 1 | LOAD AS SELECT | | | | |
| 2 | WINDOW SORT | | 6 | 12 | 4 |
| 3 | TABLE ACCESS FULL | Y | 6 | 12 | 2 |

You may also use some more sophisticated technique for switch the new and old tables if drop and rename is not desired (e.g. exchange partition)

hopefully will be this information usefull for you,

DB Nemec

Original Message ----- From: "Lord David" <DLord_at_ironmountain.co.uk> To: <oracle-l_at_freelists.org> Sent: Friday, February 20, 2004 9:58 AM Subject: Unique(ish) sequence question

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
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 18:21:05 CST