Simple update taking too long
969184Oct 18 2012 — edited Oct 23 2012Hi all,
I'm working on a some kind of BI data processing and right now I'm trying to improve the time needed to process the whole data.
The problem is that I have a table with 1.200.000 rows, and 53 columns. On the last 18 cols, which are NUMBER, I run 2 updates on each one. The first one updates to 1 if the condition matches some where clauses. The second one marks to 0 all data which doesn't match.
Well, the second update takes too much time, imo, being a simple update (UPDATE TABLE_A SET COL = 0 WHERE COL <>1).
I've tried a few things, like trying to update all rows to 0 and after that launching the update which marks right data. It's a simple UPDATE TABLE_A SET COL = 0, but it takes more time that the update with the where clause.
Strangest thing is that the first col to update (number 36) takes like 1 minute to complete, the second (37) takes a bit more, and the last one take like 15 minutes to finish the update.
I also have tried to put an unique key, launching statistics, using NOLOGGING tables + APPEND hint, but I can't see any improvement.
The last one update get the local time on a date col, and takes more than 1 hour. I'm getting crazy with all these issues.
I'm using Oracle 9.2.0.1.0.
Has anyone had any issue like this? I've googled intensely but I can't find any valid solution.
Many thanks on advance and excuse the broken english.
Best regards!