Apologies in advance as I cannot share the original query as the information is proprietary. I hope to be able to explain as much as possible to make it useful.
One of our application had converted from Sybase to Oracle Database 18c Enterprise Edition Release 18.0.0.0.0. This (obfuscated) query used to run in under 2 seconds in Sybase but was taking between 15 to 25 seconds to execute in Oracle. The 25 second result tended to be on the first execution with subsequent executions running in between 15 to 17 seconds. I put aside the 25 second result as being attributable to the creation of the access path and focused on the 15 to 17 second executions.
Original query
SELECT
many,
columns,
from,
all,
the,
joined,
tables
FROM customer_order ao
full outer join sales_notes sn
ON ao.order_id = sn.order_id
full outer join order_contacts poc
ON ao.order_id = poc.order_id
full outer join contact c
ON poc.contact = c.contact,
site_order so
full outer join order_info oori
ON so.site = oori.site
full outer join tax_info t
ON so.site = t.site
AND so.order_id = t.order_id
full outer join order_notes son
ON so.site = son.site
full outer join tax_notes tn
ON so.site = tn.site
full outer join tax_order_detail tod
ON so.site = tod.site
full outer join tax_sub_order tso
ON so.site = tso.site
full outer join other_vax_order ovo
ON so.site = ovo.site
address a
WHERE so.site IS NOT NULL
AND so.site = 'some value here'
AND so.site = ao.site
AND so.acct_order_id = ao.acct_order_id
AND so.site = a.site
ORDER BY
so.site,
so.acct_order_id
;
All of these tables were below 100k rows with no complexity of LOB columns or anything out of the ordinary. The cost estimate for this SQL was 365,047,136.
The filter predicates in the where clause on the so.site will always result in less than 10 rows. Site is the primary index on the site table. All the other tables have an index to satisfy the join columns. The tunning advisor reported that no better plan was available.
In order to understand what was occurring, I ran an explain on the below SQL:
SELECT
many,
columns,
from,
all,
the,
joined,
tables
FROM customer_order ao,
site_order so,
address a
WHERE so.site IS NOT NULL
AND so.site = 'some value here'
AND so.site = ao.site
AND so.acct_order_id = ao.acct_order_id
AND so.site = a.site
ORDER BY
so.site,
so.acct_order_id
;
The optimizer estimated the cost at 9 and the query ran consistently in less than a second. I then experimented with adding the other FULL OUTER JOIN tables one by one. The addition of any two tables as FULL OUTER JOINS did not impact the query cost. Additionally, these other two tables would be joined as NESTED LOOP joins. The addition of a third table as a FULL OUTER JOIN would raise the query cost significantly and also use a hash join.
After some trial and error, I added in the optimizer hint:
Modified query
SELECT /*+ USE_NL(so ovo) */
many,
columns,
from,
all,
the,
joined,
tables
FROM customer_order ao
full outer join sales_notes sn
ON ao.order_id = sn.order_id
full outer join order_contacts poc
ON ao.order_id = poc.order_id
full outer join contact c
ON poc.contact = c.contact,
site_order so
full outer join order_info oori
ON so.site = oori.site
full outer join tax_info t
ON so.site = t.site
AND so.order_id = t.order_id
full outer join order_notes son
ON so.site = son.site
full outer join tax_notes tn
ON so.site = tn.site
full outer join tax_order_detail tod
ON so.site = tod.site
full outer join tax_sub_order tso
ON so.site = tso.site
full outer join other_vax_order ovo
ON so.site = ovo.site
address a
WHERE so.site IS NOT NULL
AND so.site = 'some value here'
AND so.site = ao.site
AND so.acct_order_id = ao.acct_order_id
AND so.site = a.site
ORDER BY
so.site,
so.acct_order_id
;
And the cost went down to around 70 with the query consistently performing in under two seconds. I just happened to be testing the order of joins with the table aliased as OVO and hence the hint lists OVO. Almost any other combination results in the same (i.e. improved) access path.
