Hi,
You can check it in 1.6.0 release or checkout master and check on it.
On Tue, Nov 15, 2016 at 5:05 PM, Anil <anilk...@gmail.com> wrote:
> 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
>>
>
>
--
Vladislav Pyatkov
