Re: partiioning option not worth it?

> 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.

Designing your application so that at run time the evaluation of what table name to use is trivial - it's part of centralising access to the database within the application (the data services layer?). As I said, I was already doing that, so the changes required to allow the specification of a partition key where straightforward.

In my simple case, I only need either all partitions (tables) or one which makes it even easier.

Reducing to a subset of N from M could easily be done by using an inline view to union all the relevant partitions, though I suspect that in practice there would be performance problems - I've found the optimiser tends to evaluate views with union all in full before evaluating the rest of the code.

Because I derive the table name from a code column, I don't need to change the code to support a new partition / table, I do need to add a new table, but that is the same as adding a new partition to a partitioned table. One could say I'm doing value partitioning, not range partitioning. If I were to implement range partitioning, I would do what oracle does and define a table to map value ranges onto table names. This I think could be implemented in a couple of hours.

> 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).

I agree that the 'official' approach is more flexible though I would argue about "scalable" because under the covers it is doing the same thing. Adding or deleting partitions requires no changes to the code. Reorganising partitions in terms of redefining ranges that apply would be more of an issue, but I'm not sure how much of an issue it would be with partitioning - at some point I'll go through the partitioning manual to decide under what circumstances to use the 'official' version.

The obvious situation that springs to mind is when joining to other tables - my app is structered to use "compressed" codes in a fact table with the expansion to the description etc occuring on the client side with caching. Even then, for most cases, I think it would be relatively straightforward to generate a union all query with a placeholder being substituted with the appropriate table name. This would all be more expensive, but I think it would not be as expensive as the partioning option, especially where you have to upgrade from standard to enterprise edition to use it.

One of things that the 'official' version appears to give is greater availability - it might be hard without interrupting service to do some of the things that you can do online with partitioning. The issue then, as ever is, is the benefit worth the cost.

There are non-cost related arguments which may be significant in the decision. Architectural/design skills are in very short supply - ie the cost of those skills in pay etc. may not reflect the value of the skills - it may well be better having a designer working on a part of the system where the payback is greater than the few tens of thousands saved by rolling your own partitioning scheme.

Howard J. Rogers <dba_at_hjrdba.com> wrote in message news:1014461447.805245_at_bugstomper.ihug.com.au...
>
> 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 - 06:48:50 CST