ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Subject Re: Affinity key in SQL execution
Date Fri, 24 Aug 2018 09:35:24 GMT
Can someone please reply to this?

On Thu, Aug 23, 2018, 8:38 PM Prasad Bhalerao <prasadbhalerao1983@gmail.com>
wrote:

> Problem is resolved now. The index type setting in cache configuration was
> incorrect. After setting DefaultDataAffinityKey.class in cache config index
> type query executed successfully.
>
> New sql is as follows. I have few questions on it.
>
> 1) Execution plan is showing 2 sqls. What does second sql (highlighted)
> indicate ?  What it merge scan?
>
> 2) The sql is using index on unitid but it not using any index for
> condition (AG__Z0.USERID = UAD__Z1.USERID).
> I tried to use index hints : USE INDEX(user_account_idx2,
> asset_group_idx2) but I got error Index "USER_ACCOUNT_IDX2" not found.
> Index user_account_idx2 is present in UserAccountData class.
>
> How can I make use of index created on userId and unitId in this case?
>
> 3) Can I create index on affinityId?
>
> 4) After making affinityId change, how can I check that SQL is going the
> single node only?
>
> 5) Do I need to include extra condition " ag.affinityId = uad.affinityId"
> in JOIN ON clause?
>
> 6) If I am using sub queries or join queries, is it necessary to write
> affinity key condition for each Cache where caluse?
>
>
>
> SELECT ag.assetGroupId,
>   ag.name
> FROM AssetGroupData ag
> JOIN USER_ACCOUNT_CACHE.UserAccountData uad
> ON (ag.userId       = uad.userId)
> WHERE ag.affinityId = ?
> AND uad.unitId      = ?
> AND uad.userRole    = 1
>
> Execution Plan:
>
> SELECT
>     AG__Z0.ASSETGROUPID AS __C0_0,
>     AG__Z0.NAME <http://ag__z0.name/> AS __C0_1
> FROM USER_ACCOUNT_CACHE.USERACCOUNTDATA UAD__Z1
>     /* *USER_ACCOUNT_CACHE.USER_ACCOUNT_IDX2: UNITID = ?2* */
>     /* WHERE (UAD__Z1.USERROLE = 83)
>         AND (UAD__Z1.UNITID = ?2)
>     */
> INNER JOIN ASSET_GROUP_CACHE.ASSETGROUPDATA AG__Z0
>     /* ASSET_GROUP_CACHE.AFFINITY_KEY: AFFINITYID = ?1 */
>     ON 1=1
> WHERE (AG__Z0.USERID = UAD__Z1.USERID)
>     AND ((UAD__Z1.USERROLE = 83)
>     AND ((AG__Z0.AFFINITYID = ?1)
>     AND (UAD__Z1.UNITID = ?2)))
>
> SELECT
>     __C0_0 AS ASSETGROUPID,
>     __C0_1 AS NAME
> FROM PUBLIC.__T0
>     /* ASSET_GROUP_CACHE."merge_scan" */
>
>
>
> Thanks,
> Prasad
>
>>
>> On Thu, Aug 23, 2018 at 2:31 PM Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I tried your suggestion but I am getting query parsing error now. I am
>>> attaching my data and data key class in this mail. Could you please help me
>>> out?
>>>
>>> SQL :  select assetGroupId, name from AssetGroupData where affinityId =
>>> ?
>>>
>>> *Exception:*
>>> javax.cache.CacheException: Failed to parse query. Column "AFFINITYID"
>>> not found; SQL statement:
>>> explain select assetGroupId, name from AssetGroupData where affinityId =
>>> ?  [42122-196]
>>>  at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
>>>  at
>>> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:615)
>>>  at
>>> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:356)
>>>
>>> Test code I am using to push data to cache:
>>>
>>> private void pushData(String cacheName,List<? extends Data> datas){
>>>
>>>   final IgniteCache<DataKey, Data> cache = ignite
>>>       .cache(cacheName);
>>>   for (Data data : datas) {
>>>     cache.put(data.getKey(), data);
>>>   }
>>> }
>>>
>>>
>>>
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Wed, Aug 22, 2018 at 10:18 PM Prasad Bhalerao <
>>> prasadbhalerao1983@gmail.com> wrote:
>>>
>>>> Ok, I tried to write generic impl to use the same key class with
>>>> different caches . That's why kept the name affinityId. The reason I am not
>>>> getting error is I have the subscriptionId in Data(value) class as well.
>>>>
>>>> So it means the affinity key field name matters. I was thinking/trying
>>>> to map the affinity column name "subscriptionId" to field "affinityId"
>>>> without keeping the field name same. Was looking in wrong direction.
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> Thanks,
>>>> Prasad
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>
>>>>
>>>> On Wed, Aug 22, 2018, 9:29 PM vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Prasad,
>>>>>
>>>>> In this case using subscriptionId in query would be a syntax error,
>>>>> because
>>>>> the name of the field is affinityId. If you use affinityId, however,
>>>>> Ignite
>>>>> will route the query to a single node. It knows that it's affinity key
>>>>> based
>>>>> on @AffinityKeyMapped annotation.
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>>>
>>>>

Mime
View raw message