Here’s a little tip that might be helpful occasionally when you’re trying to work out why the optimizer transformation you were expecting isn’t appearing
If you’ve ever checked the 10053 trace (and who wants to do that for a complex query) you may have noticed lines like:
SU: SU bypassed: Remote table referenced.
So now you know that SU – Subquery Unnesting – has limitations in distributed queries.
When I first saw a line like this, it crossed my mind that it would be useful to keep a reference list of features that could be reported as bypassed, which I do through a simple unix line:
strings -a oracle | grep -i bypassed > bypassed.txt
If you need a reference for the various short codes for transformations you can find it near the top of the 10053 trace, looking like this: