RE: Partitioned or non-partitioned indexes
Date: Mon, 25 Apr 2011 19:58:09 -0400
Message-ID: <10dc01cc03a4$a1854f50$e48fedf0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Kevin Hale Boyes
Sent: Monday, April 25, 2011 6:25 PM
To: Adam Musch
Cc: oracle-l_at_freelists.org
Subject: Re: Partitioned or non-partitioned indexes
The detail table will have about 2.1 million rows.
Is that still small for partitioning?
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 25 2011 - 18:58:09 CDT