Re: 8.1.5 Partition Row Movement performance

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.

This is only a simple test and of course there is lots of tuning that could be done to improve both benchmark times.

We'll be processing 10's of millions of rows so row movement seems to be out of the question.

In article <938371252.10177.0.nnrp-13.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>Icant' seem to find such an extreme difference.
>The current gets in the partitioned case seem to
>be slightly higher, and the redo log generated seems
>to be about 20%higher on a few quick tests.
>
>Can you give a more detailed description of the test.
>Are you sure that the reads to select the row to be
>updated were doing partition elimination ? That could
>explain a doubling in cost.
Received on Tue Sep 28 1999 - 08:34:12 CDT