Prevent COUNT() Subquery using window functions

by 0xCAFEBABE   Last Updated August 14, 2019 13:06 PM

A developer colleague of mine has asked me to optimize statements for him. I have had a brief introduction to current level SQL technology and functionality and have the feeling that especially the window functions could help me.

The query in question is currently like this:

SELECT
T1.FIELD1,
T1.FIELD2
...
(SELECT COUNT (T2.FIELD1) FROM OUTER_TABLE_NAME T2 WHERE T2.FOREIGN_KEY = T1.PRIMARY_KEY) AS COMMENTS
FROM T1
;

This forces the database to run the subquery for each record from the primary table T1, which is generally slow. A better way would be if there was a window function that could express the same. However, it must not multiply the records from the primary table T1.

I thought about something like this:

SELECT
T1.FIELD1,
T2.FIELD2
...
COUNT(T2.FOREIGN_KEY) OVER (PARTITION BY T2.FOREIGN_KEY)
FROM T1
LEFT OUTER JOIN T2 ON T2.FOREIGN_KEY = T1.KEY
;

It needs to be an OUTER JOIN as to not filter records from the primary table T1. However, this way would multiply the records from the primary table T1 for every matching record from the secondary table T2.

Is there a way to express the count() using either window functions or any other non-proprietary methodology in Oracle 12.2?



Related Questions



Altering the location of Oracle-Suggested Backup

Updated July 03, 2016 08:02 AM

Ways to Schedule Rman backups

Updated February 24, 2017 12:06 PM

EMCA insists that it cannot connect to the service

Updated August 28, 2017 14:06 PM

Password for Oracle Database 18c user “system”

Updated August 02, 2018 06:06 AM