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

  • From: ryan_gaffuri@xxxxxxxxxxx
  • To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • Date: Thu, 20 Jul 2006 12:58:06 +0000

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@xxxxxxxxxxxx> 

> 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 
> 

Other related posts: