Re: partioning option not worth it?

Bricklen <bricklenREMOVETHIS_at_shaw.ca> wrote in message news:3C7471A6.3B720E42_at_shaw.ca...
> about this? I have several ideas in mind, but I'd like to hear from
> someone more knowledgeable about possible ways it could be done, and if

I wouldn't claim to be especially knowledgable (and I'm not sure about my spelling either!).

But, it seems to me to depend on what you're doing.

The advantages of a partitioned table are

    performance, especially on table scans and especially if parallel query is used

    administration, you can create and drop partitions easily.

In my case, I've developed an end-user query program which builds the query at run time. One of the parameters is present in >90% of queries and was the partition column. It's a trivial matter for me to create N tables (just like I had N partitions) and modify the code to point at a given table based on this key. I can easily build a union all view for queries against all partitions.

I also need to modify the data load code to load data into the N tables.

This will all take me less than 1 day.

Compared with using Oracle partitioning, I lose the ability to do some admin activities without interruption of service which is not a big issue because the database is small enough so that I actually have two copies of the schema for the current run of the data update job and the last run. This means I can run the update jobs without worrying about e.g. consistency between different tables.

I also don't get a performance advantage where I could have used more than one partition of the partitioned table.

I'm not saying that there would not be times when partitioning would be useful or even essential, I'm merely questioning the costs and benefits. Received on Thu Feb 21 2002 - 02:39:43 CST