Well, it looks like it is achievable by using the Parser -> collect all the conditions recursively (conditionAndOr lef,right) -> getting the tableName for each condition and its left and right columns (expressions, values, etc). Hence the Explain command and the Plan were not the best approach. Sorry but I'm not yet too much familiar with the API furthermore a lot of methods to explore parsed queries are not public... Anyway thanks.
2015-06-11 18:47 GMT+02:00 Pablo Beltran <[email protected]>: > The new attached picture (explain-plan_2.png) may help to understand it > better. it's an screenshot of a query against two plugged tables. I've > added some conditions (where clause) for each table. The execution plan is > really awesome as the /* function */ comments show how the conditions > affect to each table. > > With the plan I'm able to figure out that the query will perform well > against the 3rd party system because each table includes values for the > required columns. Otherwise it should/might be aborted. > > thanks. > > > > 2015-06-11 18:25 GMT+02:00 Pablo Beltran <[email protected]>: > >> Surely my question was not good and it brought some confusion. >> >> I need to access to the internal API because I'm using the H2 engine with >> plugged tables from a 3rd party system (not a database). Then I capture the >> users' queries just before invoke the 3rd party system and populate the >> plugged tables with the fetched data from the 3rd party system.... and some >> of those queries must be aborted if the users do not use the "indexes" in >> the right way. >> >> Note: "indexes" are not indexes in the strict sense (defined on some >> columns of a table). They are columns that users MUST include in the where >> clause because are required by the 3rd party system API, but the users are >> free for not doing it. Hence, If I was able to get the Plan I could analyze >> the tables and the columns used by the users to filter data and validate >> them against the scheme and abort potential full scans against the 3rd >> party system, etc The best approach to achieve it is the execution plan as >> it knows the execution order for the tables a well as the columns used as >> filters for each table. >> >> I tried to resolve it by analyzing the query syntax with the Parser: get >> the all the conditions etc. but it is a weak way and pretty difficult due >> the complexity of the SQL syntax, so I discarded this approach, even more >> after i saw that the Explain command is able to do such job much better. >> >> >> Could you help please? >> >> Thanks, >> Pablo >> >> 2015-06-11 18:08 GMT+02:00 Thomas Mueller <[email protected]>: >> >>> Hi, >>> >>> I don't understand, you just call st.execute("explain select * from >>> table"). No need to use the internal API. >>> >>> Regards, >>> Thomas >>> >>> >>> On Thursday, June 11, 2015, Pablo Beltran <[email protected]> wrote: >>> >>>> Hi, >>>> >>>> i would like to get the same text jist like the H2 Web Console when a >>>> plan is explained (Pls, see the attached picture), but I did not get it yet >>>> unfortunately. >>>> >>>> >>>> >>>> Connection conn = .... >>>> >>>> >>>> Statment st = con.createStatment("SELECT * FROM TABLE"), >>>> st.executeQuery(); >>>> >>>> .... >>>> >>>> >>>> JdbcConnection jdbcConnection = (JdbcConnection) conn; >>>> Session session = (Session) jdbcConnection.getSession(); >>>> Command current = session.getCurrentCommand(); //this method has been >>>> added to the Session class. >>>> Parser parser = new Parser(session); >>>> Prepared prepared = parser.prepare(current.toString()); //SELECT * FROM >>>> TABLE >>>> Explain exp = new Explain(session); >>>> exp.setCommand(prepared); >>>> >>>> and next?? >>>> >>>> I've tried several things from here but without too much success. >>>> >>>> Any idea? >>>> >>>> Thanks in advance! >>>> Pablo. >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/h2-database. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "H2 Database" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/h2-database/QQpyLqXHHtU/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
