Re: Creating Partitioned Indexes on a Partitioned table

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
responsibility. If they use a bright idea they do not fully understand and their system does not work that's up to them.

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

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

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.

The only 'excessive CPU' problem we are having is that Oracle was picking such bad plans, even after analysing. It may well be that I do not know enough to influence the parallel optimiser properly. I'm not an ORA 9.2 Parallel Server DBA. The client promised a DBA for the project and he resigned and has not been replaced yet. So we had to do the best turning we could on Oracle without a qualified 9.2 DBA. (I really DO believe in the value of DBA knowledge, I used to be one...;-))

>>

2.
What is good about this concept of having logical tables imbedded into single physical tables?

Well, 'lots and lots'.

Mostly, it is far cheaper to build and maintain BI Infrastructures like this. And I do mean 'FAR CHEAPER'.

Based on these modeling techniques (and some others I have not mentioned here) ALONE we can take 30%-40% of the cost out of a complex BI environment. (ODS/DW/Data Marts.)

Given the usual projects I work on are USD1M and up, pulling 30% out of the cost is worth doing.

This means, given a customer wants to solve a particularly complex BI problem and he asks for 'quotes', I know that our solution will come in at least 30% less than the next guys and actually be 'better'.

So, why do things this way? Because it is cheaper, faster, more efficient, more robust, more stable, more maintainable and generally more 'elegant' way of solving the complex BI modelling problems we face today.

And to top it off. I've been developing the 'next generation' of such design technqiues that will reduce the cost and increase the flexibility of BI modelling even more.... ;-) Best Regards

Peter Nolan Received on Thu Dec 09 2004 - 08:09:39 CST