You could also implement a transitive closure as a table, as a materialized view, possibly via a trigger. This can be advantageous for performance in many cases. I think of such a table as effectively acting like an index. It would be nice if the database provided such a hierarchical index type, but AFAIK most don't, so such an approach is sometimes unavoidable when scalability of hierarchical queries is a concerns. FWIW, I've done this for an app that had to work both with Postgresql and Sql Server (though it wasn't with JOOQ).
On Wednesday, September 11, 2013 5:26:43 AM UTC-4, Nicholas Ustinov wrote: > > Thank you for sharing experience! > Have you ever experienced troubles with recursive CTEs in Postgres or > MSSQL? > I worked only with Oracle's connect by and it works good enough, but I > have no experience with scalability of recursive CTEs in Postgres. > > Nikolay > > среда, 11 сентября 2013 г., 12:58:02 UTC+4 пользователь Ben Hood написал: >> >> Hey Nkolay, >> >> On Tue, Sep 10, 2013 at 5:41 PM, Nicholas Ustinov <[email protected]> >> wrote: >> > So at all: does JOOQ support CTE (it may be useful in oracle too for >> other >> > reasons than hierarchical queries), recursive CTE or another syntax for >> > hierarchical queries in other databases than Oracle? (for now I need >> > PostgreSQL and MSSQL Server) >> >> FWIW we had a requirement to support cross database hierarchical >> queries with JOOQ. We ended up taking a closure tree approach, which >> doesn't take advantage of any native DB support for hierarchical >> queries and forced us to redesign the the layout of the affect tables. >> But it is portable and appears to scale (at least for our workloads). >> >> HTH, >> >> Ben >> > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
