Re: Creating Partitioned Indexes on a Partitioned table

peter_at_peternolan.com wrote:
> Hi Howard,
> "Wrong. A table should be what it relationally was meant to be: a set
> of
> related records. You don't create or not create tables to make your
> maintenance operations easier. You create as many tables as are needed
> to break records up into related, normalised, groupings."
>
>
> Well, you might just have to trust me that there are some very good
> reasons for NOT 'normalising' tables in a very complex DW
> environment....;-)

That's why 'normalised' is just one of three different qualifying words in my original sentence. It's one factor. The business of being related and grouped sensibly is also important. And all in contrast to the idea of creating a table for ease of maintenance operations.

I cheerfully accept that one can over-normalise. And that there are often good grounds for careful, selective de-normalising. I've often done it myself. Materialised views and global temporary tables are, indeed, quite often ways of storing a denormalisation of otherwise normalised tables, after all...

> In this particular case we are putting 15 'different' tables into one
> physical table and then creating 15 'logical' tables coming out of the
> one physical table.

What you are describing here, and in the paragraph below, isn't thoughtful denormalisation done for successful design and performance reasons. 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.

> And yes, in some cases we want to query one
> 'logical' table on some of the columns in that 'logical' table and
> never index those columns on other 'logical' tables. And yes, in lots
> and lots of rows fields that are not used for that row are null.
>
> We do this very commonly in complex DW environments because of a
> variety of savings/features vs. using separate tables.

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!

Regards
HJR
> Best Regards
>
> Peter Nolan
>
Received on Tue Dec 07 2004 - 14:20:16 CST