I am running a query which fetches first 500 rows and it takes more than 80 minutes to complete. The cost of this query is 2.5 million timerons. When I modify the query and run it again the cost of the new query is 40 million timerons but this one finishes in 1 minute and gets me the same result set.
The first query uses an Order By clause in an inner query to get me top first row. The modified one uses a max() function in the inner query to get the top first row.
When I compare the explains for both the queries the second one is high in cost, cpu and IO but still runs a lot faster.
The question is - is this an expected scenario? The cost is high but the query runs much faster?
Thx for your inputs .
Jay
The first query uses an Order By clause in an inner query to get me top first row. The modified one uses a max() function in the inner query to get the top first row.
When I compare the explains for both the queries the second one is high in cost, cpu and IO but still runs a lot faster.
The question is - is this an expected scenario? The cost is high but the query runs much faster?
Thx for your inputs .
Jay