Hi, once I use a table function in a join it gets very slow. If I run the individual queries 1 and 2 they need <1s. However the full query needs >10s, even though the individual queries only return 140 rows!
SELECT
*
FROM
(
-----------------QUERY 1 ------------
SELECT
"PROJECT_SID",
"SITE_SID",
COUNT("DATA_POINT_SID") AS "CNT_DATA_POINTS"
FROM
CNT_DPTS ('9999-12-01')
WHERE
"PROJECT_SID" = '1146AE1E18534ECAC81F99FAB0688B9D'
GROUP BY
"PROJECT_SID",
"SITE_SID"
-----------------
) cntdpts
INNER JOIN (
-----------------QUERY 2 ------------
SELECT
"PROJECT_SID",
"SITE_SID",
COUNT("TYPE_ID") AS "CNT_PDS"
FROM
CNT_PDS ('9999-12-01',
3)
WHERE
"PROJECT_SID" = '1146AE1E18534ECAC81F99FAB0688B9D'
GROUP BY
"PROJECT_SID",
"SITE_SID"
-----------------
) cntqueries ON
cntdpts."PROJECT_SID" = cntqueries."PROJECT_SID"
AND cntdpts."SITE_SID" = cntqueries."SITE_SID"
--WITH HINT(RESULT_CACHE)