{"id":73299,"date":"2012-07-17T17:43:17","date_gmt":"2012-07-17T17:43:17","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/ansi-outer-2\/"},"modified":"2021-07-14T13:07:54","modified_gmt":"2021-07-14T13:07:54","slug":"ansi-outer-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/ansi-outer-2\/","title":{"rendered":"ANSI Outer 2"},"content":{"rendered":"<p>A comment on a recent post of mine pointed me to\u00a0<a href=\"https:\/\/forums.oracle.com\/forums\/thread.jspa?threadID=2394716&amp;tstart=0\"><em><strong>a question on the OTN SQL and PL\/SQL Forum<\/strong><\/em><\/a>\u00a0where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.<\/p>\n<p>The core of the problem is this: there is a three table join which does a hash join involving an index fast full scan on a particular index; when you add a fourth table to the join this fast full scan turns into a full tablescan for no obvious reason. Here are the queries, with the plans that I got when running 10.2.0.3. (My final plan is slightly different from the plan shown on OTN \u2013 I have a right outer hash join to the last table where the OP had a nested loop outer \u2013 but the difference is not significant).<\/p>\n<p><a title=\"ANSI Outer 2 - Oracle Scratchpad\" href=\"http:\/\/jonathanlewis.wordpress.com\/2012\/07\/16\/ansi-outer-2\/\" target=\"_blank\">Continue reading&#8230;<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A comment on a recent post of mine pointed me to\u00a0a question on the OTN SQL and PL\/SQL Forum\u00a0where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables&hellip;<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73299","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73299","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73299"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73299\/revisions"}],"predecessor-version":[{"id":91751,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73299\/revisions\/91751"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73299"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73299"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}