Re: Parallel querying with partitioned table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 23 Jun 2007 21:09:07 +0100
Message-ID: <prSdnahLLcV-4ODbnZ2dnUVZ8q6unZ2d@bt.com>

This is not a trivial question.

In principle:

A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition.

Unfortunately, this is NOT strictly true. It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins.

You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message 
news:f1Zai.12091$4Y.9270_at_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 Sat Jun 23 2007 - 15:09:07 CDT