Slow update

[mooks]

Hi

I have a table with only 15,000 records and i need to update all the records to increment by 1 every min, therefore a full scan on the table is neccessary. However, the update is very slow!!

I did a timing and it can takes up to 2 mins to update all the records! Is there any ways which I can improve the perofrmance of the updates??
Thank you!

[DaPi]

Sounds like bad design! If the value of a proposed column is incremented by 1 every minute, then one solution is for it NOT to be stored but calculated from sysdate and some start time (which IS stored in the row).

[mooks]

Thanks... how do i get the time for the previous 30 mins?

[DaPi]

? ? ? ?

If you store sysdate in (say) DateCreated when the row is inserted, then
trunc((sysdate - DateCreated)*24*60)
says how many minutes it has existed.

If that's not what you want, you need to explain your business requirements better.

[Dilippatel]

Mooks, can you check your wait events for which the query execution is waiting on.

select sid, event, total_waits,average_wait, time_waited/100 Time_Waited_in_seconds, max_wait
from v$session_event
where sid = &sid

Cheers