Oracle Scratchpad

June 23, 2011


Filed under: Indexing,Oracle,Performance,Tuning — Jonathan Lewis @ 10:54 am BST Jun 23,2011

Here’s a video of the presentation that I did as the keynote for the Turkish Oracle Users’ Group conference earlier on this year. Recording courtesy of the people at Formspider. The camera has some difficulty focusing on the screen so it trys to refocus quite frequently, but it’s worth putting up with that for the sound track.

The topic is about tuning a two-table join, and the video is about an hour long (so there’s a lot more that could be  said on the topic, and one day I might do a half  day “masterclass” on it).

Addendum (Aug 2019)

It’s inevitable that bits of this presentation will be out of date after 8 years. The first thing I noted that needed to be changed is the detail about changing the index that supports the priimary key. In the video I describe creating the new index then dropping a foreign key constraint, then the primary key, then recreating the primary key using the new index, then recreating the foreign key.  This is far more work than is needed, as discussed in a blog note I wrote 3 years later.



  1. We were glad to see you at TROUG Day 2011. It was a great presentation. Hope to see you again in Turkey.

    Comment by Gokhan Atil — June 23, 2011 @ 11:04 am BST Jun 23,2011 | Reply

  2. Unfortunately I was not in Turkey at that time, but I have watched this video via online, so I love it and I like to thank you for sharing your valuable experience with us.

    Comment by Yunus SIMSEK — June 23, 2011 @ 2:52 pm BST Jun 23,2011 | Reply

  3. Thanks for sharing that .

    Comment by Greg — June 23, 2011 @ 4:53 pm BST Jun 23,2011 | Reply

  4. Good presentation, and hope your new coming book Oracle Core: Essential Internals for Troubleshooting covering it though.

    Comment by Bunditj — June 23, 2011 @ 5:39 pm BST Jun 23,2011 | Reply

  5. Very interesting presentation! Thank you very much.
    But could you please explain trick with dept_no=432? And could you please provide some reference on information about how subquery result cache works (I mean Oracle version before 11g).

    Comment by YURI — June 23, 2011 @ 8:18 pm BST Jun 23,2011 | Reply

  6. The presentaion was great as always. I was very glad to spend a little time with you Jonathan!

    Comment by Kamran Agayev A. — June 24, 2011 @ 5:57 am BST Jun 24,2011 | Reply

  7. Excellent presentation.
    How many Oracle instances are on your laptop :-O

    Comment by Darryl Griffiths — June 24, 2011 @ 11:59 am BST Jun 24,2011 | Reply

  8. OK, I’m going to have to ask.
    Why no terminal version of 10g? (go easy)

    Comment by Darryl Griffiths — June 26, 2011 @ 4:22 pm BST Jun 26,2011 | Reply

    • Darryl,

      Nothing sinister or exciting; I just forgot that I ought to do the upgrade. I need two have two versions of 10.2 because of the significant histogram change across the 3/4 break – but I’m less worried about changes from 4 to 5.

      Comment by Jonathan Lewis — June 30, 2011 @ 6:13 am BST Jun 30,2011 | Reply

    • Darryl,

      Thanks to your question I’ve finally got around to doing two upgrades – then shifted everything on to a new laptop so that now I can run the four most important versions of Oracle simultaneously.

      Comment by Jonathan Lewis — July 4, 2011 @ 8:09 pm BST Jul 4,2011 | Reply

  9. Amazing presentation. Thanks for sharing

    Comment by Pablo Mazzei — June 27, 2011 @ 10:15 am BST Jun 27,2011 | Reply

  10. Hi jonathan,

    It’s an fantastic video.. !!
    I doubt, During the demonstration – where you have said that “order by (sub-query with sort)” while using sub-query remembers the previous result came from the main query. Finally, when have seen that demo, I observed that, as the blocks are referenced previously and might be exists in buffer due to the query would be fetched most recently used buffer from hot of the LRU ?

    Other wise i might be thinking way out different or totally in wrong path.

    Further all the best for the upcoming book and very much waiting from long time… !!

    thanking you in advance.. !!

    Comment by Pavan Kumar N — June 28, 2011 @ 6:58 am BST Jun 28,2011 | Reply

    • Pavan Kumar N,

      I think you’re asking whether the performance benefit could come from the fact that we rerun the same subquery against the same data because we have pre-sorted the inputs and therefore get a caching benefit. The answer is no (although I have pre-sorted a partial result to get this benefit with a nested loop join in the past).

      You need to read the article that I linked to in my answer to comment 5 above.

      Comment by Jonathan Lewis — June 28, 2011 @ 7:09 am BST Jun 28,2011 | Reply

  11. […] b) Tuning a two-table join by Jonathan Lewis […]

    Pingback by FREE !!! Oracle Database training video collection – internals, best practices « Istvan Stahl's Oracle blog — August 16, 2011 @ 8:50 am BST Aug 16,2011 | Reply

  12. […] talk on two-table joins (after all, all joins eventually become two-table joins). You can see a video of a similar two-table join optimization presentation that he gave to the Turkish OUG earlier …. Fantastic improvements are possible when you break things down to their simplest forms. The […]

    Pingback by A NoCOUG to Remember | The Pythian Blog — September 1, 2011 @ 6:25 pm BST Sep 1,2011 | Reply

  13. It is a great video

    Comment by sathish — September 4, 2012 @ 3:22 pm BST Sep 4,2012 | Reply

  14. Hi Jonathan,
    Great video. I feel a bit educated now. Can we have similar videos for other areas like Indexes, Storage parameters etc.

    Comment by Snehasish Das — November 1, 2013 @ 10:17 pm GMT Nov 1,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by