Hello Bala, I have started using JOOQ for sql query buidling. It would be a nice idea > to , if the query builder validates the indexes by connecting the database > and auto arranges the column order in join clause/where caluse based on > database indexes. >
I have some doubts about the usefulness and reliability of such a feature, for two reasons: 1. Users expect jOOQ to render SQL the way they intend. If jOOQ were to transform queries and to re-arrange projections / predicates / table expressions, the outcome might be quite hard to foresee. For this reason, of course, this would then have to be an optional feature - controlled through a setting. I don't completely object to query transformation per se. jOOQ already does quite a bit of transformation to simulate functions / SQL clauses that are not available in some SQL dialect. 2. Sophisticated databases distinguish themselves by doing precisely what you request: Optimising queries through query transformation. Tom Kyte from Oracle published an interesting presentation called "Metadata Matters" on that subject. He explains how constraints and indexes can influence the CBO's decisions. I have linked to that presentation from my blog, here: http://blog.jooq.org/2011/11/25/how-schema-meta-data-impacts-oracle-query-transformations/ Now, formal query transformation is very complex by itself. Finding the right rules to optimise queries on a per-database-dialect and on a per-database-version basis is even harder. jOOQ only has very limited information about the underlying database and database schema. As soon as you start adding "plain SQL", for instance, jOOQ would have to: - render the complete SQL statement and bind all values - parse it again to form the AST of the actual SQL - compile a canonical execution plan (or let the database do that) - start optimising that plan with a CBO like you suggest - transform the SQL according to the findings of the previous optimisation - re-render the SQL to the database Now there are two ways to implement the above: 1. Open source it in jOOQ. I wouldn't expect any reliable results from the above, so I personally prefer that my Oracle instance does all that for me :-) 2. Close source this (on top of jOOQ, which would remain open, of course) and make a lot of money. :-) > Any how have built custom solution for DB2, since JOOQ supports great deal > of database , if this feature is added would be better. > What do you mean by "custom solution"? Anyway, if you have a lot of DB2 optimisation knowledge, you can implement that in a higher-level API, built on top of jOOQ and go for option 2... > Also i am interested in coding for this group. > Thanks for your interest! What kind of contributions were you thinking of? Note, a good place to get some initial information from, is this page: http://www.jooq.org/community.php Then, there is a special milestone on GitHub for user contribution candidates. These are feature requests that do not really require a deep understanding of jOOQ's internals: https://github.com/jOOQ/jOOQ/issues?milestone=20 Feel free to suggest your own ideas as well Cheers Lukas
