How to reproduce the Explain command output programmatically?

[Pablo Beltran]

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.

[Noel Grandin]

Just call executeQuery with EXPLAIN PLAN SELECT.......

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Pablo Beltran]

Hi Noel,

Unfortunately queries are written by clients and I cannot control them. My intention is intercept the users' queries at the server (this is fully controlled by me) and intercept the queries,  build the execution plan for each one and abort some of them under some circumstances which I guess I can manage properly if I was able to get the plan.

Thanks,

Pablo.

--
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 h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

I don't understand, you just call st.execute("explain select * from table"). No need to use the internal API.

Regards,

Thomas

--

[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

--
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 h2-database...@googlegroups.com.

[Pablo Beltran]

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.

[Pablo Beltran]

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.

[Nicolas Fortin (OrbisGIS)]

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

[Thomas Mueller]

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

[Pablo Beltran]

I've got the point just now. Hopefully, I had understood it well some weeks ago. I wrote a brute index and performed some optimizations from inside the fullScan index according to the where conditions.I did not declare any other index on the table columns. As mentioned, I just realized now that I should declare the indexes on the tables and write specific optimizations for each one.Thanks Thomas.