Skip to Content
0
Jul 02, 2020 at 09:03 AM

Result Cache of View not Used in Join

44 Views Last edit Jul 01, 2020 at 07:44 PM 3 rev

I have created the following view:

CREATE VIEW "test_view" AS (SELECT "SUBJECT_FID", count (DISTINCT "RECORD_SID") AS cnt FROM "DIM_RECORD" GROUP BY "SUBJECT_FID")

Then I have added a cache

ALTER VIEW "test_view" ADD STATIC CACHE RETENTION 120 OF "SUBJECT_FID", sum("CNT")

If I run this query ...

SELECT "SUBJECT_FID", sum("CNT") FROM "test_view" GROUP BY "SUBJECT_FID" WITH HINT(RESULT_CACHE)

the cache is used:

However if I run this query (for testing I do a self-join)...

SELECT * FROM 
(
SELECT "SUBJECT_FID", sum("CNT") FROM "test_view" GROUP BY "SUBJECT_FID"
) a
INNER join
(
SELECT "SUBJECT_FID", sum("CNT") FROM "test_view" GROUP BY "SUBJECT_FID"
)b
ON a."SUBJECT_FID"=b."SUBJECT_FID"
 WITH HINT(RESULT_CACHE)

then the cache does not seem to be used:

How can I tell HANA to use the cache while executing this join? Thanks a lot already! I am happy to provide more details if needed.

Attachments

cache-used.png (17.0 kB)
cache-not-used.png (21.5 kB)