Here’s a little oddity I came across in 11.2.0.4 a few days ago – don’t worry too much about what the query is trying to do, or why it has been written the way I’ve done it, the only point I want to make is that I’ve got the same plan from two different strategies (according to the baseline/outline/hints), but the plans have a difference in cost.
Here’s the definition of my table and index, with query and execution plan:
As you can see (lines 5 to 15), the optimizer has unnested the IN subquery and done a parallel tablescan of the table to gather the distinct set of values for (trx_trade_date_time,trx_xxx_id); then it has done a nested loop (using the “doubled NL” strategy of 11g) to probe the TRX table by index.
The point I want to pick up is the subquery unnesting. If you look at the “Outline Data” part of the plan you will find the hint UNNEST(@”SEL$2″ UNNEST_INNERJ_DISTINCT_VIEW) – it’s the first version of Oracle where I’ve seen an unnest hint referencing what appears to be an explantory internal view name or mechanism (this doesn’t happen in 10.2.0.5 or 11.1.0.7). The interesting thing is this, when I did my first few experiments with the code I wanted to ensure that the subquery unnested so my very first test had included the unnest hint (commented out in SQL statement above) and when it had been there I had got exactly the same plan except the Outline Data had the following TWO hints:
SEMI_TO_INNER(@”SEL$5DA710D3″ “VW_NSO_1″@”SEL$5DA710D3″)
UNNEST(@”SEL$2″ UNNEST_SEMIJ_VIEW)
Again the unnest carries a descriptive view name, suggesting that the optimizer has changed an IN subquery to an EXISTS subquery, then unnested it to a semi-join. The previous descriptive name seems to suggest that the optimizer unnested the distinct view before merging it into a join. So we’ve got to the same position by following a different code path from the previous transformation. (Incidentally, the cost for this path was slightly higher than the cost for the other path).
At some stage I ought to look at the 10053 trace to see if there is any significant difference in the way the optimiser arrives at these two paths in case there are circumstances where it might make a real difference to performance – but this is another case where I’m going to leave further investigation to anyone who’s interested. The key concern here, of course, is that there’s a slight chance that some SQL running with an explicit unnest hint (or a stored outline with an unnest hint) in an earlier version of Oracle may change its behaviour because of a change in the definition of the hint.
The only other thing I’ve done with this example is to check the plans under 12c – where only the first version of the Outline Data appeared whether or not I supplied the hint. Whether this is due to an optimizer code change or simply a coincidence of costing I don’t yet know.
