Re: partitioned table-number of tablespaces (oracle 10g2)
Date: Fri, 17 Dec 2010 09:19:13 -0800 (PST)
Message-ID: <e1f3b478-96d2-480f-8a3d-8dd6c7aae7e6_at_h17g2000pre.googlegroups.com>
On Dec 17, 2:33 am, Michał Jabłoński <michal.jablo..._at_gmail.com> wrote:
> On 16 Gru, 21:05, Mladen Gogala <n..._at_email.here.invalid> wrote:
>
> > On Thu, 16 Dec 2010 03:55:40 -0800, Michał Jabłoński wrote:
> > > I've got non-partitioned table which I have to move to partitioned
> > > table. I've decided to move it to hash partitioned table with 16
> > > partitions. It is a good practice to create dedicated tablespace for
> > > each partition with one datafile?
>
> > What would you like to achieve by splitting table into different
> > tablespaces, as opposed to splitting it into a single tablespace?
>
> > --http://mgogala.byethost5.com
>
> I think about performance, time of execution sql on this table. In
> this case it is better to create more tablespaces with one datafile or
> less with one or more datafiles? Or it doesn't matter?
Well, the only answer to that is It Depends. If you split the datafiles across various devices with their own controllers, it might make a difference. If everything is on a SAN anyways, the only difference would be recovery time (which can be important!). With heavy updating of the db (not even necessarily these tables), there can be differences in how many file headers need to be updated and whether you get a bottleneck updating controlfile information. On a SAN, the number of spindles request are spread across and the size of caches in various places can make such a question pointless, until you hit yourself in the knee (that's a joke, see Cary Millsap's paper thinking clearly about performance).
I agree with Noon's rant, though I would qualify it for situations where many people have nearly identical configurations and apps - the 10%. At some relatively low point, rules of thumb can be useful to at least get you into a ballpark where you can start scientific investigations. It's only stupid when applied inappropriately. I would red-flag the OP as possibly falling into that trap, from the limited information posted.
The take-away for Michael is you need to properly empirically test for your load and characteristics, and get to know people with similar situations.
jg
-- _at_home.com is bogus. Microsoft, Apple, EMC and Oracle walk into a bar... Bartender says, "we don't see many patent blocks in here." "Unless youse pays us $14B, youse won't see any more." http://www.bnet.com/blog/technology-business/microsoft-forms-a-patent-bloc-with-apple-emc-and-oracle/7432Received on Fri Dec 17 2010 - 11:19:13 CST