Hi, I would probably use org.h2.table.Table, and specially org.h2.index.Index. That way, you can ensure there are conditions on all the required columns. I don't think using the plan, or another internal API is needed.
Regards, Thomas On Friday, June 12, 2015, Nicolas Fortin (OrbisGIS) <[email protected]> wrote: > Hi, > > I don't understand all but You can track queries if you are using the > DatabaseEventListener: > > > https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/api/DatabaseEventListener.java > > Generally we use connection url to set a event listener however you may be > able to force it using another way. > > Regards, > > > > -- > Nicolas Fortin > IRSTV FR CNRS 2488 > GIS http://orbisgis.org > Spatial DB http://h2gis.org > Noise http://noisemap.orbisgis.org > > > Le vendredi 12 juin 2015 00:37:29 UTC+2, Pablo Beltran a écrit : >> >> 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 >>> >>>> 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] >>>> <javascript:_e(%7B%7D,'cvml','[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] >>>>> <javascript:_e(%7B%7D,'cvml','[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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[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.
