There are significant downsides to nesting subqueries within subqueries in Oracle SQL:
Independence:  Subqueries are sometimes executed independently of the outer query:  For non-correlated subqueries, the subquery is executed independently from the master query, making it difficult to get the execution plan and a "whole" explain plan for the query.

Hard to Maintain:  Nested subqueries are confusing.  Even with documentation, subqueries within subqueries can make a SQL statement difficult to maintain.

Optimizer Confusion:  The Oracle optimizer does not always do a good job understanding the complexity that is inherent in nested subqueries and it is not uncommon to see sub-optimal execution plans for SQL with several levels of subquery nesting.
Remember, the purpose of SQL is to move data into computer programs where it is manipulated, and there is not need to make the SQL statements complicated.  Even a table join can be performed within a language.

All else being equal, "simple" SQL statements are easier to maintain and easier for the optimizer to always choose the "best" execution plan.   Oracle knows this fact and gives us these tools for removing nested subqueries from Oracle SQL:

Automated query re-write:  Oracle is a leader in query re-write technology   In 11g and beyond, some sub-optimal subqueries are re-written as a standard outer join with a NULL test. Oracle Materialized views are also used to pre-join tables and flatten-out complex SQL queries into a simpler form.

Subquery factoring:  Except in a few rare cases, subqueries can be necessary, but in general, it is a good practice to flatten-out nested subqueries using Oracle subquery factoring , using the SQL WITH clause or global temporary tables to removed the nested subqueries.

In sum nested subqueries allow for complex problems to be solved solely within SQL, but there are downsides to this approach, especially with regard to SQL tuning.  Remember, it is the job of SQL to process the data while it is the job of an application program to transform that data.  Wile SQL analytics provides mind-boggling opportunities for complex problem solving solely within SQL, sometimes a simpler approach is better.

For complete details on writing to the Oracle alert log, read the full tip here:

http://www.dba-oracle.com/t_sql_nested_subqueries.htm

*****************************************

Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal settings that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author. Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training. Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf 

*****************************************

There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list:

http://www.dba-oracle.com/jobs.htm