Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: update Is Oracle update a direct update in-place?
Fengqing Zhou wrote:
>
> Hi!
>
> Sybase has direct update in-place and non-direct update in-place.
>
> Nondirect update in-place means a update is a DELETE followed by
> an INSERT.
>
> Direct update in-place means the record updated still keeps its original
> physical location after update.
>
> My understanding for Oracle update is:
> All updates in Oracle are "Sybase direct update in-place".
>
> Am I wrong?
>
> TIA,
You are correct. Oracle does not perform a 'delete then re-insert'
when a row is updated. If the updated row has grown in size and can't
fit within the current block, then it is relocated to another block.
However, to avoid referencing problems from indexes using rowid, the
rowid remains the same and the original block contains a pointer to
the 'chained' row. This is called chaining and should be avoided at
all costs by using the proper pctfree and pctused parameters. If
you often insert a row with little data and update it with more data
later, set your pctfree high to reserve block space for later updates.
If this doesn't work, you can try padding the initial row with dummy
data, either in the null columns or in a dummy column just for that
purpose, and then replace the dummy data with real data on the
subsequent update. Chaining essentially doubles the number of blocks
read (logically speaking) for one row.
-- --- Allen Kirby AT&T ITS Production Services akirby_at_att.com Alpharetta, GA.Received on Tue Jan 07 1997 - 00:00:00 CST