Re: Partitioning best practices

JL We think along the same lines :-)

My 2 cents on indexes: we depend on using alter table exchange partition to load data in, and the existence of global indexes increases the time it takes this command to execute from 5-10 seconds to 17-20 minutes. We've eliminated all global indexes as a result (even where they may be useful) and gone with local indexes (mostly bitmaps) in all cases. Hasn't really affected performance too badly.

Followup question for you JL in re parallelism: we're having a hell of a time deciding how to use parallelsm in regular operations. When we have it turned on, any query immediately pops up 8 parallel engines and completely hogs all the CPU and i/o channels to the data, effectively serializing database access and killing overall performance. Is the only way to really figure out what the best solution is to just do trial and error with degrees and options til you find a workable solution? Or do you even use parallel server outside of loading?

boss

>
>
> First decide
> a) why you want to partition
> b) how any particular form of partitioning gets you a benefit.
>
> e.g. If it's for ease of loading and maintenance, then
> a typical strategy will be range partitioning with local
> indexes so that you can partition by time and use
> partition exchange and drop.
>
> If it's for performance, then you partition according to
> the most critical queries, introduce global indexes where
> necessary, worry about options for (full and partial)
> partition-wise joins; and take the hit on loading, exchanging
> and dropping partitions.
>
> In both cases you try to work out how a suitable degree
> of parallelism will benefit you.
>

--
http://www.freelists.org/webpage/oracle-l