0

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?

1
SELECT
T1.FIELD1,
T2.FIELD2,
...
NVL(T2.COMMENTS, 0) AS COMMENTS
FROM T1
LEFT OUTER JOIN 
(SELECT FOREIGN_KEY, COUNT (FIELD1) AS COMMENTS FROM OUTER_TABLE_NAME GROUP BY FOREIGN_KEY) T2
ON T2.FOREIGN_KEY = T1.PRIMARY_KEY
;
3
  • Wouldn't this drop all records from T1 where there is no records in T2? With the original query, they would just be listed with 0 in the subquery column.
    – 0xCAFEBABE
    Aug 14 '19 at 13:16
  • @0xCAFEBABE No, T2.COMMENTS would return null for those because of the outer join, but I added an NVL() to take care of that. Aug 14 '19 at 13:18
  • Although NVL() is proprietary, there's a function like that for most other systems, so I'll accept that solution. Thank you.
    – 0xCAFEBABE
    Aug 14 '19 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.