RE: Partitioned or non-partitioned indexes
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <kcboyes@xxxxxxxxx>, "'Adam Musch'" <ahmusch@xxxxxxxxx>
- Date: Mon, 25 Apr 2011 19:58:09 -0400
It may be. The question is whether partition pruning overhead outweighs the
benefits in elapsed time and consumption of machine resources.
On my laptop, a scan count of a million row table takes about .15 seconds,
including the parsing and the output on the screen. Please do not take this
as bragging about my laptop, but rather a real consideration of how powerful
your server is likely to be and when the tradeoff in favor of extra
complexity kicks in versus the speed of machinery we have now as the bulk of
a database grows.
In 1989 or so, if you told me you had a million row table I would probably
try to change your design to multiple tables with union all views to
represent the whole when you needed to (we didn't have partitioning then).
Now it seems questionable whether the overhead is worth the engineering. But
a few tests should guide you. At that size you should be able to test the
top several variants that make sense to you.
Of course if your detail table of 2.1 million rows contains blobs averaging
1 gig each or some other anomoly, that would change the presumptions in my
analysis.
Now if your partitioning is intended for long term life cycle maintenance
partitioning on some type of time or aging period, that is an entirely
different question that what might serve a given query best. If find Tim
Gorman's write up of "scaling to infinity" to be the clearest telling of
that tale I've seen.
Good luck, and notice I wrote "may be." Your practical implementation may
have uncommon characteristics. And I would not dismiss it without looking.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kevin Hale Boyes
Sent: Monday, April 25, 2011 6:25 PM
To: Adam Musch
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Partitioned or non-partitioned indexes
The detail table will have about 2.1 million rows.
Is that still small for partitioning?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
