Prevent COUNT() Subquery using window functions

oracleoracle-12c

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?

Best Answer

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
;

Related Solutions

An Effective Way to Count the Number of Pages in Oracle

If the typical use pattern is to page through all the data, then all the records will eventually need to be sent to the client. In this scenario re-querying the database for every page is in-efficient. You should consider running the query once and paging through a cache on the client side.

If on the other hand the typical use pattern is to page through a very small amount of the data, then this method may be appropriate. Nick Chammas' comment on using ROW_NUMBER() is fleshed out by Tom Kyte in a Oracle Magazine article.

SELECT * FROM (
   SELECT /*+ first_rows(25) */
      your_columns,
      Row_Number() Over (Order By something unique) rn
   FROM your_tables 
   )
WHERE rn BETWEEN :n AND :m 
ORDER BY rn;

I recommend you read the whole article. Note in particular the warning to sort by something unique so that the results are consistent between pages (given static data):

You need to order by something unique for these pagination queries, so that ROW_NUMBER is assigned deterministically to the rows each and every time.

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Best Answer

Related Solutions

An Effective Way to Count the Number of Pages in Oracle

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Related Question