|
|
在asktom上看到如下一段文字，指专门讨论大表更新问题的：
不知道大家是怎么看的，到底order rowid真正起作用了吗？
http://asktom.oracle.com/pls/ask ... 2330#21410664830973
please refer "How to update part of a Very Large Table July 28, 2003"
When updating a large table in a database having comparatively less buffer cache one thing to make
sure is that blocks are updated in a continuous order. Suppose some records of block1 is updated
first in the beginning and next after updating some other blocks, then block1 will be written twice
by DBWR and LGWR. After the first update block1 may get flushed out by LRU algorithm. If all
records in block1 is updated at a time then block1 is wriiten by LGWR and DBWR only once. If the
update statement is doing a full table scan then bloacks are updated in order. But the order can be
different when the is using an index for selecting the rows.
The SQL statement that makes sure of that is
update (select id, description from test_update where rowid in(select rowid from (select rowid from
test_update order by rowid))) set description=description||' new val' where description like '%5%';
Followup August 28, 2003 - 7am Central time zone:
not necessarily so -- first lgwr doesn't write blocks -- it writes redo. second, dbwr may or may
not write the block more then once.
I would never never never use your approach. why I would want to
a) read the entire table
b) sort the rowids
c) distinct that set (implied)
d) join to it (and you are making the assumption the table in the IN will be the driving table and
that a hash join will not be used and ..... lots of other stuff)
when a simple:
update T set d = d || 'x' where d like '%5%'
will do it -- if you are worried about the index on d being used - you would simply FULL hint it --
but doubtful that it would be necessary.
the additional work you add would more then offset the work you are trying to save. |
|
楼主: fromeast
IP卡
狗仔卡
发表于 2009-9-25 10:24
显身卡
发表于 2010-1-20 10:51
