CODEWITHSUNDEEP
sqldatabaserecursionrecursive-queryhana
Ruchir Saxena
Ruchir Saxena

Reputation: 39

Alternative of HIERARCHY_ANCESTORS function in SAP HANA

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.

Upvotes: 2

Views: 1341

Answers (1)

Remi sap
Remi sap

Reputation: 164

Consider the following hierarchy

enter image description here

The leaves and their ancestors are:

| NODE_ID | ANCESTOR |
| ------- | -------- |
| A010    |    A     |
| A10     |    A     |
| B       |    B     |
| C0      |    C     |

To get there, you can use annother hierarchy function to produce the same behavior.

create COLUMN TABLE TSTHIERARCHY(
  parent_id nvarchar(32),
  node_id   nvarchar(32),
  val       integer
);

INSERT INTO TSTHIERARCHY VALUES (NULL,'A',1);
INSERT INTO TSTHIERARCHY VALUES ('A','A0',2);
INSERT INTO TSTHIERARCHY VALUES ('A','A1',2);
INSERT INTO TSTHIERARCHY VALUES ('A0','A01',3);
INSERT INTO TSTHIERARCHY VALUES ('A01','A010',4);
INSERT INTO TSTHIERARCHY VALUES ('A1','A10',3);
INSERT INTO TSTHIERARCHY VALUES (NULL,'B',1);
INSERT INTO TSTHIERARCHY VALUES (NULL,'C',1);
INSERT INTO TSTHIERARCHY VALUES ('C','C0',2);

WITH t1 AS (SELECT * FROM HIERARCHY( SOURCE TSTHIERARCHY ))
SELECT leaves.NODE_ID, ancestors.NODE_ID AS ANCESTOR
FROM (
    select NODE_ID, HIERARCHY_ROOT_RANK
    FROM t1 WHERE HIERARCHY_RANK NOT IN (SELECT DISTINCT HIERARCHY_PARENT_RANK FROM t1)
) leaves
INNER join
(   SELECT NODE_ID, HIERARCHY_RANK
    FROM t1 WHERE HIERARCHY_RANK = HIERARCHY_ROOT_RANK 
) ancestors
on (leaves.HIERARCHY_ROOT_RANK=ancestors.HIERARCHY_RANK)

Does that work ?

A simpler query exists to display the ancestor for all hierarchy members, not just leaf nodes:

SELECT NODE_ID,HIERARCHY_RANK,
       FIRST_VALUE(NODE_ID) OVER (PARTITION BY HIERARCHY_ROOT_RANK ORDER BY hierarchy_level) AS ancestors
FROM HIERARCHY( SOURCE TSTHIERARCHY )

Upvotes: 0

Related Questions