Re: partioning option not worth it?

I should probably clarify why I asked the question... My employer is, shall I say... cheap, and the chances of getting EE as well as the partitioning option are prettttty slim. I have used the EE + P.O. in my test database (as a trial), and the benefits were numerous. Most of the uses of PO are not necessarily crucial to our environment however, so I'm always looking for ways to improve performance. A thought of mine was to create tables based on the id of each client (about a 100 clients), for quicker response time when generating reports etc. We currently have about 60 million rows in our main table, which isn't huge, but within the next few months that is expected to increase to about 500M rows.

just wonderin' if any of this sounds viable.

Cheers,

Brick

;-)

"Keith Boulton" <kboulton_at_ntlworld.com> wrote in message news:<No2d8.1990$hM6.129212_at_news6-win.server.ntlworld.com>...>
> 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 - 10:05:29 CST