Hi,
> 1. Department object has an id and affinity key on the name(it
doesn't make sense in the real world). Does ignite create an index for
affinity keys as well ? I don't see the affinity key index in the list
below.
You need to configure affinity key with annotations a little bit
differently, please check docs [1]: AffinityKeyMapped must be part of cache
key, not value object. AffinityKeyMapped doesn't affect if it is used for
field in cache value.
So, it should look like
DepartmentKey {
@QuerySqlField
@AffinityKeyMapped
private final String deptName;
...
}
and then cache will be like IgniteCache<DepartmentKey, Department>.
> 2. When I use the default index explicitly in IndexQuery, it throws an
exception that the index name doesn't exist.
Which index name did you use?
> So by default it uses primary key index or hash index when* IndexQuery* is
executed with _KEY in criteria* ?*
They PrimaryKey index executes if criteria consist of single _KEY criteria.
[1]
https://ignite.apache.org/docs/2.11.1/data-modeling/affinity-collocation#configuring-affinity-key
On Wed, Jun 22, 2022 at 9:49 PM Surinder Mehra <redni...@gmail.com> wrote:
> Thanks for the reply. I have another question on the affinity key index(if
> any). When we enable sql on ignite, it by default creates an index on the
> primary key(highlighted in red below). We have created a custom index on
> deptId(highlighted in red).
>
> 1. Department object has an id and affinity key on the name(it
> doesn't make sense in the real world). Does ignite create an index for
> affinity keys as well ? I don't see the affinity key index in the list
> below.
> 2. When I use the default index explicitly in IndexQuery, it throws an
> exception that the index name doesn't exist. So by default it uses primary
> key index or hash index when* IndexQuery* is executed with _KEY in
> criteria* ?*
>
> [image: image.png]
> [image: image.png]
>
> On Wed, Jun 22, 2022 at 9:35 PM Николай Ижиков <nizhi...@apache.org>
> wrote:
>
>> SELECT * FROM SYS.INDEXES
>>
>> 22 июня 2022 г., в 18:38, Surinder Mehra <redni...@gmail.com> написал(а):
>>
>> Hi,
>> We have defined indexes on sql enabled ignite cache and are able to see
>> indexes being used while querying.
>> sqline *!indexes* also shows those indexes in output. But we cant see
>> default index created by ignite on *primary key *and *affinity key*.
>> We would like to use index on key and affinity key to get results sorted
>> accordingly.
>>
>> For example, in official docs link below, Is it possible to use the above
>> mentioned default indexes instead of ORG_SALARY_IDX ?
>> How can we get more details about these indexes which are not shown in
>> sqlline commands.
>>
>>
>>
>> https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries
>>
>>
>> QueryCursor<Cache.Entry<Integer, Person>> cursor = cache.query(
>> new IndexQuery<Integer, Person>(Person.class, "ORG_SALARY_IDX")
>> .setCriteria(eq("orgId", 1), gt("salary", 1000)));
>>
>>
>>
