Re: partioning
Date: Tue, 15 Nov 2011 15:53:21 -0300
Message-ID: <CAJ2dSGTLo2jFDMM2v1AK5xKieLfYJNAQV9NOCN5CdxSwHnb+Tg_at_mail.gmail.com>
I think partitioning benefits are realized when the majority of your queries access the data through the partitioned column. That way the rest of the operation is done on a considerably smaller portion of data. Lets say you have a table partitioned by date (one partition per month per year) and you want to get all the operations of a certain type (assume there are few discrete types, about 10 should be enough, usually you'd see two or three). If you don't have a partition you need to do an index scan to get all the blocks that are in you date range and then scan those blocks to see if the types coincide. The first scan would be of an index containing the entire table, it should be organized, but still should take longer than if you have a partition and you get a smaller index. I.e. less reads.
Sure, if you had each partition in different very fast physical arrays it would increase performance, but that's true regardless of whether you use partitioning or not. If you spread you datafiles across several fast disks, chances are you're gong to get better performance. But I think the real benefit of paritioning is reducing the ammount of reads for particular queries.
hth
Alan.-
On Tue, Nov 15, 2011 at 10:12 AM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:
> On 11/15/2011 01:25 PM, Joel.Patterson_at_crowley.com wrote:
> >
> > Someone has requested that we cost out some thing including portioning
> and suggested that to get the "performance benefit out of partitioning you
> needed separate physical disks".
> >
> > It would be 11.2.0.3, we use a SAN, and have a variety of RAID groups
> including 10, 5, and 6. I don't expect this to ever get to that size,
> right now it is about 100Gb, but size hasn't been discussed yet. (we are
> meeting in the future).
> >
> > I wonder just how much data is needed to realize a cost/benefit, and
> other considerations such as channels etc., and basically, is that true and
> accurate? I have asked him for some supporting docs.
> >
> > I remember hearing that raw disks could give you 10% more -- until you
> dug a little further and realize that you would need about a Terabyte of
> data to realize such an improvement - in reality.
> >
> > Any comments or experience is appreciated, (including supporting docs if
> handy).
> >
> >
> > Joel Patterson
> > Database Administrator
> > 904 727-2546
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> Joel,
>
> I don't think that one should first think "disks" when one think
> about partitioning. If you consider a table or a partition as a kind of
> pool of Oracle blocks, whenever you partition, somehow you redirect your
> inserts to a smaller pool - you are, if I dare say, lowering entropy.
> That means that parallel inserts may conflict more if they go to the
> same partition, and also that selects may perform fewer logical I/Os if
> there is in your query something that allows partition pruning. I am not
> sure that the comparison is very good, but for me it's akin to the
> clustering factor of indexes, primarily more a question of how rows are
> distributed among Oracle blocks than how Oracle blocks are stored on disk.
>
> HTH,
>
> --
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 15 2011 - 12:53:21 CST