Streamlining joins in hierarchy

Comments

• Po_Hong_1

  April 2014

  Not sure about what the exact question is.  I have recently blogged about a design (based on flattening out one or many parent-child tree structures) that can handle
  multiple hierarchies in one table by generating a new "flattened" table (in an automated script) which explicitly lays out all the hierarchical relationship on a given row.  Please take a look:

  
  http://www.vertica.com/2014/03/24/can-vertica-climb-a-tree/

  
  For obvious reasons, I used very simple examples in my blog. But more complex scenarios have
  been implemented in real life and the design has proven to be effective.  The basic idea is to translate complex tree navigation, predicate
  filtering and aggregation requirements into relatively simple SQL queries.

  Your case seems to indicate a known level or tree depth (which would otherwise need to be determined programmatically) and it makes things simpler.  I would consider
  building a "flattened/de-normalized" table that encodes the relationship in the way specified in my blog.  On the technical side, it will
  involve a bunch of joins and UNION ALLs.  Keep in mind that each node (no matter where it is located on the hierarchy) will become a row in the new "flattened" table.

  0
• Joanne_Qian

  December 2014

  Thanks you for the blog. I think it is good flattened/de-normalized structure. But could you throw more lights on how the script will be to build it? Is it possible to make it inside of Vertica or it need to be supported by host programming language? And also the script of query all nodes below node= 88063633, it is could be done within vertica or it need to be supported by host programming language? Thanks

  1