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

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? I assume I get: 1. 8 slaves scanning 8 partitions in hash_a 2. 8 slaves scanning 8 partitions in hash_b

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

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?

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.

thanks.
-------------- Original message -------------- From: "Christian Antognini" <Christian.Antognini_at_trivadis.com>

> Ryan
>
> >My expectation before running this query was:
> >1. Each slave will scan a partition
> >2. Then eight slaves will each do a partition wise hash join.
> >3. The results will queued. The query coordinator will dequeue the results.
> >
> >Is this a correct interpretation?
>
> Yes.
>
> >I am not sure if the plan says that.
> >
> >This is the second plan:
> > explain plan for
> > select /*+ parallel (a,8) parallel (b,8) */ count(*)
> > from hash_test_a partition(HASH_TEST_A01) a, hash_test_c
> partition(HASH_TEST_A01) b
> > where a.object_id = b.object_id
>
> A join is executed using a full partition-wise join if the partition row source
> appears before the join row source. According to your attachment this is your
> case. If you use, for example, 6 slaves, you should see a different execution
> plan.
>
> >I read the partition docs and it does not give examples of partition wise join
> explain plans.
>
> AFAIK the Data Warehousing Guide describes partition wise joins quite well.
>
>
> HTH
> Chris
>

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