Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE with random order (PL/SQL Developer, 9.2.0.6, Windows)
UPDATE with random order [message #403961] Tue, 19 May 2009 18:39 Go to next message
GBlake09
Messages: 2
Registered: May 2009
Junior Member
Hey everybody.

I've been having a problem lately which some of you may consider as odd. I would like to create randomized versions of tables as I need to work with them later on (so just randomizing a view doesn't suffice).

I'm already able to create a random version of an existing table:

CREATE TABLE table1 (rid NUMBER);
INSERT INTO table1 rid VALUES (1);
INSERT INTO table1 rid VALUES (2);
INSERT INTO table1 rid VALUES (3);
INSERT INTO table1 rid VALUES (4);
INSERT INTO table1 rid VALUES (5);

ALTER TABLE table1 ADD mykey NUMBER;

DECLARE
CURSOR cur_row IS SELECT ROWID FROM table1;
i NUMBER := 1;
BEGIN
FOR rec IN cur_row LOOP
UPDATE table1 SET mykey = i WHERE ROWID = rec.ROWID;
i := i + 1;
END LOOP;
COMMIT;
END;

SELECT * FROM table1 ORDER BY mykey;

- | RID | MYKEY
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5

CREATE TABLE table_ran (ord) AS SELECT rid FROM table1 ORDER BY dbms_random.value;

ALTER TABLE table_ran ADD mykey NUMBER;

DECLARE
CURSOR cur_row IS SELECT ROWID FROM table_ran;
i NUMBER := 1;
BEGIN
FOR rec IN cur_row LOOP
UPDATE table_ran SET mykey = i WHERE ROWID = rec.ROWID;
i := i + 1;
END LOOP;
COMMIT;
END;

SELECT * FROM table_ran ORDER BY mykey;

- | ORD | MYKEY
1 | 2 | 1
2 | 5 | 2
3 | 3 | 3
4 | 4 | 4
5 | 1 | 5

Unfortunately, I need to be able to automatically create lots of those random instances (with other, more complex columns of course). And since I cannot drop and create tables in procedures, I need to be able to rely solely on UPDATE (unless one of you wizards can come with some magic to make it happen for procedures anyway).

I originally thought about something like:

CREATE TABLE table_ran2 (ord NUMBER);
UPDATE table_ran2 SET ord = (SELECT rid FROM (SELECT rid FROM table1 ORDER BY dbms_random.value));

or even

UPDATE table_ran2 r SET r.ord = (SELECT rid FROM (SELECT rid, ROWID ro FROM table1 ORDER BY dbms_random.value) WHERE r.ROWID = ro);

But for some reasons, no rows get updated in either case at all.

SELECT * FROM table_ran2;

- | ORD



What am I doing wrong?

Any help would be greatly appreciated!

- Frank
Re: UPDATE with random order [message #403962 is a reply to message #403961] Tue, 19 May 2009 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I would like to create randomized versions of tables
Why?
How will any software know or determine if rows are "random" or not?
What bad will result if rows are not "random"?
Rows in a table are like balls in a basket & have NO inherent order.
How would you increase the randomness of balls in a basket?

Please answer the questions.

>CREATE TABLE table_ran2 (ord NUMBER);
>UPDATE table_ran2 SET ord = (SELECT rid FROM (SELECT rid FROM table1 ORDER BY dbms_random.value));
What is there to UPDATE in an empty/newly created table?
Without a WHERE clause you would UPDATE every rows in table with same value.

Part of your basic problem is that you do not really understand the concept of ROWID.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3896

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Tue, 19 May 2009 19:24]

Report message to a moderator

Re: UPDATE with random order [message #403966 is a reply to message #403962] Tue, 19 May 2009 21:33 Go to previous messageGo to next message
GBlake09
Messages: 2
Registered: May 2009
Junior Member
>>I would like to create randomized versions of tables
>Why?
Usually the entries would be temporally connected, but in order to test some hypothesis, I need them to be random. And I need them to be tables instead of queries because I have to perform extensive consecutive actions later on which couldn't be done easily in one query.

>How will any software know or determine if rows are "random" or not?
>What bad will result if rows are not "random"?
If it's not random then the program itself would be and worthless (I need it for scientific purposes).
But I assume when using the function dbms_random.value in a query, I indeed receive a selection of rows which would satisfy the attributes of quasi-randomness.

>Rows in a table are like balls in a basket & have NO inherent order.
That's why I would usually order it myself. Even though the physical addresses themselves seem to establish an order. How else would the 'mykey' section of the 'table1' part in my program work otherwise?

>Part of your basic problem is that you do not really understand the concept of ROWID
Granted. ROWID is an alphanumeric address, not a number I could simply compare with other order of rows. I think I meant to use ROWNUM instead. But ROWNUM is predefined also and doesn't get changed upon randomizing the query on a table.

Is there no other way to access that exact order in which the rows are being returned? Or just a way to define an enumeration of the consecutive order in a query which I could then use as quasi-row numbers? The PL/SQL Developer automatically displays an enumeration of the row numbers. I cannot imagine that there is no way to access them somehow.

On a side-note. I assume the 'table_ran' part works because ROWNUM gets defined at the creation, thus already incorporating the attributes of the query after which the table was created?

Btw, thanks for your quick reply BlackSwan.
Re: UPDATE with random order [message #403968 is a reply to message #403961] Tue, 19 May 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>. I think I meant to use ROWNUM instead.
The behaviour or ROWNUM will likely surprise you too.

>Is there no other way to access that exact order in which the rows are being returned?
ORDER BY clause is the only way to control the "order" in which rows get returned.


>Or just a way to define an enumeration of the consecutive order in a query which I could then use as quasi-row numbers?
I see the words, but I won't try to guess at what you mean.

"random rows" are somewhat an orthogonal concept within RDBMS.

I don't know if the following will assist but SELECT can contain the optional SAMPLE clause.
Re: UPDATE with random order [message #404000 is a reply to message #403961] Wed, 20 May 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to BlackSwan answers, you can go from table1 to table_tan in one step:
SQL> CREATE TABLE table1 (rid NUMBER);

Table created.

SQL> INSERT INTO table1 rid VALUES (1);

1 row created.

SQL> INSERT INTO table1 rid VALUES (2);

1 row created.

SQL> INSERT INTO table1 rid VALUES (3);

1 row created.

SQL> INSERT INTO table1 rid VALUES (4);

1 row created.

SQL> INSERT INTO table1 rid VALUES (5);

1 row created.

SQL> create table table_ran (ord, mykey) as
  2  select rid, row_number() over(order by dbms_random.value)
  3  from table1
  4  /

Table created.

SQL> select * from table_ran ;
       ORD      MYKEY
---------- ----------
         1          1
         3          2
         5          3
         2          4
         4          5

5 rows selected.

How to format a post is detailed in OraFAQ Forum Guide, please read it.

Regards
Michel

[Updated on: Wed, 20 May 2009 00:57]

Report message to a moderator

Re: UPDATE with random order [message #404153 is a reply to message #403961] Wed, 20 May 2009 11:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think your best bet would be to create a copy of your original table, with an ORDER BY dbms_random.value, and then either drop the original table and rename the copy, or truncate the original and re-insert the data.
-- Setup data                            
create table test_205  (col_1 number);

insert into test_205 (select level from dual connect by level <= 100);

-- Create copy
create table test_205_copy as select * from test_205 order by dbms_random.value;

--And then either:
drop table test_205;

rename test_205_copy to test_205;

-- OR

truncate table test_205;

insert into test_205 select * from test_205_copy;


Hop this helps.
Previous Topic: Triggers
Next Topic: how to maintain concurrency during process
Goto Forum:
  


Current Time: Mon Dec 05 04:56:20 CST 2016

Total time taken to generate the page: 0.06452 seconds