Re: Creating Partitioned Indexes on a Partitioned table

peter_at_peternolan.com wrote:
> Hi Howard,
> "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."
>
>

>>>What someone else does based on what they read on Google is their

Nice attitude! I think you have responsibilities too.

> "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."
>
>
> 1.
> Well, I haven't admitted to: 'partitioning problems', 'indexing
> problems', 'excessive CPU problems' , though in this case I do have one
> table with 400 columns. This is quite unusual. Never had one more than
> 100 columns even using these techniques before.
>
> I have physically partitioned a table because the oracle optimiser
> still seems to be making some rather strange choices and I could force
> it to make the choices I wanted by partitioning. Creating the
> partitions was not a particularly difficult task and it is only
> 'visible' at the database storage/optimser level, not at the table
> level.

I didn't ask you about why you adopted partitioning. I asked why you merged multiple tables into one table, so that you can keep referring to 'virtual' tables within the real, physical one.

> Sure, I would have preferred not to partition the table physically. (As
> I said, it's only 12M rows. I could do the same thing on SQL Server on
> may laptop and not need to partition the table. I can make SQL server
> do what I want without partitioning. )

Yawn. Well go an use SQL Server then! What possible point is there in a comment like that one? We get it here a lot from people who have paddled themselves into a certain creek using a little knowledge and a lot of assumptions that Oracle is just another RDBMS and now desperately wish they hadn't.

> Having partitioned the table to force the optimiser to do what I want I
> was interested if we can do away with some indexes that I happen to
> know are redundant. And we can't apparently.

So you do have an indexing problem. Thank you.

> The only 'excessive CPU' problem we are having is that Oracle was
> picking such bad plans, even after analysing.

So you do have an excessive CPU problem. Thanks again.

> It may well be that I do
> not know enough to influence the parallel optimiser properly.

It could be your bodgy design, which you still haven't justified.

> I'm not
> an ORA 9.2 Parallel Server DBA.

Uh huh. Parallel Server doesn't actually exist in 9i. The nearest equivalent is called RAC. And it's got precisely nothing to do with your situation.

> The client promised a DBA for the
> project and he resigned and has not been replaced yet.

I'm not surprised he resigned. I wouldn't want to take responsibility for this mess which you seem unwilling to actually explain or justify.

You've got a self-inflicted awful design, and you self-confessedly have bad execution plans and inappropriate over-indexing. And you breezily say 'but I am experienced in data warehousing' when the design is criticised.

End of thread Peter. I'm clearly wasting my time (and rather more to the point, you're wasting yours trying to patch up awfulness, when the correct approach is to stop running with the awfulness in the first place.)

HJR Received on Thu Dec 09 2004 - 13:46:09 CST