Re: Local partitioned indexes and partition exchange in 8.1.7

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

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