How to figure out a query with too many subqueries [closed]
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:
- Replace old style joins with new style ones
- 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.
精彩评论