Re: partiioning option not worth it?

As I said, one day I'll sit down with the partitioning manual however...

Mark Townsend <markbtownsend_at_attbi.com> wrote in message news:B8A2EE6E.13F17%markbtownsend_at_attbi.com...
> For small amounts of data building your own partitioning scheme IS
trivial.
> But how much data are you adressing ?

The quantity of data is irrelevant. The number of partitions to be supported is an issue.

> Doing multiple full table scans on what is basically a union all view is
easy when you only have 10 Mb of data
As I said, the point actually is to perform searches on only the relevant tables. This is easy to achieve but tedious unless you design your application with an architecture in which the runtime query is generated from a single component. In any case, this is a good idea.

> address +500 Gb of data in a single table. And your solution doesn't
support

> secondary indices, prune partitions

If by pruning partitions you mean excluding tables not required for a query, that is exactly what my solution does.

> determine the best optimizer plan from both table and partition level
statistics
Under what circumstances would this be an issue? Almost certainly, if the partition key was given as part of the query, a partition level search is most efficient.

> perform partition-wise joins or

I agree this is a more interesting problem, but, as I said, it can be addressed by using templated sql statements. In any case, for a reporting database when partitioning is most likely to be used, you are, I believe, generally better off not joining the partitioned table to anything else, but to perform joins only after aggregation for reporting purposes has taken place.

> prevent you from updating rows so that they no longer blong to the correct
Check constraint

> (or move the rows if you do).

Agreed. But then, I believe in most cases you have a bulk dataload with more appends than updates. After all, what are you going to partition on? Normally it will be a date or business unit.

> I would agree that from your description, the partitioning option is way
> overkill for your problem.

> But for big sites with vast amounts of data, they
> simply could not survive without partitioning.
Again, the quantity of data is irrelevant.

> BTW - I believe the row movement problem will be an issue in your app
given
> your description of how you are using codes to determine which partition
to
> ship queries to.

It will not be an issue because it is a record of transactions for business units. A transaction does not move from one to another.

I will re-iterate. I'm sure there are circumstances when buying the partitioning option (and more expensively, enterprise edition) is cost effective and possibly even required. I just don't believe there are many such circumstances ie less than 5% of the cases where partitioning is being used.. Received on Thu Feb 28 2002 - 00:24:57 CST