Re: Local partitioned indexes and partition exchange in 8.1.7

Hi Eric,

> keeps getting slower.

.

>Approximately 3% of the rows is updated after
>insertion, judging from the contents of an audit table.

If I had a free guess to diagnostic it remotely, I'd say you have an update based on nested loop with auditing table as outer table and a full partition scan as a inner table.
You don't say if you have only one update per day or if you update on a per row basis.

If the former is true you may try to use updatable join view to perform the update; should be acceptable on 3M row partition even without a dedicated index (and much better with an index covering the column in the where clause of the update).

> In oracle 9i I would suggest a local partitioned index on the relevant
> columns . And I am thinking of building the index using partition exchange
> to minimize down-time.

Exchange partition works fine if the new daily data fits exactly one partition.

I assume this is well know, I mention it only for completeness - use INCLUDING INDEXES in exchange partition, otherwise the local partition of the index gets unusable.

If you have lot of updates, be careful (read: don't use) with bitmap indexes.

You may also check if the partition pruning works well in all queries; there are some traps (similar to index disabling), e.g. trunc(part_key_column) = some_date suppress partition pruning.

good luck

Jaromir D.B. Nemec

• Original Message ----- From: "Eric Buddelmeijer" <Eric.Buddelmeijer_at_elegant.nl> To: "'Oracle-L Freelists'" <oracle-l_at_freelists.org> Sent: Wednesday, January 12, 2005 3:15 PM Subject: Local partitioned indexes and partition exchange in 8.1.7

Hi Listers,

One of our customers has problems with an application that keeps getting slower. It has a relatively large central table without indexes which is loaded daily with about 3 million rows. The table is range partitioned per day. Looks like a lot of querying is done on the table with the necessary partition full scan. Approximately 3% of the rows is updated after insertion, judging from the contents of an audit table. In oracle 9i I would suggest a local partitioned index on the relevant columns . And I am thinking of building the index using partition exchange to minimize down-time. But the customer is still on 8.1.7 and I have no experience using that version with these features . Are there any known issues with this approach in 8.1.7 that any of the listers knows of? Better approaches are always welcome of course.

TIA,
Eric.

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



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