hello, actually it was in an update, I have 5 fields, each field updated by a lookup in another table. This lookup query had to be done using a OR. I could split the update in 2 so it is now using index.
However I think it would be really the best if H2 could manage it. For instance sqlite is using 2 indices. note: ...another wish is to have the CTE. SQLite also has this functionality. I miss it when I am using H2. Best regards, Sylvain On Thu, Jul 30, 2015 at 6:02 PM, Thomas Mueller < [email protected]> wrote: > Hi, > > H2 currently uses only one index. What you could try is using "union": > > select * from TEST where a = '123' and b = '456' and c='PP' > union > select * from TEST where a2='1234' and b = '456' and c='PP' > > Ideally, H2 should automatically do that for you. But well, right now, it > doesn't > > Regards, > Thomas > > > On Thursday, July 30, 2015, Steve McLeod <[email protected]> wrote: > >> 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]> >>> 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. >> > -- > 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. > -- 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.
