Indexing for an OR clause that references two separate columns is a
difficult task.
Does your use case tolerate an index on columns b and c only?
When I add this index to your example code:
create index IDX_TEST_4 on TEST(b,c);
I get this query plan:
SELECT
TEST.A,
TEST.A2,
TEST.B,
TEST.C
FROM PUBLIC.TEST
/* PUBLIC.IDX_TEST_4: C = 'PP'
AND B = '456'
*/
WHERE (C = 'PP')
AND ((B = '456')
AND ((A = '123')
OR (A2 = '1234')));
On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote:
>
>
> On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin <[email protected]
> <javascript:>> wrote:
>
>>
>> Try doing this:
>>
>> select * from TEST where (a = '123' and b = '456' and c='PP') or
>> (a2='1234' and b = '456' and c='PP');
>
>
> same result
>
> explain select * from TEST where (a = '123' and b = '456' and c='PP') or
> (a2='1234' and b = '456' and c='PP');
> PLAN
> <http://192.168.5.85:8082/query.do?jsessionid=1bfd56e4866af5b58a02a0da0e930c6e#>
> SELECT
> TEST.A,
> TEST.A2,
> TEST.B,
> TEST.C
> FROM PUBLIC.TEST
> /* PUBLIC.TEST.tableScan */
> WHERE ((C = 'PP')
> AND ((A = '123')
> AND (B = '456')))
> OR ((C = 'PP')
> AND ((A2 = '1234')
> AND (B = '456')))
>
>
--
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.
