Traverse hierarchy with a tally table

Question

Traverse hierarchy with a tally table

s.duvernay 71743

SSC Veteran

Points: 212
More actions
December 2, 2018 at 1:45 pm

#369213

Hi ,
I have recently discovered the tally table concept and its usage to replace loops. Therefore I would like to use it to traverse a hierarchy and build a sort path.
I have an adjency table storing the node_id and the parent_node_id and some other data.
My problem is that I don’t know to combine the tally table and relation parent node/child node.
Hope my explanations are clear enough so that someone can help me.
Thank you very much.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Please see the following articles.
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

s.duvernay 71743 SSC Veteran Points: 212 More actions · Answer 2

Hi Jeff,
I read both article but I' have probably miss something...

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

s.duvernay 71743 - Sunday, December 2, 2018 1:45 PM
Hi ,I have recently discovered the tally table concept and its usage to replace loops. Therefore I would like to use it to traverse a hierarchy and build a sort path.I have an adjency table storing the node_id and the parent_node_id and some other data.My problem is that I donâ€™t know to combine the tally table and relation parent node/child node.Hope my explanations are clear enough so that someone can help me.Thank you very much.

Can you provide the DDL?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

s.duvernay 71743 - Monday, December 3, 2018 6:27 AM
Hi Jeff,
I read both article but I' have probably miss something...

The point of the first article is that maintaining the hierarchy through an Adjacency List is the best way to maintain it and that Nested Sets is the best way to traverse the Hierarchy. Hierarchies usually don't change as often as other data and so you don't need to constantly calculate how to traverse the hierarchy if nothing has changed. Instead, you use the technique to express the Adjacency List as Nested Sets in a very high performance manner after the hierarchy has changed so you can continue to enjoy the benefits of both an Adjacency List (ease of maintenance) and Nest Sets (nasty fast).

To be sure, using ONLY a Tally Table will have no benefit in helping to traverse a hierarchy. However, a Tally Table was used and is critical to creating the Nested Sets from the Adjacency List in a very high performance manner.

As for the second article, it does the preaggregation of the usual/most common values that you would need to traverse a hierarchy for and does so with the same high performance. Instead of traversing the hierarchy for "totals", you just SELECT them from the preaggregated table (similar to what people would call a DW table).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

s.duvernay 71743 SSC Veteran Points: 212 More actions · Answer 5

@jeff: thank you very much for your additional explanations.
@jonathan-2: if I'm still blocked I will post the DDL with some data sample.
Thanks.