Re: 8.1.5 Partition Row Movement performance

>
>What was the corresponding insert/delete ?
>
> insert into table2 select * from table1;
> delete from table1;
>

Yes, as simple as that.

>
>I would guess that the extra time spent when
>updating without the where clause was down
>to the select statement running over all the rows
>that had moved from partition1 to partition3 to check
>blocks for read-consistency, and possibly re-reading
>rollback segments (which would probably be on
>disk given your db_block_buffer size)
>
>As an idle test, you could try an update which
>moves the rows in the 'opposite' direction, and
>see if that makes a difference.
>
>The test with the where clause is interesting.
>I am not too surprised to see a difference on
>a row by row basis, but I would not have expected
>to see 2:34 going up to 3:29 on a bulk update.
>Did you also check the difference in redo log
>generated ?

Didn't measure the size of the redo.

>
>
>The other question to ask, of course, is whether
>or not this is a reasonable test of what you want
>to achieve -
>
>If you are expecting to cause millions of partition
>migrations to occur are you partitioning in the
>most appropriate way ?

Ideally this could all be done using a relevant partition key but for various reasons we're stuck with the current setup.

Perhaps row movement requires some more tuning on our platform. Until then it looks as though it's best for us to stick with INSERT/DELETE. Received on Tue Sep 28 1999 - 13:23:34 CDT