Re: partiioning option not worth it?

I cannot, of course, do that since it is the copyright of my employer. But I've already said what I was going to do.

I was using partitioning to improve performance by allowing querying by business unit on any combination of 15 other attributes. 95% of the queries are for a single business unit of which there are 12. By using partitioning, I reduced the time for a query involving a table scan by a factor of 12 from seconds to < .3 seconds, which is the difference between unusable and usable in an interactive application. An alterntative query path involved a group of assets managed by an individual ( a system ) - a table partitioned by system id was used in this case.

The changes made were:

1. Replace 4 partitioned tables each with a number of individual tables with a suffixed name (which was the name previously used in the partition since that's essentially what oracle does). Also create a view that was the union of the individual tables. This took about 1 hour, because I was trying to rush and made syntax errors.
2. Replace the data population code for the four tables with code to explictly populate the new "partitions". This took 2 hours including a test run.
3. Modify the query building component to derive a table name based on the selected system / business unit. This took 2 hours and I added some additional indexes for some types of query.

You will almost certainly reply with "so you didn't do x, y, or z" or "that's trivial" and I entirely agree.

Which is my point. I am more and more convinced that partitioning is *mostly* trivial.

Essentially, all that oracle does under the covers is create a table per partition and then map the table name onto the underlying tables based on the query parameters.
This is simple to implement in any decent front end development tool.

By the way, I was told by the head of an IT department once that a rule of thumb is to double someone's pay to estimate what they cost. I tried to perform the calculation:

40000 Pay

4800     NI
4000     Pension
4000     Redundancy pay
8000     Expenses
2000     PC
1000     Desk
4000     IT Support
67800     Total

This would leave 12200 for office rent and the cost of the cleaners. The cost of my managers / bean counters is their cost, not my cost.

You can expect 200 days work per year per employee, so for 80,000 / 200 = £400 per day.

On a twin processor server, the paritioning option costs c £14,000 = 35 days development effort.

Given that you don't generally need the enterpise edition there would be an additional saving of £32,000 = 80 days of development effort, most of which goes on design and build because the analysis and testing effort is approximately the same if you use the partitioning option or do it yourself.

If they could, Oracle would sell only the enterprise edition, but they have to work in a semi-competitive market so they produce a standard edition and have to judge what features they can convince people to pay for in the enterprise edition. Fortunately for them, people generally assume that if it's done in the database (AQ, Replication, Partitioning Option) it is magically better than code that could be developed in-house.

damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message news:3C76E7DA.C477EEF1_at_ci.seattle.wa.us...
> Post your code. Let's see what you did ... or didn't do in duplicating the
> functionality.
>
> Daniel Morgan
>
>
>
> Keith Boulton wrote:
>
> > For anyone interested, it took about 5 hours and I also added a
performance
> > enhancement.
>
Received on Sat Feb 23 2002 - 03:13:28 CST