Streamlining joins in hierarchy

[Deleted User]

Having hierarchical schema A-B-C-D, where each table one level "deeper" has pre-join projection that joins all previous tables. When inserting into such table, it will not reuse existing pre-join projections one level "upper" (with only one additional join) but it will make all joins again.

-@gregakespret

Po_Hong_1

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.

Joanne_Qian

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