Can someone please reply to this? On Thu, Aug 23, 2018, 8:38 PM Prasad Bhalerao <prasadbhalerao1...@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 < >> prasadbhalerao1...@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 < >>> prasadbhalerao1...@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.kuliche...@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.kuliche...@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/ >>>>> >>>>
