Re: Tuning an UPDATE command

Indexing 5,000 times into a 6,000,000 row table - probably the case that every single update is in a different block, so each row results in a physical read for the table.

Options for improvement: make sure that VERY few physical reads take place on
the big index - your time is indicative of one index physical read and one table
physical read per update.

    what are the options for using a compressed     index to make the index very small

    can you split the buffer into large keep, small     recycle and default; set the table to recycle     and the index to keep.

    Can you recreate the small table in sorted     order before doing the update ? CTAS     with ORDER BY works in 8.1.

Can up run parallel updates from the
small table - this may interleave I/O time on the large table ?

Your best serial time is likely to be not much better than 250 seconds (working at 5,000 table I/Os at a fairly pessimistic 20 I/Os per second).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kevin O'Gorman wrote in message
<384039C0.40012C0_at_trixie.kosman.via.ayuda.com>...
>This was a modest win. Time for the installation phase dropped 25%
>from 661 to 498 seconds. Now I have to attack the delete that's using
>180 of the remaining seconds -- an index will probably help.
>
Received on Sat Nov 27 1999 - 14:20:51 CST