Parallel querying with partitioned table

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 10 Jun 2007 16:19:34 -0500
Message-ID: <f1Zai.12091$4Y.9270@newssvr19.news.prodigy.net>

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. Received on Sun Jun 10 2007 - 16:19:34 CDT