Nested queries

[dixie09]

Hi, I need some help with a query with 2 layers of nested sub-query. Here's an outline of the query. Problem is each nested query takes < 15 mins to run when run individually but when executed together the query takes a few hours! Any ideas how to improve query performance?

(outer query
(nested query 1 -- derives columns a,b
(nested query 1-1)
(nested query 1-2)
) nq1,
(nested query 2 -- derives columns c,d
(nested query 2-1)
(nested query 2-2)
) nq2,
table1,
table2
) oq1

[dixie09]

Think I fixed the issue. One of the nested queries was running a full table scan. I'm not sure why it was doing that as it doesnt run a fts when run individually. The nested query is actually built on a view so I dont know if that was the issue. Any ideas? (In the outline above the query was "nested query 1-2".)

[jnjulian]

Think I fixed the issue. One of the nested queries was running a full table scan. I'm not sure why it was doing that as it doesnt run a fts when run individually. The nested query is actually built on a view so I dont know if that was the issue. Any ideas? (In the outline above the query was "nested query 1-2".)

I'd have to see the actual code to identify what might cause a FTS in the query plan. But I suspect either one of two things:

1) The outer query has a left/right outer join between tables/nested queries that would require a FTS to complete, or
2) You have a Cartesian join somewhere in your outer query.

You could check the V$SQL view for your query and see what the QUERY_REWRITE did. When using a nested query using a view... the optimizer will sometimes rewrite the outer query (if you have the your DB configured for QUERY_REWRITE) by substituting the view code into the query, then finding what it thinks is a better query syntax. While this produces the same result set, often times it changes the query execution plan. Grab the SQL that actually ran and do an explain plan on it and see if it differs from the explain plan run against your query before the QRW transform.

These would be the most obvious things I could think of. Seeing the code might shed some light on it, though.

As an aside: You could also consider creating a materialized view of your data from this complex query - let it "run for hours" overnight, then query the results in the morning - the response time for your final query should be a lot faster... assuming you can live without real-time reporting.

Regards,

~JJ