Hi Anil,
I tred to reproduce your case, but got expected behavior (indexes are used).
I slightly modified your SQL for the my cache configuration:
select * from (
(select p.id, p.name from "simple_cache".Person p join table(joinId INT =
(1, 3)) i on p.id = i.joinId)
UNION
(select p.id, p.name from "simple_cache".Person p join table(name
varchar(10) = ('Name 0', 'Name 1')) i on p.name = i.name)
) order by id
The configuration was used:
<bean id="simple_cache"
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="simple_cache" />
<property name="atomicityMode" value="TRANSACTIONAL"/>
<property name="cacheMode" value="PARTITIONED" />
<property name="memoryMode" value="OFFHEAP_TIERED" />
<property name="writeSynchronizationMode" value="FULL_SYNC"/>
<property name="rebalanceMode" value="ASYNC"/>
<property name="indexedTypes">
<list>
<value>java.lang.Long</value>
<value>org.ignite.userlist.test.model.Person</value>
</list>
</property>
</bean>
And used debug H2 console[1], I got the "explain analyze" result:
SELECT
_3.ID,
_3.NAME
FROM (
(SELECT
P.ID,
P.NAME
FROM "simple_cache".PERSON P
INNER JOIN TABLE(JOINID INT=(1, 3)) I
ON 1=1
WHERE P.ID = I.JOINID)
UNION
(SELECT
P.ID,
P.NAME
FROM "simple_cache".PERSON P
INNER JOIN TABLE(NAME VARCHAR(10)=('Name 0', 'Name 1')) I
ON 1=1
WHERE P.NAME = I.NAME)
) _3
/* (SELECT DISTINCT
P.ID,
P.NAME
FROM TABLE(JOINID INT=(1, 3)) I
/++ function ++/
/++ scanCount: 3 ++/
INNER JOIN "simple_cache".PERSON P
/++ "simple_cache"."id_idx": ID = I.JOINID ++/
ON 1=1
/++ scanCount: 4 ++/
WHERE P.ID = I.JOINID)
UNION
(SELECT DISTINCT
P.ID,
P.NAME
FROM TABLE(NAME VARCHAR(10)=('Name 0', 'Name 1')) I
/++ function ++/
/++ scanCount: 3 ++/
INNER JOIN "simple_cache".PERSON P
/++ "simple_cache"."name_idx": NAME = I.NAME ++/
ON 1=1
/++ scanCount: 4 ++/
WHERE P.NAME = I.NAME)
*/
/* scanCount: 4 */
ORDER BY 1
As you can see all steps scaning only part of data (I have loaded 1000 rows)
and using index for search.
[1]: https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/IN-Query-tp8551p8987.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
