Alternative of HIERARCHY_ANCESTORS function in SAP HANA

[ruchirsaxena]

Hi,

I wrote a piece of code in HANA and used the HIERARCHY_ANCESTORS function. However, it started giving me all sorts of OOM (Out of Memory) issues. I raised an OSS & provided all the details just to realize that it was an unidentified issue of the standard SAP function which will now be rectified in the next release.

Now I do not want to delve on the HIERARCHY_ANCESTORS function, as I already lost a month communicating through the OSS.

What I need is an alternative, which does not include any loops. Basically, I need the ancestors of all the leaf nodes (HIERARCHY_TREE_SIZE = 1) identified from the HIERARCHY function, without using loops. There can be over around 35k leaf nodes.

The data size is over 80k records, and I have tried looping over the same earlier, and it severely degrades the performance, timing out after a certain point. My need is to wrap it up in less than 30s, like the HIERARCHY_ANCESTORS functions would.

I can perhaps create a recursive function to fetch all the ancestors of 1 leaf ID. But how would I use it inside a SQL query, so that the same function can then fetch the ancestors of all the requisite IDs?

Any help is appreciated from HANA POV.

Thank you.