Hello,
I would like to know if this is possible to use an index in the following
case:
select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';
given the table:
create table TEST (
a varchar(50) NOT NULL,
a2 varchar(50) NOT NULL,
b varchar(50) NOT NULL,
c varchar(50) NOT NULL
);
create index IDX_TEST_1 on TEST(a,a2,b,c);
create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);
insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
explain select * from TEST where (a = '123' or a2='1234') and b = '456' and
c='PP';
PLAN
<http://192.168.5.85:8082/query.do?jsessionid=18ea287c17996120ff0aa31eed0272a9#>
SELECT
TEST.A,
TEST.A2,
TEST.B,
TEST.C
FROM PUBLIC.TEST
/* PUBLIC.TEST.tableScan */
WHERE (C = 'PP')
AND ((B = '456')
AND ((A = '123')
OR (A2 = '1234')))
whereas
explain select * from TEST where a = '123' and b = '456' and c='PP';
PLAN
<http://192.168.5.85:8082/query.do?jsessionid=18ea287c17996120ff0aa31eed0272a9#>
SELECT
TEST.A,
TEST.A2,
TEST.B,
TEST.C
FROM PUBLIC.TEST
/* PUBLIC.IDX_TEST_2: C = 'PP'
AND A = '123'
AND B = '456'
*/
WHERE (C = 'PP')
AND ((A = '123')
AND (B = '456'))
how to make this query (the first one) using an index? is this possible?
Best regards,
Sylvain
--
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.
