Thank you. You saved my time. May i know working ignite version ? i see it is issue in h2 db itself.
Thanks. On 15 November 2016 at 19:30, Vladislav Pyatkov <vldpyat...@gmail.com> wrote: > Hi Anil, > > You are right. I have checked this on not released version, but in 7.0.0 > indexes are not used by some strange reason. > You can check the case in master or previous version, it worked earlier > and will work after (but 7.0.0 have bug). > > On Tue, Nov 15, 2016 at 2:36 PM, Anil <anilk...@gmail.com> wrote: > >> HI, >> >> i am still seeing no index used. Can you verify the below query please? >> >> explain select * from ( >> >> ( select * from Person p join table(joinId varchar(10) = >> ('anilkd1','anilkd2')) i on p.id = i.joinId) >> UNION >> (select * from Person p join table(name varchar(10) = ('Anil1', 'Anil5')) >> i on p.name = i.name) >> >> ) order by id >> >> and explain plan - >> >> [[SELECT >> _0._KEY AS __C0, >> _0._VAL AS __C1, >> _0.NAME AS __C2, >> _0.ID AS __C3, >> _0.COMPANYID AS __C4, >> _0.JOINID AS __C5 >> FROM ( >> (SELECT >> P._KEY, >> P._VAL, >> P.NAME, >> P.ID, >> P.COMPANYID, >> I.JOINID >> FROM "person-map".PERSON P >> INNER JOIN TABLE(JOINID VARCHAR(10)=('anilkd1', 'anilkd2')) I >> ON 1=1 >> WHERE P.ID = I.JOINID) >> UNION >> (SELECT >> P._KEY, >> P._VAL, >> P.NAME, >> P.ID, >> P.COMPANYID, >> I.NAME >> FROM "person-map".PERSON P >> INNER JOIN TABLE(NAME VARCHAR(10)=('Anil1', 'Anil5')) I >> ON 1=1 >> WHERE P.ID = I.NAME) >> ) _0 >> /* (SELECT >> P._KEY, >> P._VAL, >> P.NAME, >> P.ID, >> P.COMPANYID, >> I.JOINID >> FROM "person-map".PERSON P >> * /++ "person-map".PERSON.__SCAN_ ++/* >> INNER JOIN TABLE(JOINID VARCHAR(10)=('anilkd1', 'anilkd2')) I >> /++ function: JOINID = P.ID ++/ >> ON 1=1 >> WHERE P.ID = I.JOINID) >> UNION >> (SELECT >> P._KEY, >> P._VAL, >> P.NAME, >> P.ID, >> P.COMPANYID, >> I.NAME >> FROM "person-map".PERSON P >> * /++ "person-map".PERSON.__SCAN_ ++/* >> INNER JOIN TABLE(NAME VARCHAR(10)=('Anil1', 'Anil5')) I >> /++ function: NAME = P.ID ++/ >> ON 1=1 >> WHERE P.ID = I.NAME) >> */ >> ORDER BY 4], [SELECT >> __C0 AS _KEY, >> __C1 AS _VAL, >> __C2 AS NAME, >> __C3 AS ID, >> __C4 AS COMPANYID, >> __C5 AS JOINID >> FROM PUBLIC.__T0 >> /* "person-map"."merge_scan" */ >> ORDER BY 4]] >> >> Cache configuration : >> >> CacheConfiguration<String , Person> pCache = new >> CacheConfiguration<>("person-map"); >> pCache.setIndexedTypes(String.class, Person.class); >> pCache.setBackups(0); >> pCache.setCacheMode(CacheMode.PARTITIONED); >> pCache.setCopyOnRead(false); >> pCache.setSwapEnabled(true); >> pCache.setOffHeapMaxMemory(100); >> pCache.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED); >> >> >> public class Person{ >> @QuerySqlField(index = true) >> private String name; >> @QuerySqlField(index = true) >> private String id; >> @QuerySqlField >> private String companyId; >> private String value; >> >> // removed the getters and setters >> } >> >> SqlFieldsQuery sqlQuery = new SqlFieldsQuery("explain select * from (( >> select * from Person p join table(joinId varchar(10) = >> ('anilkd1','anilkd2')) i on p.id = i.joinId) UNION (select * from Person >> p join table(name varchar(10) = ('Anil1', 'Anil5')) i on p.name = i.name)) >> order by id"); >> >> List<List<?>> all = testMap.query(sqlQuery).getAll(); >> >> Thanks >> > > > > -- > Vladislav Pyatkov >