Update Statement

[amerbaig]

I have column in a table named Sno. I want to update it with a new sequence number like 1, 2, 3 4,5 In short I want to get Sno for each row. My table is big containing 50000000 Please tell me the most efficient way of doing it.

Regards
Amer

 

[SpiritOfLennon]

You need to create a sequence first use ;
create sequence sno_sequence

Then use;

update <table_name> set sno = sno_sequence.nextval

This should work SOL
The best thing about banging your head against a wall is when you stop.

 

[SpiritOfLennon]

Sorry that should have been;
create sequence sno_sequence start with 1 increment by 1
for the first part SOL
The best thing about banging your head against a wall is when you stop.

 

[amerbaig]

Thanks. It worked for me. Is there anyway to optimize it? Or do I need it for large tables?

 

[SpiritOfLennon]

I don't understand what you mean. What are you trying to achieve? SOL
The best thing about banging your head against a wall is when you stop.

 

[sem]

You you need just numbers you may use:
update <table_name> set sno = rownum

This approach is better in the way it does not need any sequence and also does not permit gaps in numbers.

But if you chose using sequence you may increase its cache size:
alter sequence sno_sequence cache 10000 (or some big number)

 

[amerbaig]

Optimization means updating such a large table, may cause system to slow down. Second solution seems much faster?

Anyway thanks for great help

Regards
Amer