Hi, This is strange. i tried to couple of times and did not see any index in explain plan. Let me give a try again.
Thanks for you response. On 15 November 2016 at 15:04, vdpyatkov <[email protected]> wrote: > 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. >
