Re: oracle-l Digest V3 #208

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 20 Jul 2006 08:18:24 +0100


Christian has given you the answer you need.

Personally I find it irritating that dbms_xplan fails to
show the partition_id column from the plan_table.
If it did, it would be obvious when you had a partition-wise
join because you would see only one partition id repeated
through the plan.

In your example with 8 hash partitions you could get a
non-partition wise join if Oracle decides that the effective
build table size is small enough to use the broadcast
distribution instead of the hash distribution.  To get a
view of the way this affects the plan, you could use the
pq_distribute hint.
   pq_distribute(hash_test_c, hash, hash)
should give you the partition wise join

   pq_distribute(hash_test_c, broadcast, none)
should give you the non-partition wise join

(You may need to include /*+ ordered use_hash(hash_test_c) */
to stop Oracle from switching table orders for the broadcast
case.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

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

From: ryan_gaffuri@xxxxxxxxxxx
Subject: how to tell if you are getting a partition wise join
Date: Wed, 19 Jul 2006 20:27:36 +0000
Content-Type: text/plain Content-Transfer-Encoding: 8bit I am doing some simple tests. I made 2 copies of dba_objects that are hash partitioned with 8 partitions. called hash_test_a and hash_test_c I added a small attachment with the two plans. I want to make sure it formats properly.
I ran this query:
explain plan for
   select /*+ parallel (a,8) parallel (b,8) */ count(*)
   from hash_test_a a, hash_test_b b
  where a.object_id = b.object_id
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? 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

This is the same query accept it is restricted to just the first partition. The plan changes considerably. This is what I find interesting: PX RECEIVE PX SEND HASH PX BLOCK ITERATOR

This happens before each table scan. This tells me(if I am reading it correctly) that 1 parallel slave is reading a partition and then queing it up for the query coordinator. However, how come I don't see that when I do not restrict the partition and read the whole table?

I read the partition docs and it does not give examples of partition wise join explain plans.


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

Re: oracle-l Digest V3 #208

Other related posts: