Hi,
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 couple of 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).
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 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 <
[email protected]> 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 <
> [email protected]> 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 <[email protected]>
>> 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 <[email protected]>
>> 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/
>>>
>>
