Hi, It's a good question. The optimizer currently only knows a few ways to use an index: range conditions (<=, >=, >, <, =, between), in(list), in(select), like (which is converted to a range condition), is null, is not null, min(x), max(x), count(*).
I wonder if there is a "generic" way to support index usage. It's won't be that easy I guess. For the MVStore it would be nice to have a good solution (so that the r-tree can be used), but we might end up "hardcoding" it within H2. Could you tell me what are possible operations that could use a (spatial or other) index, and how such an index could be used? Regards, Thomas On Tue, May 21, 2013 at 1:27 PM, Nicolas Fortin (OrbisGIS) < [email protected]> wrote: > I know function table but this lead to modifying the sql request and it is > not in standard. > > Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit : >> >> See the section title "Using a function as a table" here >> http://h2database.com/html/**features.html#user_defined_**functions<http://h2database.com/html/features.html#user_defined_functions> >> >> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote: >> >> Discussion about user defined index on OTHER type. >> >> In spatial SQL the following request on an indexed geometry column would >> use spatial index: >> >> select * from spatialTable where ST_Intersects(the_geom, >>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1)); >>> >> >> Usage of ST_Intersects filter the_geom field by using BoundingBox stored >> index of the spatialTable, then the method is applied on filtered rows. >> >> Can I do this by creating my own TableEngine ? There is an easier way >> without modifying the sql request ? Maybe a way to let special kind of >> function to alter the execution plan ? >> >> Thanks for your support. >> >> -- >> 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 [email protected]. >> >> Visit this group at >> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >> . >> For more options, visit >> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >> . >> >> >> >> >> -- > 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?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
