FWIW I can confirm that Oracle will use two indexes in a situation
like this. Plan for OR query:
Filter Predicates Access Predicates Plan
SELECT STATEMENT ALL_ROWS
5 5 5 CONCATENATION
2 2 2 TABLE ACCESS BY INDEX ROWID TABLE X.FOO
1 1 "LEFT"<1234 1 INDEX RANGE SCAN
INDEX X.FOO#LEFT
4 LNNVL("LEFT"<1234) 4 4 TABLE ACCESS BY INDEX ROWID
TABLE X.FOO
3 3 "RIGHT"<1234 3 INDEX RANGE SCAN INDEX
X.FOO#RIGHT
Plan for corresponding UNION query:
Filter Predicates Access Predicates Plan
SELECT STATEMENT ALL_ROWS
6 6 6 SORT UNIQUE
5 5 5 UNION-ALL
2 2 2 TABLE ACCESS BY INDEX ROWID TABLE
X.FOO
1 1 "LEFT"<1234 1 INDEX RANGE SCAN
INDEX X.FOO#LEFT
4 4 4 TABLE ACCESS BY INDEX ROWID TABLE
X.FOO
3 3 "RIGHT"<1234 3 INDEX RANGE SCAN
INDEX X.FOO#RIGHT
Regards,
--knut
On Tue, May 1, 2012 at 10:31, Thomas Mueller
<[email protected]> wrote:
> Hi,
>
> It is true, H2 doesn't use two indexes for the single query (the one
> without union), as described in the documentation at
> http://h2database.com/html/performance.html#storage_and_indexes
>
> I just tested that Apache Derby doesn't use two indexes either (the same as
> H2).
>
> I didn't test, but I guess other databases with a more advanced
> optimizer (such as PostgreSQL) do use two indexes for this case. But
> I'm not sure. I guess some databases can use two indexes, and some can
> not.
>
> Regards,
> Thomas
>
> --
> You received this message because you are subscribed to the Google Groups "H2
> Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
