Oracle Scratchpad

December 10, 2010

Quiz Night

Filed under: Execution plans,Hints,Oracle — Jonathan Lewis @ 6:19 pm BST Dec 10,2010

I have four simple (non-partitioned, non-clustered, not views, not object type – really I’m not trying to be cunning or devious here) heap tables, and write a query that joins them:

select
	/*+
		leading(t1 t2 t3 t4)
		use_hash(t2) use_hash(t3) use_hash(t4)
	*/
	count(t1.small_vc),
	count(t2.small_vc),
	count(t3.small_vc),
	count(t4.small_vc)
from
	t1,
	t2,
	t3,
	t4
where
	t2.id2 = t1.id1
and	t3.id3 = t2.id2
and	t4.id4 = t3.id3
;


Given that I’ve specified the join order and every join method, and given that there are no indexes on any of the tables, how many different execution plans are there for this query.

Update: When I say I’m not trying to be cunning or devious, it means I’m not trying to be cunning or devious – it doesn’t mean that I’m being really, really cunning and devious. Honestly, I’m just trying to make a simple but important point that isn’t terribly well known.

Update 2: And when I said simple tables I should also have said they weren’t declared parallel, weren’t going to be hinted parallel, weren’t external or remote/non-oracle, etc. etc. etc.

The Answer:

The answer to the question I was trying to ask is eight – as stated then demonstrated by Pavol Babel and explained very succinctly by Greg Rahn.

The answer to the question I actually asked is four as stated by Vyacheslav Rasskazov. In my attempts to describe the situation as clearly and simply as possible I forgot about a special case that I will comment on at the end of this note.

Going back to the “expected” answer. The key point is this:

    You have NOT defined a hash join completely until you have specified which rowsource should be used as the build table and which as the probe table – so every time you supply the use_hash() hint for a table, you should also supply the swap_join_inputs() hint or the no_swap_join_inputs() hint.

So my original query is suffering from incomplete hinting. There are three hash joins, so there should be three hints about swapping inputs or not. For example:

/*+
	leading(t1 t2 t3 t4)
	use_hash(t2) no_swap_join_inputs(t2)
	use_hash(t3) no_swap_join_inputs(t3)
	use_hash(t4) no_swap_join_inputs(t4)
*/

Since there are two possibilites for the swap/no_swap option, there are 2 x 2 x 2 = 8 possibilities in total for the execution plan – even though only one join order is examined. (If you check the 10053 trace file for this query you will find all the computation for these execution plans under one line that reads: Join order [1], there will not be a Join order[2])

Pavol did list all the different patterns of hints with their execution plans – but I’m going to do it again, stripped to the minimum output:

use_hash(t2) no_swap_join_inputs(t2)
use_hash(t3) no_swap_join_inputs(t3)
use_hash(t4) no_swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|*  3 |    HASH JOIN          |      |    70 |  1260 |     8 |
|*  4 |     HASH JOIN         |      |    70 |   840 |     5 |
|   5 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   6 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   7 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|   8 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2)    swap_join_inputs(t2)
use_hash(t3) no_swap_join_inputs(t3)
use_hash(t4) no_swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|*  3 |    HASH JOIN          |      |    70 |  1260 |     8 |
|*  4 |     HASH JOIN         |      |    70 |   840 |     5 |
|   5 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   6 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   7 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|   8 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2) no_swap_join_inputs(t2)
use_hash(t3)    swap_join_inputs(t3)
use_hash(t4) no_swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|*  3 |    HASH JOIN          |      |    70 |  1260 |     8 |
|   4 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|*  5 |     HASH JOIN         |      |    70 |   840 |     5 |
|   6 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   7 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   8 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2)    swap_join_inputs(t2)
use_hash(t3)    swap_join_inputs(t3)
use_hash(t4) no_swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|*  3 |    HASH JOIN          |      |    70 |  1260 |     8 |
|   4 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|*  5 |     HASH JOIN         |      |    70 |   840 |     5 |
|   6 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   7 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   8 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2) no_swap_join_inputs(t2)
use_hash(t3) no_swap_join_inputs(t3)
use_hash(t4)    swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|   3 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
|*  4 |    HASH JOIN          |      |    70 |  1260 |     8 |
|*  5 |     HASH JOIN         |      |    70 |   840 |     5 |
|   6 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   7 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   8 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2)    swap_join_inputs(t2)
use_hash(t3) no_swap_join_inputs(t3)
use_hash(t4)    swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|   3 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
|*  4 |    HASH JOIN          |      |    70 |  1260 |     8 |
|*  5 |     HASH JOIN         |      |    70 |   840 |     5 |
|   6 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   7 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   8 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2) no_swap_join_inputs(t2)
use_hash(t3)    swap_join_inputs(t3)
use_hash(t4)    swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|   3 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
|*  4 |    HASH JOIN          |      |    70 |  1260 |     8 |
|   5 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|*  6 |     HASH JOIN         |      |    70 |   840 |     5 |
|   7 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
|   8 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
--------------------------------------------------------------

use_hash(t2)    swap_join_inputs(t2)
use_hash(t3)    swap_join_inputs(t3)
use_hash(t4)    swap_join_inputs(t4)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |
|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |
|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |
|   3 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |
|*  4 |    HASH JOIN          |      |    70 |  1260 |     8 |
|   5 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |
|*  6 |     HASH JOIN         |      |    70 |   840 |     5 |
|   7 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |
|   8 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |
--------------------------------------------------------------

Note the extreme change in shape and apparent order of tables in the plan. Despite this the join order really is t1 -> t2 -> t3 -> t4 in every case. I’ll give a quick description of the first and last plans to explain this.

First plan (no_swap all the way):

    We build a hash table from t1 and probe it with t2 to create a result set
    As this result set is generated we build a new hash table from it
    As the result set completes we discard the hash table from t1
    We probe the result set with t3 to create a second result set
    As the second result set is generated we build a new hash table from it
    As the second result set completes we discard the hash table from the first result set
    We probe the second result set with t4 to create a third result set
    As the third result set is generated we pass the results up to the aggregation step to count the output.

It is very obvious from this description that the tables are being joined in the order we dictated.
Last plan (swap all the way)

    We build a hash table from t4
    We build a hash table from t3
    We build a hash table from t2
    We pick a row from t1 and probe the t2 hash,
    if the row “survives” we probe the t3 hash
    if the row “survives” we probe the t4 hash
    if the row “survives” we pass it up to the aggregation step to be counted.

With this description it becomes clear that, once again, the tables are being joined in the order we dictated.

Notice: the number of in-memory hash tables we have at any one instant in the first case is two and, no matter how many tables are involved in this pattern, the number of in-memory hash tables will always be two. The actual size of the two hash tables is a little unpredictable, though, and, a very crude guideline, you might expect the size to grow as more tables are joined into the result set. In the last case the number of in-memory hash tables we end up with is “N-1″ (where N is the number of tables joined). We can predict the approximate size of each hash table because it is based on the data we expect to extract from the corresponding “real” table. If you have enough memory to hold all the hash tables in memory at once (i.e. if none of the spill to disc) you will find that this join pattern is likely to be the fastest option you can produce.

Footnote: although a hash join is not fully specified unless you have also supplied an associated “swap/no swap” hint, the no_swap_join_inputs() hint didn’t become available until 10g !

Footnote 2: I’ve had this note on my draft list for nearly a year now – after answering this question on OTN. Unfortunately I think it’s a bit too late to update the thread now.

Special Case

And now we come to the point made by Vyacheslav Rasskazov. I have changed the original code to hint the eight different paths that could come from a single join order with three hash joins. But if I were limited to changing the data (technically the statistics) I would only be able to persuade the optimizer to pick four of those paths for this join order. The four paths that the optimizer till not consider are the ones which I have hinted with /*+ swap_join_inputs(t2) – i.e. the 2nd, 4th, 6th and 8th above.

As far as I can tell the built-in decision process is this: when considering a hash join, the optimizer will examine the cost of swapping the inputs if the expected volume (i.e. predicted cardinality * row length) of the second input is smaller than the expected volume of the first input. However, in the special case of the first join of the first join order, this does not happen. This is fairly easy to demonstrate by setting up a two-table example where selected content is a small number of large rows from one table and a large number of small rows from the other table – the first join order is dictated by number of rows, swapping is dictated by volume of data. I suspect, but cannot confirm, that this is an accident (i.e. bug) since I can think of no rational explanation of why this has to happen.

Since my original text only allowed for one join order (the leading() hint) the optimizer will not examine the option for swapping the (t1, t2) step of that join order – which is why Vyacheslav’s answer is the answer to the question I actually asked.

My apologies – this wasn’t intended to be a cunning trap – I simply forgot that odd little glitch when I was writing up the question.

27 Comments »

  1. Jonathan,

    CBO can always choose whether to “swap join inputs” for tables t2, t3, t4, or not. Since it has to (or at least should :-) ) start with t1, I suggest there are six different execution plans.

    Comment by Pavol Babel — December 10, 2010 @ 6:25 pm BST Dec 10,2010 | Reply

  2. I would say 6 (also): 3 * 2 * 1. But of course that’s so obvious, it must be wrong….

    Comment by Roel — December 10, 2010 @ 7:23 pm BST Dec 10,2010 | Reply

  3. “how many different execution plans are there for this execution plan

    Not being devious, eh?

    A hint is a hint and not a “specification”. The optimizer can always choose to ignore hints, so the hints are really irrelevant to the question. I don’t know off the top of my head how many ways Oracle can join four non-indexed heap tables together, so my best guess is: many (assuming the latest version of Oracle. For older versions, probably slightly-less-than-many.)

    Comment by Milo — December 10, 2010 @ 7:36 pm BST Dec 10,2010 | Reply

    • Milo,

      Thahks for spotting the error – now corrected.

      However, Oracle cannot “choose to ignore hints”. If you think you’ve got an example where Oracle ignores hints I’ll show you where you’ve made a mistake or found a bug.

      I’ve seen plenty of cases where Oracle doesn’t do as expected – but that’s not the same as “choosing to ignore a hint”.

      Comment by Jonathan Lewis — December 10, 2010 @ 8:15 pm BST Dec 10,2010 | Reply

  4. I’ll vote for there being only one plan.

    leading(t1 t2, t3 t4). I think the optimizer will ignore the comma and take the table join sequence to be t1 t2 t3 t4

    Comment by Sean — December 10, 2010 @ 7:49 pm BST Dec 10,2010 | Reply

    • Sean,

      That’s two errors I made in writing this one – the comma is a typo (but it’s one of those cases where the presence or absence of commas makes no difference). I have just deleted it, though.

      Comment by Jonathan Lewis — December 10, 2010 @ 8:19 pm BST Dec 10,2010 | Reply

    • Sean,

      there will be just one join order. And one joining order for all tables. However, it does not imply only one execution plan.

      Comment by Pavol Babel — December 10, 2010 @ 9:27 pm BST Dec 10,2010 | Reply

  5. My 2 cents.

    LEADING only tells Oracle to join t1 and t2 together and to join t3 and t4 together. The join input can be swapped. That would lead to 4 different plans
    (t1,t2)+(t3,t4),(t2,t1)+(t3,t4),(t1,t2)+(t4,t3),(t2,t1)+(t3,t4). The only join allowed is hash. So it would lead to 4 possibilities.
    The optimizer might however decide that another join-type is cheaper. But I’ll go for 4 plans.

    Comment by Arian — December 10, 2010 @ 8:14 pm BST Dec 10,2010 | Reply

  6. It appears that the answer could be vary based on the value of the _optimizer_ignore_hints parameter, and whether the database version is 10.2.0.5, 9.2.0.8, or 8.1.7.4 (I wonder if there are more possibilities in 11.2.0.2 to remove the SORT AGGREGATE operation?).

    Comment by Charles Hooper — December 10, 2010 @ 8:32 pm BST Dec 10,2010 | Reply

    • I will make a guess of 4 different execution plans on Oracle Database 11.1.0.7. The first join order and join method is well controlled T1 -> T2. Table T3 could be the first or second row source in the hash join. Table T4 could be the first or second row source in the hash join. Something like this:

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------
      | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |      |       |       |    10 (100)|          |
      |   1 |  SORT AGGREGATE       |      |     1 |    26 |            |          |
      |*  2 |   HASH JOIN           |      |     2 |    52 |    10  (20)| 00:00:01 |
      |*  3 |    HASH JOIN          |      |     2 |    38 |     8  (25)| 00:00:01 |
      |*  4 |     HASH JOIN         |      |     2 |    26 |     5  (20)| 00:00:01 |
      |   5 |      TABLE ACCESS FULL| T1   |     2 |    12 |     3  (34)| 00:00:01 |
      |   6 |      TABLE ACCESS FULL| T2   |  1000 |  7000 |     3  (34)| 00:00:01 |
      |   7 |     TABLE ACCESS FULL | T3   |     2 |    12 |     3  (34)| 00:00:01 |
      |   8 |    TABLE ACCESS FULL  | T4   |  1000 |  7000 |     3  (34)| 00:00:01 |
      ------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - access("T4"."ID4"="T3"."ID3")
         3 - access("T3"."ID3"="T2"."ID2")
         4 - access("T2"."ID2"="T1"."ID1")
       
      --------------------------------------------------------------------------------------
      | Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |      |       |       |       |  1434 (100)|          |
      |   1 |  SORT AGGREGATE       |      |     1 |    31 |       |            |          |
      |*  2 |   HASH JOIN           |      |     2 |    62 |       |  1434   (7)| 00:00:06 |
      |*  3 |    HASH JOIN          |      |     2 |    48 |       |  1432   (7)| 00:00:06 |
      |   4 |     TABLE ACCESS FULL | T3   |     2 |    12 |       |     3  (34)| 00:00:01 |
      |*  5 |     HASH JOIN         |      |  1000K|    17M|    20M|  1418   (6)| 00:00:06 |
      |   6 |      TABLE ACCESS FULL| T1   |  1000K|  8789K|       |   270   (7)| 00:00:02 |
      |   7 |      TABLE ACCESS FULL| T2   |  1000K|  8789K|       |   270   (7)| 00:00:02 |
      |   8 |    TABLE ACCESS FULL  | T4   |  1000 |  7000 |       |     3  (34)| 00:00:01 |
      --------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - access("T4"."ID4"="T3"."ID3")
         3 - access("T3"."ID3"="T2"."ID2")
         5 - access("T2"."ID2"="T1"."ID1")
      
      --------------------------------------------------------------------------------------
      | Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |      |       |       |       |  2772 (100)|          |
      |   1 |  SORT AGGREGATE       |      |     1 |    33 |       |            |          |
      |*  2 |   HASH JOIN           |      |     1 |    33 |       |  2772   (6)| 00:00:12 |
      |   3 |    TABLE ACCESS FULL  | T4   |     1 |     6 |       |     2   (0)| 00:00:01 |
      |*  4 |    HASH JOIN          |      |  1000K|    25M|    20M|  2758   (6)| 00:00:12 |
      |   5 |     TABLE ACCESS FULL | T3   |  1000K|  8789K|       |   274   (9)| 00:00:02 |
      |*  6 |     HASH JOIN         |      |  1000K|    17M|    20M|  1426   (7)| 00:00:06 |
      |   7 |      TABLE ACCESS FULL| T1   |  1000K|  8789K|       |   274   (9)| 00:00:02 |
      |   8 |      TABLE ACCESS FULL| T2   |  1000K|  8789K|       |   274   (9)| 00:00:02 |
      --------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - access("T4"."ID4"="T3"."ID3")
         4 - access("T3"."ID3"="T2"."ID2")
         6 - access("T2"."ID2"="T1"."ID1")
      

      The above example did not use any additional hints or tricks.

      The fourth execution plan would have table T3 below the hash join of T1 and T2, with table T4 above the hash join of T1 and T2.

      Comment by Charles Hooper — December 10, 2010 @ 9:44 pm BST Dec 10,2010 | Reply

      • Charles,

        You are not reading these execution plans in proper way, I’m afraid. The join order is always same in all cases provided by you. It is ALWAYS T1 -> T2 -> T3 -> T4. It is just putting some hash tables in the memory sometimes. As I already write, Oracle is sometimes just “swapping join oreder” :-)

        Comment by Pavol Babel — December 10, 2010 @ 10:02 pm BST Dec 10,2010 | Reply

      • Please disregard my guess. Depending on the direction that Jonathan is heading with this blog article (we may have already arrived at that point), I can easily generate additional execution plans by adjusting the default parallel degree (is it possible that the parallel degree could automatically change?) of tables T1, T2, T3, and T4 while not adjusting the number of rows in the tables. My memory is a bit fuzzy on this topic, but if the maximum specified parallel slaves (PARALLEL_MAX_SERVERS) is too small, Oracle may elect to create additional execution plans using fewer parallel slaves. A somewhat related article:
        http://oracle-randolf.blogspot.com/2010/07/double-trouble.html

        Without experimenting with parallel, and using the SQL statement provided by Jonathan, regardless of whether table T1 contained 1 row or 100,000,000 rows, table T1 was the build input table and table T2 (regardless if it contained 1 row or 1,000 rows) was the probe input table (the definitions of the terms may be found here: http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA434 ).

        I feel the need to clarify that when I stated “Table T3 could be the first or second row source in the hash join”, I probably should have stated instead that table T3 could be either the build input table or the probe input table in the hash join – the first table listed below “HASH JOIN” is the build input table.

        Comment by Charles Hooper — December 11, 2010 @ 2:15 am BST Dec 11,2010 | Reply

  7. Jonathan,

    Interestingly, it really seems to be more difficult question for most readers, than I’ve initially expected.

    I have also made some little arithmetic error in my first Comment, but I think correct answer is eight and here is list of (believed to be all possible) execution plans

    1.
    select
    	/*+
    		leading(t1 t2 t3 t4)
    		use_hash(t2) use_hash(t3) use_hash(t4)
                    no_swap_join_inputs (t2)  no_swap_join_inputs(t3)  no_swap_join_inputs(t4)
    	*/
    	count(t1.small_vc),
    	count(t2.small_vc),
    	count(t3.small_vc),
    	count(t4.small_vc)
    from
    	t1,
    	t2,
    	t3,
    	t4
    where
    	t2.id2 = t1.id1
    and	t3.id3 = t2.id2
    and	t4.id4 = t3.id3
    /
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 797810935
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |*  3 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |*  4 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   5 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("T4"."ID4"="T3"."ID3")
       3 - access("T3"."ID3"="T2"."ID2")
       4 - access("T2"."ID2"="T1"."ID1")
    
    
    2. 
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    swap_join_inputs (t2)  no_swap_join_inputs(t3)  no_swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2829836417
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |*  3 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |*  4 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   5 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       3 - access("T3"."ID3"="T2"."ID2")
       4 - access("T2"."ID2"="T1"."ID1")
    
    
    3.
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    no_swap_join_inputs (t2)  swap_join_inputs(t3)  no_swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4125425203
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |*  3 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  5 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       3 - access("T3"."ID3"="T2"."ID2")
       5 - access("T2"."ID2"="T1"."ID1")
    
    4.
    select
            /*+     
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    swap_join_inputs (t2)  swap_join_inputs(t3)  no_swap_join_inputs(t4)
            */      
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,     
            t2,     
            t3,     
            t4      
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 941936323
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |*  3 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  5 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       3 - access("T3"."ID3"="T2"."ID2")
       5 - access("T2"."ID2"="T1"."ID1")
    
    5. 
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    no_swap_join_inputs (t2)  no_swap_join_inputs(t3)  swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2681823313
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  4 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |*  5 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       4 - access("T3"."ID3"="T2"."ID2")
       5 - access("T2"."ID2"="T1"."ID1")
    
    6.
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    swap_join_inputs (t2)  no_swap_join_inputs(t3)  swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2202247778
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  4 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |*  5 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       4 - access("T3"."ID3"="T2"."ID2")
       5 - access("T2"."ID2"="T1"."ID1")
    
    
    7.
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    no_swap_join_inputs (t2)  swap_join_inputs(t3)  swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1344217782
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  4 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  6 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       4 - access("T3"."ID3"="T2"."ID2")
       6 - access("T2"."ID2"="T1"."ID1")
    
    8.
    select
            /*+
                    leading(t1 t2 t3 t4)
                    use_hash(t2) use_hash(t3) use_hash(t4)
                    swap_join_inputs (t2)  swap_join_inputs(t3)  swap_join_inputs(t4)
            */
            count(t1.small_vc),
            count(t2.small_vc),
            count(t3.small_vc),
            count(t4.small_vc)
    from
            t1,
            t2,
            t3,
            t4
    where
            t2.id2 = t1.id1
    and     t3.id3 = t2.id2
    and     t4.id4 = t3.id3
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 834430347
    
    ------------------------------------------------------------------------------
    | Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |	   1 |	 120 |		  |	     |
    |*  2 |   HASH JOIN	      |      |	   1 |	 120 |	  10  (20)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL  | T4   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  4 |    HASH JOIN	      |      |	   1 |	  90 |	   7  (15)| 00:00:01 |
    |   5 |     TABLE ACCESS FULL | T3   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |*  6 |     HASH JOIN	      |      |	   1 |	  60 |	   5  (20)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL| T2   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    |   8 |      TABLE ACCESS FULL| T1   |	   1 |	  30 |	   2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T4"."ID4"="T3"."ID3")
       4 - access("T3"."ID3"="T2"."ID2")
       6 - access("T2"."ID2"="T1"."ID1")
    

    Comment by Pavol Babel — December 10, 2010 @ 10:31 pm BST Dec 10,2010 | Reply

  8. I think, if we don’t use additional hints, right answer 4, because leading hint fixes t2 as inner table in join of t1 and t2

    Comment by Vyacheslav Rasskazov — December 10, 2010 @ 10:55 pm BST Dec 10,2010 | Reply

  9. Pavol Babel: +1 :)

    I attended that dispute last week. On the front row :-)

    Comment by Flado — December 11, 2010 @ 1:58 am BST Dec 11,2010 | Reply

  10. Since there are four tables there are three hash joins. With hash joins there are two “places” for a row source for a given join: the build side and the probe side. So the answer is 2*2*2=8.

    Comment by Greg Rahn — December 11, 2010 @ 7:53 am BST Dec 11,2010 | Reply

    • Greg,

      Thanks for joining in with that delightfully succinct description.

      Can you make any comment on my observations about the optimizer’s decision process regarding swapping join inputs ?

      Comment by Jonathan Lewis — December 11, 2010 @ 9:34 am BST Dec 11,2010 | Reply

  11. Jonathan,

    Great! I also forgot, that optimizer will never consider swapping join inputs of first two tables (+1 for Vyacheslav), unless SWAP. I have a theory and think there could be a reason why CBO skips this possiblity.
    In fact, when just two tables are hash-joined (or two fist tables in a join order), swapping join inputs makes no sense, at least to my mind. You can always change join order of tables with “leading” or “ordered” hint to achieve same execution plan.

    
    select
            /*+
                    leading(t1)
                    use_hash(t2)
                    swap_join_inputs(t2)
            */
            count(t1.small_vc),
            count(t2.small_vc)
        from
            t1,
            t2
       where
         t2.id2 = t1.id1
    /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4274056747
    
    ------------------------------------------------------------------------------------
    | Id  | Operation	    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |	   |	 1 |	40 |	   |  6346  (24)| 00:01:17 |
    |   1 |  SORT AGGREGATE     |	   |	 1 |	40 |	   |		|	   |
    |*  2 |   HASH JOIN	    |	   |   100M|  3814M|   216K|  6346  (24)| 00:01:17 |
    |   3 |    TABLE ACCESS FULL| T2   | 10000 |	97K|	   |   221   (1)| 00:00:03 |
    |   4 |    TABLE ACCESS FULL| T1   | 10000 |   292K|	   |  4397   (1)| 00:00:53 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T2"."ID2"="T1"."ID1")
    
    select
            /*+
                    leading(t2)
                    use_hash(t1)
            */
            count(t1.small_vc),
            count(t2.small_vc)
        from
            t1,
            t2
       where
         t2.id2 = t1.id1
    /
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4274056747
    
    ------------------------------------------------------------------------------------
    | Id  | Operation	    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |	   |	 1 |	40 |	   |  6346  (24)| 00:01:17 |
    |   1 |  SORT AGGREGATE     |	   |	 1 |	40 |	   |		|	   |
    |*  2 |   HASH JOIN	    |	   |   100M|  3814M|   216K|  6346  (24)| 00:01:17 |
    |   3 |    TABLE ACCESS FULL| T2   | 10000 |	97K|	   |   221   (1)| 00:00:03 |
    |   4 |    TABLE ACCESS FULL| T1   | 10000 |   292K|	   |  4397   (1)| 00:00:53 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T2"."ID2"="T1"."ID1")
    
    

    Clearly, there is equivalence between both plans (also the plan hash value is the same). Actually, you are changing join order of tables with SWAP_JOIN_INPUTS. It can be undrestand as not conistent behaviour. Moreover, I run this simple example on 9i (9.2.0.8).

    select
            /*+
                    leading(t1)
                    use_hash(t2)
                    swap_join_inputs(t2)
            */
            count(t1.small_vc),
            count(t2.small_vc)
        from
            t1,
            t2
       where
         t2.id2 = t1.id1
    /
    
    
    -------------------------------------------------------------------------
    | Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |     1 |    50 |     8  (25)|
    |   1 |  SORT AGGREGATE      |             |     1 |    50 |            |
    |*  2 |   HASH JOIN          |             |  2000 |    97K|     8  (25)|
    |   3 |    TABLE ACCESS FULL | T1          |  2000 | 50000 |     4  (25)|
    |   4 |    TABLE ACCESS FULL | T2          |  2000 | 50000 |     4  (25)|
    -------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T2"."ID2"="T1"."ID1")
    
    
    

    Obviously, 9i optimizer do not accept SWAP_JOIN_INPUTS hint when only two tables are joined. I have also tried it ACCEPTS hint when joining 3rd… N-th table. I would guess that behaviuor changed in Oacle 10g just because of RIGHT semi/anti joins (which were not present in 9i, of course)

    select  
          /*+
                     leading(t1) 
                     use_hash(@in1 t2) 
                     swap_join_inputs(@in1 t2)
           */
           count(t1.small_vc)
    from
            t1
    where 
            t1.id1 IN (
              select  
                     /*+ qb_name(in1) */
                    id2
              from   
                    t2
            )
    /
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2146439244
    
    --------------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |     1 |    40 |       |  4902   (1)| 00:00:59 |
    |   1 |  SORT AGGREGATE       |      |     1 |    40 |       |            |          |
    |*  2 |   HASH JOIN RIGHT SEMI|      | 10000 |   390K|   216K|  4902   (1)| 00:00:59 |
    |   3 |    TABLE ACCESS FULL  | T2   | 10000 |    97K|       |   221   (1)| 00:00:03 |
    |   4 |    TABLE ACCESS FULL  | T1   | 10000 |   292K|       |  4397   (1)| 00:00:53 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T1"."ID1"="ID2")
    

    Comment by Pavol Babel — December 11, 2010 @ 11:51 pm BST Dec 11,2010 | Reply

    • Pavol,

      Interesting detail there about 9i ignoring the hint – I hadn’t noticed that before.

      I agree with your comment about not needing to do the swap because it’s just a change of the leading table and is something you could dictate with a leading hint. However, there is a consistency thing. The swap won’t happen automatically on the FIRST join order, but it will happen on the SECOND join order which (when you have more than two tables) is just as redundant as it would be on the first join order – except that by not doing it on the first join order the optimizer may have run the entire join calculation with the first two tables in the sub-optimal order.

      Comment by Jonathan Lewis — December 13, 2010 @ 7:30 pm BST Dec 13,2010 | Reply

      • Now I have to addmit that I do not understand what you mean. Optimizer may have run calculation with first two tables in the suboptimal order, but it is not optmizer’s issue, but problem of author of the hint

        Comment by Pavol Babel — December 16, 2010 @ 8:37 am BST Dec 16,2010 | Reply

  12. Pavol,

    I had a write-ahead error while I was typing, and put “leading hint” in the first sentence when I meant “leading table”.

    Regardless of the possible confusion in what we might have been saying – I think we probably agree that the hint has either been specified in an incomplete fashion, or has been implemented in an incomplete fashion – in that there are appear to be a couple of simple strategies the optimizer could use to avoid some redundant work, but doesn’t use in every case.

    Comment by Jonathan Lewis — December 16, 2010 @ 6:45 pm BST Dec 16,2010 | Reply

  13. Jonathan,

    you are absolutely right. Thanx for interesting discussion.

    Comment by Pavol Babel — December 17, 2010 @ 12:15 am BST Dec 17,2010 | Reply

  14. Hello Jonathan,

    Does the swap_join_inputs hint change anything with regard to interpretation of “outer table” and “inner table” with regard to the pq_distribute hint?

    Comment by Naresh — September 20, 2012 @ 1:56 am BST Sep 20,2012 | Reply

  15. […] For a serial execution plan this typically means you have to identify the join order, join methods and access methods together with the point at which each predicate was applied. Generally this isn’t too difficult, although subquery pushing (and a couple of presentation bugs relating to other scalar subqueries) can cause a little confusion; and the difference between join order and the order of operation can be slightly obscured when considering hash joins. […]

    Pingback by Parallel Execution – 1 | Oracle Scratchpad — October 13, 2013 @ 7:44 pm BST Oct 13,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers