How to figure out a query with too many subqueries [closed]

It's difficult to tell what开发者_如何学C is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I've been given a big query to figure out. But there are so many subqueries that it's nearly impossible.The number of subqueries is about 15-20.What do you suggest I do?

---

What I usually do when confronted with such monsters is:

1. Replace old style joins with new style ones
2. Modularize every non-trivial subquery into "virtual" tables using the with statement, for example:

with
 subquery1 as (select /*big query*/ ),
 subquery2 as (select /*big query*/ )
select *
from ...
     join subquery1
where foo in (select foo from subquery2)

At that point some patterns emerge and more often than not the query can be rewritten in a sensible way.

---

The first thing I would do is to issue an explain plan to see how the DBMS would execute the query and go from there.