Re: Parallel querying with partitioned table

Dereck L. Dietz wrote:
> I'm working on designing a history table which will be partitioned as
> follows on the SYS_DATE column (system date of the row modification):
>
> HIST_MBR_ADDR
> HIST_MBR_ADDR_Q2_2007
> HIST_MBR_ADDR_Q3_2007
> HIST_MBR_ADDR_Q4_2007
> HIST_MBR_ADDR_Q1_2008
> HIST_MBR_ADDR_OVERFLOW
>
> The index (SYS_DATE, USER_ACTION, USERNAME ) will be local and partitioned
> in partitions similar to the table partitions. Each partition will be in a
> separate tablespace (both data and index).
>
> My question is this: Would there be ANY benefits from defining the table as
> parallel with a degree equal to the number of partitions or would I be best
> served by just defining it as a partitioned table and letting the Oracle
> optimizer decide whether to use parallel querying or not?
>
> This will be in an Oracle 10g 10.2.0.3 environment running under Windows
> 2003 Server, 8 GB of ram and 8 CPUs.
>
> Thanks.

If I understand that you are planning to put table segment and index segments into separate tablespaces ... my question would be why?

If you are planning to parallelize based on the number of partitions, again, I would ask why? And suggest you read the docs. You don't create more CPUs when you create more partitions.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org