Re: Tuning an UPDATE command

Jonathan Lewis wrote:
>
> 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
>

I have no idea; I'm not familiar with compressed indexes other than BITMAP indexes, and that wouldn't be appropriate here.

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

I'm new at this; this sounds like gobbledeygook to me. Can you point me into the docs where this sort of thing happens? I have all the PDF files of Oracle manuals, plus the Oracle press DBA and Tuning books.

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

CTAS? What's that? I build the small table every time, so adding an ORDER BY is no problem. Is the idea to localize references in the big index?

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

I've not played with the parallel option at all. I presume this would work, but that this would require partitioning of the tables. Could be done, I guess.

>
> 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).

Probably a bit faster then, this is pretty fast iron; just a slow DBA. Pentium III 550, two IDE drives on two controllers; both drives very new. No partitioning of tables at the moment.

>
> --
>
> 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.
> >

--
Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net At school: kogorman_at_cs.ucsb.edu
Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org Received on Sat Nov 27 1999 - 15:26:36 CST