RE: how to tell if you are getting a partition wise join

Ryan

>so if i am joining 2 hash partitioned tables with 8 partitions each.
>
>I have a query as follows:
>
>select /*+ parallel (a,8) parallel (b,8) */ count(*)
>from hash_a a, hash_b b
>where a.pk = b.pk;
>
>How many threads does oracle start?

The DOP you specify with the hint (or with any other method) is used to set the so-called "intra operational parallelism". This is the parallelism used by a single operation like a FTS or an order by.

Now, if the execution plan is not very simple, e.g. if it contains joins, there is an "inter operational parallelism" as well. This is basically when two sets of slave processes exchange data.

With Oracle, when you use parallel operations, the maximum number of slave processes is twice the DOP.

>I assume I get:
>1. 8 slaves scanning 8 partitions in hash_a
>2. 8 slaves scanning 8 partitions in hash_b

If the system can provide them, yes.

>Does Oracle start a 17th thread to be the master thread? So I have 17 total threads?

In a parallel operation the coordinator is the back-end process associated with the session which started the parallel operation. Therefore there are, potentially, 17 processes working on the query.

>How does the partition wise hash join work? Does oracle generate 8 more slaves to perform the hash
>joins while the slaves that are scanning the partition are working or do the table scans get
>'queued' and then as the slaves finish scanning a partition it works on the partition wise hash
>join?

As I wrote before the maximum is twice the DOP. To do the join Oracle uses a so-called PARALLEL_COMBINED_WITH_PARENT operation. Basically this means that you have a set of slave processes that does, for example, a FTS and at the same time performs the join as well.

>the datwarehouse docs describe partition wise joins well, they do not give an example(or atleast I
>didn't see it) of an explain plan for a partition wise join.

Execution plans are in the Tuning Guide...

19.8.3 Examples of Partial Partition-wise Joins: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#sthref1910

19.8.4 Examples of Full Partition-wise Joins: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#sthref1916

HTH
Chris

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