Re: SELECT, UPDATE and ressource usage

Dear Mr Awad,

> I hope that I presented a good solution to your problem
> or question. Looking forward to hearing from you again.

Thanks very much for your suggestion. For sure, it is excellent to create a new table. My problem was however to estimate the time needed to extend the size of the CARD_NUMBER field on the original (2'000'000 records) table, without disturbing people using it.

The iteration was used as way to avoid the usage of a huge rollback segment, which would be likely to be too large for the instance. It was also intended to avoid to disturb the concurrent creation of new occurences in this table, as it is used 7 days per week, from 6AM to 9PM.

from another answer, I deduce that my code had at least the following problem: for each block of 1000 modified occurences, a whole scan was performed, rollback segments were created and modified occurences were written down in the files.

A more conventional PL/SQL formulation, with a cursor and a counter would, at least, have required a single scan. Now, I am not sure that it will be enough to avoid any perturbation of the concurrent exploitation.

To have this update running smoothly, in background, I guess that I'll have to create an additional index, based only on the transaction dates, in order to select a small number of headers at every select.

What do you think?

Best regards

Emmanuel Baechler
Lausanne
Switzerland Received on Tue Oct 27 1998 - 13:05:24 CET