Re: Local partitioned indexes and partition exchange in 8.1.7
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Eric.Buddelmeijer@xxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jan 2005 22:35:19 +0100
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@xxxxxxxxxx>
To: "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
