Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: oracle-l Digest V3 #208

Re: oracle-l Digest V3 #208

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 Jul 2006 08:18:24 +0100
Message-ID: <012a01c6abcc$b06699f0$0600a8c0@Primary>

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_at_comcast.net
> 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.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 20 2006 - 02:18:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US