Streamlining joins in hierarchy

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.



  • Options

    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:


    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.

  • Options
    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file