Re: 8.1.5 Partition Row Movement performance

What was the corresponding insert/delete ?

    insert into table2 select * from table1;     delete from table1;

or something else ?

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 ?

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 ?

--

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

simon.quinn_at_bigfoot.comX wrote in message <7sqg4k$v4k_at_edrn.newsguy.com>...
>In my original SQL statement I didn't specify a where clause as it was
updating
>the whole table. After adding a where clause so that the partition is
specified
>the performance has increased a lot but is still slower than INSERT/DELETE
>between two tables.
>
>My setup is:
>db buffers 5Mbytes
>SGA 23Mbytes
>
>Table to be updated is 250Mbytes with 200,000 rows (PT_PARTITION = 1),
default
>storage parameters. Table contains 2 partitions based on the pt_partition
>column.
>
>Second table used by INSERT/DELETE is exactly the same apart from the name.
>
>Rollback segment of 250Mbytes created.
>
>Row movement SQL statement:
>UPDATE table
> SET pt_partition = 3
> WHERE pt_partition = 1;
>
>Time to do ROW movement with WHERE clause:
>3 minutes 29 seconds
>
>Time to do ROW movement without WHERE clause:
>5 minutes 49 seconds
>
>Time to do INSERT/DELETE between two tables:
>2 minutes 32 seconds
>
>Database was shutdown and restarted for each test.
>
>Noticed the rollback segment usage is 8% higher using row movement.
>
Received on Tue Sep 28 1999 - 09:57:50 CDT