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 <
> [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/
>>>>
>>>
