Assign number value circularly in PL/SQL

[tmf33uk]

Hi!

I have a table with an id column which I would like to update with values 1 to n. Value n is to be defined each time the script is run.

I´m using the MOD function, but it´s not working as desired. If I set NUMBER_SCRIPTS=3 most of the rows are assigned ID=3. Should be assigned in the order of size by bytes (see cursor) as 1,2,3,1,2,3,1,2,3,etc. and if I set define NUMBER_SCRIPTS to 5, for example, the rows in descending order by size should be assigned 1,2,3,4,5,1,2,3,4,5,1,2,etc.

Please let me know if the objective is not clear and I´ll explain with more "real" examples.

Thanks in advance for your help. I have found this forum to be most valuable in the past and I am most grateful for it.

The relevant part of the script is as follows:

DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;

CURSOR CONTROL IS
SELECT ilv.*, ROWNUM
FROM ( SELECT SEGMENT_NAME, SEGMENT_TYPE
FROM REPLICA_CONTROL_TABLE
WHERE SEGMENT_TYPE ='MVIEW'
AND ACCION='REFRESH'
ORDER BY BYTES DESC
) ilv
;

FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(ROWNUM, N_SCRIPT)+1;
commit;
END LOOP;

It seems like the MOD function is not working as expected. Is there a better way to do this?

Regards,
Teri.

 

[SantaMufasa]

It seems like the MOD function is not working as expected.

Frankly, Teri, I can't believe that your code, above, is even working at all. Your syntax contains multiple errors (thus causing me to believe that your code isn't actually doing anything yet). And once you get past the syntax issues, your logic is problematic.


Syntax issues:[ul][li]You are missing a BEGIN header (following your CURSOR definition) to separate your DECLARE section from your procedural section.[/li][li]You cannot say "UPDATE TABLE...". A valid table name (not "TABLE") must follow the verb "UPDATE".[/li][/ul]Logic issues:[ul][li]All your code is doing is setting the value of a column ("ID") to a numeric value derived from a CURSOR...For that, you do not need PL/SQL...A simple UPDATE statement (once you identify what table you need to update) will do.[/li][li]Your PL/SQL code never refers to any of the columns in your CURSOR, so why do you SELECT those columns, or even have the CURSOR?[/li][li]Every table from which you SELECT or UPDATE or DELETE has its own ROWNUM function. Therefore, when you refer to ROWNUM as you have in your UPDATE statement, it does not refer to the ROWNUM you SELECT in your CURSOR definition. If you want to refer to the CURSOR's ROWNUM, then you should give ROWNUM an alias (e.g. "RN") and then qualify the alias with the CURSOR name (e.g. "R_CONTROL.RN") when you reference it.[/li][li]You have no WHERE clause in your UPDATE statement. Therefore, every single row in the table you wish to UPDATE gets updated, every single time through your loop. If you have 10,000 rows in the table you are trying to UPDATE, and if you have 1,000 rows in your CURSOR, then you are doing 10 million UPDATES!...and the value that ends up in those 10,000 rows is only the value of the 1,000th update.[/li][li]And the biggest problem of all...there is nothing that correlates the rows in the table you are updating with the rows in the CURSOR. Therefore, since I cannot discern your correlation logic/condition, I cannot even suggest much simpler SQL-only code that does the update you want.[/li][/ul]If you can resolve the above issues and post the resulting code, then I'm certain that we can offer a single-command, SQL-only alternative to do what you want.



Looking forward to your corrections,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
It is probably ROWNUM that is not working...
I believe you need to have a ROWNUM value in your SubQuery and select it in the main query - Try modifying the query to:

CURSOR CONTROL IS
    SELECT  ilv.*, Rnum
    FROM    (   SELECT ROWNUM Rnum, SEGMENT_NAME, SEGMENT_TYPE
                FROM    REPLICA_CONTROL_TABLE
                WHERE   SEGMENT_TYPE ='MVIEW'
                AND     ACCION='REFRESH'
                ORDER BY BYTES DESC
             ) ilv
   ;



FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(Rnum, N_SCRIPT)+1;
commit;
END LOOP;

[/quote]
Here is probably why:

To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Santa first again - I should have known...
He'll probably answer your identical post in the 10 forum.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

I'm simply amazed that you were awake at 6:00 a.m. (British Summer Time) to be responding to Teri's even earlier original post!...You're an "Early Bird" and I'm a "Night Owl", so between the two of us, we probably have the Oracle Globe covered for most of the day (and night). Plus, everyone knows that it's nearly impossible to anticipate simultaneous/cross-posts, so all I can say is "Good-onya for your vigilance."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Just a time zone oddity...Here in Arizona we do not have Daylight Savings Time ( like British Summer Time)so it wasn't all that early ( actually late at night) ..

For Instance, it is 8:17 AM when I am posting this.
( As a personal note: Great to have you up and responding)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Thanks, TBear, for your kind wishes ! For some reason, I thought that you were located across The Pond...Sorry for my geographical haze.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[tmf33uk]

Thanks Santa and Turkbear, you definitely have the Oracle globe covered between the two of you.. ;o)

Santa, I didn´t put the whole script because it does a few other things also. I was narrowing the problem down to where it doesn´t function as expected.

My script most definitely has a BEGIN header after the DECLARE secction. Still, you make some good points that I need to check once I´ve slept a little. I´ve had to wrok all night and I can´t see out of my eyes anymore.. /

Thanks to both of you!

T.

 

[ChrisHunt]

Using a cursor, rownum and MOD() seems a bit cumbersome. Why not just use an Oracle sequence?

CREATE SEQUENCE my_seq
  START WITH 1
  MINVALUE 1
  MAXVALUE 3
  CYCLE;

UPDATE my_table
SET id = my_seq.nextval;

DROP SEQUENCE my_seq;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[tmf33uk]

Hi ChrisHunt,

your suggestion sounds neater than the MOD function. I´m going to try it shortly. But, I don´t think I´ll be able to do without the cursor as I need to update row by row with the next number...

I like it though. Will update you when I´ve tried it.

Thanks!
T.

 

[ChrisHunt]

I need to update row by row with the next number

That's exactly what my solution will do, though it probably doesn't look that way if you're unfamiliar with sequences.

Essentially, [tt]UPDATE my_table SET id = my_seq.nextval[/tt] says "update the id column, pulling a new value from the sequence for each row of the table".

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[tmf33uk]

Ah, I see. However, it is not all rows I want to be given a value from 1 to n. And those that are to be given a value from the sequence need to be assigned one in a particular order, not as they appear in the table.

In my case, the table contains, among other details about materialized views in the database, which ones need to be refreshed. I want to refresh them in n number of sessions so that the work is spread more or less equally between n scripts/sessions. n could be 3, but it might change in the future. So the idea is for the cursor to order those rows in the table by bytes, starting with the largest.

So if I have 6 large (L) mviews to refresh, 6 medium (M) ones and 6 small (S) ones, they should be more or less equally spread between the n refresh scripts, starting with the largest ones.

For example, f I run n=3 scripts to refresh them they would be spread as follows:

Script 1: L1, L4, M1, M4, S1, S4
Script 2: L2, L5, M2, M5, S2, S5
Script 3: L3, L6, M3, M6, S3, S6

Sorry if my explanation was a little mysterious before. /

T.

 

[SantaMufasa]

Ah, yes...it's interesting how much easier it is once we know enough facts. <grin>

I've modified (slightly) your original code to do what you want. The modifications allow you to UPDATE the current row of the CURSOR. This feature depends on two items:[ul][li]The "FOR UPDATE" clause in the CURSOR definition and[/li][li]The syntax, "UPDATE <table_name>...WHERE CURRENT OF <cursor_name>"[/li][/ul]

DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;
CURSOR CONTROL IS
    SELECT  ilv.*, ROWNUM rn
    FROM    (   SELECT  SEGMENT_NAME, SEGMENT_TYPE
                FROM    REPLICA_CONTROL_TABLE
                for update
                WHERE   SEGMENT_TYPE ='MVIEW'
                AND     ACCION='REFRESH'
                ORDER BY BYTES DESC

             ) ilv
   ;
begin
FOR R_CONTROL IN CONTROL LOOP
UPDATE replica_control_table SET ID=MOD(r_control.rn, N_SCRIPT)+1
 where current of control;
END LOOP;
commit;
end;
/

Also, I've moved your COMMIT statement outside the loop sind the COMMIT withing the loop causes disruption to your CURSOR.


Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”