0

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?

Trev
  • 23
  • 2

1 Answers1

0

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
Patrick H
  • 653
  • 6
  • 14
  • This kind of worked. I am also experimenting now with lateral joins, which I found while researching cross apply. I say kind of because the explain plan for inline looks a lot better, but the optimizer changed how it is accessing parts of everythingElse, which seems to make the query worse overall. Most of these parts have no direct relation to inline, so I think the optimizer is just being silly. – Trev Jul 25 '18 at 17:45