Re: Creating Partitioned Indexes on a Partitioned table

peter_at_peternolan.com wrote:
> 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.

Yeah, but before you get carried away with how trivial that is *for you* spare a thought for the poor sod that reads this at Google in a few months' time and thinks "that sounds like a good idea", implements it accordingly, and runs straight into a performance brick wall as a result.

In this scenario, I couldn't really give a monkey's whether it works for you or not. It's *bad practice* and I would hate for anyone else to think otherwise. That you run into an excessive indexing issue just underlines the point.

And there really isn't any need to trot out how many years experience you have doing this sort of thing. Doing this specific sort of thing is bad practice however experienced you happen to be (and I've been normalising things since 1985, so let's not go there).

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

Precisely.

Just out of interest, what was it that was so awful about having the "logical tables" as separate, physical tables? Because so far, you've admitted to partitioning problems, indexing problems, tables with 400 columns, and excessive CPU consumption problems... and I've yet to hear one convincing reason for cheerfully being saddled with all these issues.

HJR Received on Wed Dec 08 2004 - 05:29:40 CST