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
>
