The most efficient “Bushy” execution plan for this query looks like the next one:

-----HJ-----
| |
| |
---NL--- ---NL---
| | | |
T1 T2 T3 T4

-----HJ-----
| |
| |
---NL--- ---NL---
| | | |
T1 T2 T3 T4

This plan joins T1 and T2 tables by Nested Loop using idx_t1 index, joins T3 and T4 tables by Nested Loop using idx_t3 index and finally joins results of the previous joins by the Hash Join.
But Oracle has never been able to generate such execution plan automatically. You had to rewrite this query with subqueries and bunch of hints in order to force this kind of execution plan.

The following example shows the typical execution plan that Oracle can generate:

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.