Re: Creating Partitioned Indexes on a Partitioned table

Hi Howard,

"It's a bodge-job of the first order, with little to recommend it apart from the fact that you end up with just one table to manage. And that, as I said first time, is not sufficient justification for it.

It sounds like a disaster just waiting to happen. And your original post
(from what I remember of it) indicates that it actually has just happened!"

>>

Well, we shall have to just 'agree to disagree' on whether it is a 'bodge-job' of the first order....;-)

I've been building relational databases since 1989 and DWs since 1991 and I am very clear on the benefits of what we are doing. In this particular case we seem to be 'stretching' the capabilities of Oracle to do what we want to do so we are compromising a bit. We typically do not have so many columns in a single physical table. This time it is 400 and usually it is only around 100.

We have no 'disaster'. This post was about 'can I save some CPU on index maintenance by not having indexes on specific partitions of a table that I know I don't need'. Seems the answer is 'no' in Oracle. So we must simply pay the price of the overhead of maintaining some local indexes that are not necessary. And that's fine, the whole of the 15 logical tables only add up to 12M rows, which is a very small table.

We only partitioned the table because we could not get the optimiser to only access the rows it needed to by having these logical tables presented as a 'view' which is our usual practice. By making them partitions we can force oracle to only read the rows we want it to read. And since it is a partitioned table nothing above the database needs to know about the cahnge. Previously Oracle was scanning the whole table pretty much every time it needed any rows from one of the logical tables.

Best Regards

Peter Nolan Received on Wed Dec 08 2004 - 04:42:11 CST