Re: partiioning option not worth it?

I can appreciate that.

But based on what you've written I would hardly call what you did a replacement for the full functionality of Oracle's PO. A partial replacement, perhaps. Enough for your particular purpose, perhaps. But it would never work in the environments I have as it would require a huge investment in addition to what it does.

Daniel Morgan

Keith Boulton wrote:

> 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 - 09:06:51 CST