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 <[email protected]> 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
