Re: inserting empty records makes updates faster?

Hi David,

    What I am about to say is based on the relative speeds of disk I/O and instruction execution, that is, in most systems each disk I/O operation takes about the same amount of time as the execution of 10,000 instructions. Therefore the first place to focus on when tuning is the minimization of disk I/O operations.

    I think that you will have more success by adjusting the PCTUSED and PCTFREE values for your table than by inserting dummy records. Assuming that most of the columns in each row are either NUMBER or VARCHAR2 Oracle initially uses only as much storage space as is needed to hold the content's of the row at the time the insertion is made. My understanding (if I am wrong I am sure that no one will hesitate to correct me) is that updates which increase the number of bytes stored will result in the row either being expanded in place or, if the block containing the row is out of space, the row being stored as a chained row, that is the row being split across two physical disk blocks.

    The approach that was suggested to you will lead to chained rows, which is a very bad thing. Don't do it.

    Using inserts instead of updates is preferable. The problem with inserts is that, unless you have sufficient space in the block, the row being inserted will be written in a new disk block and some index reorganization, which involves writing more disk blocks, will be incurred. The unnecessary writing disk blocks is a bad thing. This can be avoided by setting a low PCTUSED for the table.

regards
Jerry Gitomer

David Michaels wrote in message <37792D02.B75B1722_at_shockmarket.com>...
>We will have a need to frequently increment a column in a row in any of
>a fixed set of possible rows if the row exists, or insert the row if it
>doesnt.
>
>We want this process to be very fast, since it will be done often.
>
>It's been suggested that we initialize the table with all possible
>entries set to 0 so that we can merely do an:
> [update]
>rather than a
> [try to update but if it fails do an insert]
>along the thinking that the one operation is faster than two. Is this
>faster, or does the database need to do the same work anyway?
>
>We're using Oracle 805. Does the db have an atomic "update row if it's
>there or insert it if it's not" method that would be useful here?
>
>thanks!
>
>--
>David Michaels <david_at_shockmarket.com>
>Director of Technology
>ShockMarket Corporation (650) 330-4665
>
>
Received on Wed Jun 30 1999 - 08:13:26 CDT