Re: Local partitioned indexes and partition exchange in 8.1.7

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • To: Eric.Buddelmeijer@xxxxxxxxxx
  • Date: Wed, 12 Jan 2005 09:28:47 -0500

Comments inline.

Eric Buddelmeijer wrote:

>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. 
I wonder why it keeps getting slower? It must be a miracle! Blessed are 
the cheesemakers!

>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.

We are talking about 3 million rows a day? 3% is approximately 90,000 
rows. No indexes?
Hmmm, have you considered bigger HW? Much bigger HW?

>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.

You can do it in 8i, it works just fine. No problems there. Partitions 
and local indexes are 8i concepts. Using table which is being loaded 
with 3,000,000 daily and has 90,000 of those
records update without indexes (indices?) is an outlandish concept.

Mladen Gogala
Oracle DBA


Other related posts: