RE: partioning

        Partitioning doesn't really need separate disks to get the bang for the buck. But you do need to pick the appropriate and most effective key to partition on. I've seen this done right and wrong and the wrong doesn't take very long to figure out. Rebuilding the table(s) can, so I'd start with an analysis of the queries that access the data as well as the data itself. There are those obvious keys like month, quarter, year, etc. if you're into a financial application. If it's an engineering application it will be different. Really depends on the application. As for how much data does it take to realize a benefit, again it depends on how many rows of data there are, the distribution on your partition keys, and the frequency of the queries thereon. I'd say that if you have less than a million rows of data you probably should take a look and the indexing of the data before trying to partition it. Poorly indexed data will cause issues no matter how much there is or isn't.

Richard Goulet
Senior Oracle DBA/Na Team Leader

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Tuesday, November 15, 2011 7:25 AM To: oracle-l_at_freelists.org
Subject: partioning

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 15 2011 - 15:14:04 CST