I have an overly complex query that contains an inline view with a group by that I wish to try to remove for performance reasons, but I can't seem to think of a way to do so. An overly simplified representation of this query would be:
Select inline.totalValue, inline.type, everythingElse.*
From everythingElse,
(Select sum(value) totalValue, type, id
from otherTable
group by type, id) inline
Where everythingElse.id = inline.id
everythingElse only contains a small subset of the id's that are contained by inline, but it is a one to many relationship. Because inline contains a group by, the optimizer has to sum every row in the table before it can join. This makes it so this subquery represents 99% of the job's estimated cost.
Due to the fact that the select is using 2 values from inline, I don't think changing the subquery to a nested subquery, or a scalar subquery is an option. I have also considered moving the group by to encompass the entire query, but every aggregate function that I know of would cause everythingElse.* to take more time than I am saving by removing the inline view. The fastest processing one I could find was max(), but if something like any() exists, that might solve my problem.
Is there some sort of obvious solution to this that I am missing, or am I just going to have to deal with the fact that 1% of the query is getting 99% of the process time?
As long as you are using 12c or later, you could try using a cross apply. I'm not sure if it will improve your specific case or not, but it is worth a shot.
Select inline.totalValue, inline.type, everythingElse.*
From everythingElse
cross apply
(Select sum(value) totalValue, type, id
from otherTable
where everythingElse.id = otherTable.id
group by type, id) inline