Re: Global Indexes on Large Partitioned Tables

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 15 Apr 2008 01:31:22 +0200
Message-ID: <108701c89e87$a88c4e00$3d02a8c0@ADLA>

Hi Don,

> We have a range/hash partitioned table, around 250M rows, that we are
> seeing some recent performance slowdown on. This slowdown is occuring
> during a large batch UPDATE job.

wouldn't you mind to post the explain plan of the update? If it start some how with

|   0 | UPDATE STATEMENT
|   1 |  UPDATE
|   2 |   NESTED LOOPS

it could take hours to update 10M of records. While updating lots of records the NL isn't necessary the best approach. The large number of "db file sequential reads" could be a sign that NL is used.
Btw what is the average throughput of the quicker and slower update (in updated rows per second)?

For a huge update I'll suggest to update a joined view of the main and helper table. Something like
update
(select columns to be modified, columns used for modification from main_table b , helper_table a
where join condition . . .
)
set column to be modified = column used for modification,  . . .

This should work for you fine if the helper table has the same unique index on it.
Preferably the join of both tables should be a hash join. You may deploy parallelism and possible partition wise join (I'm not sure how / if the partial PWJ on subpartitioned table works). The best effect of this approach will be in case that the number of subpartition to be updated is limited.
As a side effect you'll see that the global index is not used.

good luck,

Jaromir D.B. Nemec

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 14 2008 - 18:31:22 CDT