It all sounds fascinating, but how do you get partition pruning with your
solution? I guess that comes from modifying the query code... but the point
with the 'official' solution is that the code doesn't need to be altered, it
happens automatically. Which must make it an inherently more flexible and
scalable solution (I presume you'd have to continually re-code if the number
of partitions needed to be changed and so forth, which starts sounding like
a more and more expensive option).
Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Keith Boulton" <kboulton_at_ntlworld.com> wrote in message
news:k4Jd8.13062$H43.1551647_at_news11-gui.server.ntli.net...
> 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 - 04:50:16 CST
