Closed GoogleCodeExporter closed 9 years ago
This feature has been added to the roadmap at
http://www.h2database.com/html/roadmap.html under "Issue 196: Function based
indexes". Patches are welcome of course!
Original comment by thomas.t...@gmail.com
on 8 May 2010 at 1:17
By the way a workaround is to use a computed column:
http://www.h2database.com/html/features.html#computed_columns
Original comment by thomas.t...@gmail.com
on 8 May 2010 at 1:18
One way to implement this would be to copy and modify
org.h2.index.PageBtreeIndex so that it stored computed column(s).
Actually, it looks like the changes to the optimiser are a lot trickier than
the changes to the storage classes.
Original comment by noelgrandin
on 7 Mar 2011 at 12:01
I hope this could be added 'on top' of the current features instead of having
to change the low level index mechanism.
How to do that: for the index expression, add a hidden "computed column" and
index this one. About computed columns, see:
http://www.h2database.com/html/features.html#computed_columns
> the changes to the optimizer are a lot trickier
Yes, the optimizer would need to be improved. The easiest way is probably to
compare the SQL snipped of the expression with the SQL snipped of the index.
Such a mechanism is already used in one case: when trying to match ORDER BY
columns with select expressions, so that the expression only needs to be
calculated once. It's actually required for one case:
-- legal:
SELECT DISTINCT ID+10 FROM TEST ORDER BY ID+10;
-- illegal:
SELECT DISTINCT ID+10 FROM TEST ORDER BY ID+11;
-- PostgreSQL: for SELECT DISTINCT, ORDER BY expressions must appear in select
list
-- H2: Order by expression "(ID + 11)" must be in the result list in this case
-- HSQLDB: invalid ORDER BY expression
-- Apache Derby and MySQL allows it, but I think that's a bug
Anyway, that's where H2 already matches sub-expressions (Query.initOrder).
Original comment by thomas.t...@gmail.com
on 11 Mar 2011 at 12:30
Original issue reported on code.google.com by
nicolas....@gmail.com
on 6 May 2010 at 8:46