Oracle Scratchpad

August 2, 2010


Filed under: humour — Jonathan Lewis @ 6:13 pm BST Aug 2,2010

There are only three join mechanisms used by Oracle: merge join, hash join and nested loop join.

Here’s an important thought: all three join methods are nested loop joins with different startup costs.


And while I’m asking questions: what’s the perfect tense of the verb “troubleshoot”, as in :

  • I am trouble-shooting a problem
  • I was trouble-shooting a problem
  • … ?

Update 9th Aug: By a fairly convincing lead English grammar comes out as more interesting than Oracle technologies – so I’ll write up joins in my next note (or three). In the meantime, we have a new question to ask about trouble-shooting, inspired by Bix (note 11): is the verb transitive or intransitive, viz: do we simply trouble-shoot, or do we trouble-shoot an object ?

Linguistic constructs tend to go through three phases, of course: (1) nobody says that, (2) nobody with a decent education would say that, (3) everybody says it expect for a couple of old fogies. I reserve the right, therefore, to be opinionated and wrong about the use of the verb “trouble-shooting”.

Opinion 1: the verb is intranstive – you do not troubleshoot a problem, although you may do trouble-shooting on a system.

Opinion 2: there is no past participle (despite Nigel’s perfectly reasonable suggestion (note 1) of “trouble-shot”) for trouble-shooting.  Consequently the only past tenses for trouble-shooting are:

  • past progressive – I was troubleshooting
  • present perfect progressive – I have been trouble-shooting
  • past perfect progressive – I had been trouble-shooting

Trouble-shooting is a “continuous” action, even when it takes (took) place in the past, so has no “simple past” tenses.  (Alternatively, you can compare troubleshooting with those extreme irregular Latin verbs whose past participle looks nothing like their present participle, hence the simple past for “I am troubleshooting” is “I have (not) fixed it”.)

Normal service will be resumed as soon as possible.


  1. I trouble-shot a problem? Both by analogy with shooting, and with the pleasing feeling that you might actually have shot and killed said problem.

    Comment by Nigel Thomas — August 2, 2010 @ 6:37 pm BST Aug 2,2010 | Reply

  2. nested loop join: applies the “where condition” on the outer table. For each row of the result set, oracle searches the inner table by the join condition, using a FTS or index;

    hash join: applies a hash algorithm on the columns of the join condition (of the smaller table). then does the same on the bigger table and execute a… nested loop comparing the hash keys. the initial cost is the hash algorithm;

    merge join: you order both tables by the join columns… compare the top values from both tables… you take the higher one out… if they are equal, it’s a match… i guess it would be like a nested loop with a stop condition.

    am i right?

    Comment by Daniel Stolf — August 2, 2010 @ 9:16 pm BST Aug 2,2010 | Reply

  3. BTW, i agree with Nigel: i think the perfect tense for the verb troubleshoot is the same of the verb shoot :)

    Comment by Daniel Stolf — August 2, 2010 @ 9:28 pm BST Aug 2,2010 | Reply

  4. I have been troubleshooting grammatical conundrums, serially.
    Pluperfectly, I had been troubleshooting grammatical conundrums, boorishly.

    Comment by Peter Whedbee — August 2, 2010 @ 9:58 pm BST Aug 2,2010 | Reply

  5. I suppose one could troubleshoot one’s self in the foot :-)

    Comment by Connor — August 3, 2010 @ 4:26 am BST Aug 3,2010 | Reply

  6. Anyone but me troubleshooting the problem sounds perfect to me :)

    Comment by Graham Oakes — August 3, 2010 @ 8:10 am BST Aug 3,2010 | Reply

  7. Any operation on a potentially infinite set must employ a loop (or recursion, but let’s not go there). A join is an operation on two potentially infinite sets where each element of one of the sets must be searched for in the other set, so you need to process a potentially infinite set for every member of another potentially infinite set. Hence, the corresponding loops must be nested. Q.E.D.
    Disclaimer: I might be talking complete rubbish (wow, it’s been 20 years already since that course).


    Comment by Flado — August 3, 2010 @ 11:40 am BST Aug 3,2010 | Reply

  8. Well if you are a scientist, you have to put it in the most passive third person:

    The trouble-shooting of the problem was completed by the consultant.

    (Slightly-less-jokey answer: it’s slang for various forms of domain-specific forensic engineering, so somewhere in the land between null and not-yet-properly-defined)

    Comment by joel garry — August 3, 2010 @ 8:44 pm BST Aug 3,2010 | Reply

  9. NLJ: The driver outer loop is executed at most once, while the inner loop may or not be executed at all: it can return the first rows while computing the entire result set.

    MJ: each step is executed once, but before returning the first rows it must read all the inputs, because it needs to sort them

    HJ: each step is executed once. Before returning the first rows only the first step must read its input completely.

    Comment by marcov — August 6, 2010 @ 9:58 am BST Aug 6,2010 | Reply

  10. By definition – ‘JOIN’ means – to ‘look up’ a value (from set A ) in Set B.

    the basic task is to ‘look up’ which is a task basically in all 3 types of JOINS – they way Oracle ‘organise’ and ‘arrange’ the tables before starting look-up is different – that is all about ‘start up’ cost

    Comment by Bix — August 6, 2010 @ 2:32 pm BST Aug 6,2010 | Reply

  11. I am trouble-shooting a problem
    I was trouble-shooting a problem

    i just ‘think’ the above both are wrong sentences…

    I am on trouble-shooting a problem
    I am good at trouble shooting

    Comment by Bix — August 6, 2010 @ 2:40 pm BST Aug 6,2010 | Reply

  12. Opinion 1 is closest to my own, and therefore the most likely to be correct ;) “Troubleshooting a problem” = “Troubleshooting”, or “Problem-shooting” if you will. It’s the sort of vague thing you say you’re doing when you want someone to just go away reassured that you’re doing “something” about a problem. It could mean so many things that in the end it means almost nothing.

    Anyway, I’m putting “troubleshooting” on my List Of Forbiddden Words and Phrases.

    Comment by David Aldridge — August 10, 2010 @ 11:52 am BST Aug 10,2010 | Reply

  13. Actually there are at least four kinds of joins used by Oracle: Merge, Hash, Nested Loops and Cluster Joins.

    The latter are used when you join two tables that are stored in the same data blocks (clustering). When you join them in a way that each of the rows being joined share the same data block, Oracle can easily join them together without much additional efford.

    I haven’t tested this, but I guess in the execution plan the cluster join will show up as a nested loop join with a cluster access path on all but one of the tables accessed.

    Comment by Stefan Schwetschke — August 11, 2010 @ 9:19 am BST Aug 11,2010 | Reply

    • Stefan,
      Thanks for your suggestions. I was just a little careful to use the term “join mechanism” in my original statement – the cluster join isn’t an extra join mechanism: in fact, as you suggest in your final sentence, the commonest mechanism used in a cluster join is the nested loop. (Merge joins – cartesian, inevitably – can also appear).

      Example of plan:

      | Id  | Operation                    | Name           | Rows  | Bytes | Cost  |
      |   0 | SELECT STATEMENT             |                |    10 |   300 |     3 |
      |   1 |  NESTED LOOPS                |                |    10 |   300 |     3 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| INDEXED_TABLE1 |     1 |    15 |     2 |
      |*  3 |    INDEX RANGE SCAN          | IT_I1          |     1 |       |     1 |
      |*  4 |   TABLE ACCESS CLUSTER       | INDEXED_TABLE2 |    10 |   150 |     1 |
      Predicate Information (identified by operation id):
         3 - access("T1"."SMALL_VC"='0000001000')
         4 - filter("T2"."ID"="T1"."ID")

      The two tables are in the same index cluster the ID column is the cluster key and the join is on the cluster key. Note how we have a special access method (table access cluster) but, as you assumed, a standard join mechanism (nested loop).

      Comment by Jonathan Lewis — August 12, 2010 @ 7:48 am BST Aug 12,2010 | Reply

  14. [...] Joins – MJ Filed under: Execution plans,Performance — Jonathan Lewis @ 5:50 pm UTC Aug 15,2010 The final join mechanism in my “all joins are nested loop joins” argument is the Merge Join – a join mechanism that depends on both its row sources being pre-sorted on the join columns. ((For a quick reference list of URLs to all three articles in turn, see: Joins.)) [...]

    Pingback by Joins – MJ « Oracle Scratchpad — September 6, 2010 @ 1:12 pm BST Sep 6,2010 | Reply

  15. [...] Comments Joins – MJ « Oracle Scratchpad on JoinsScalability Conflict « Oracle Scratchpad on HeisenbergFragmentation 1 « Oracle [...]

    Pingback by Joins – HJ « Oracle Scratchpad — September 6, 2010 @ 1:17 pm BST Sep 6,2010 | Reply

  16. […] This is part one of my thesis that “all joins are nested loop joins – it’s just the startup overheads that vary”; there will be a note on “Joins – HJ” and “Joins – MJ” to follow. (For a quick reference list of URLs to all three articles in turn, see: Joins.) […]

    Pingback by Joins – NLJ | Oracle Scratchpad — June 5, 2014 @ 7:58 am BST Jun 5,2014 | Reply

  17. […] written notes about the different join mechanisms in the past – but such things are always worth revisiting, so here’s an accumulated […]

    Pingback by Hash Joins | Oracle Scratchpad — June 5, 2014 @ 8:26 am BST Jun 5,2014 | 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: Logo

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 3,910 other followers