One important concept here, which only Marius touched upon, is recreating the table from scratch, instead of updating the existing one. update generates undo and redo, which takes a lot of time and resources. If you have indexes, updating them while you update the values in your table will add that much more overhead. There is a lot of discussion, for example on AskTom, about this issue (and this suggestion - of creating a NEW table in such a case, instead of updating the existing one). You will see that they always remind you to make a note of indexes, triggers etc. that may exist on your table, so you can recreate them after you create the new table, drop the old one, and rename the new one to the old name.
Then, since you have a very large number of rows (in relational database terminology there are no "records"), it is best not to use regular expressions. Michael offered a very efficient way to get the desired result using just standard string functions. Alternatively, if all your input strings are the same length, say ten characters (or if you don't really care to preserve the length - once you mask the "numbers" perhaps preserving their original length is irrelevant anyway) you may even do something like substr(source_num, 1, 6) || 'XXXX' - saving the repeated calls to length() and rpad(), one for each row in your table.
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
