Re: IN Query

[Anil]

HI,

i am still seeing no index used. Can you verify the below query please?

explain select * from (

( select * from Person p join table(joinId varchar(10) =
('anilkd1','anilkd2')) i on p.id = i.joinId)
UNION
(select * from Person p join table(name varchar(10) = ('Anil1', 'Anil5')) i
on p.name = i.name)

) order by id

and explain plan -

[[SELECT
    _0._KEY AS __C0,
    _0._VAL AS __C1,
    _0.NAME AS __C2,
    _0.ID AS __C3,
    _0.COMPANYID AS __C4,
    _0.JOINID AS __C5
FROM (
    (SELECT
        P._KEY,
        P._VAL,
        P.NAME,
        P.ID,
        P.COMPANYID,
        I.JOINID
    FROM "person-map".PERSON P
    INNER JOIN TABLE(JOINID VARCHAR(10)=('anilkd1', 'anilkd2')) I
        ON 1=1
    WHERE P.ID = I.JOINID)
    UNION
    (SELECT
        P._KEY,
        P._VAL,
        P.NAME,
        P.ID,
        P.COMPANYID,
        I.NAME
    FROM "person-map".PERSON P
    INNER JOIN TABLE(NAME VARCHAR(10)=('Anil1', 'Anil5')) I
        ON 1=1
    WHERE P.ID = I.NAME)
) _0
    /* (SELECT
        P._KEY,
        P._VAL,
        P.NAME,
        P.ID,
        P.COMPANYID,
        I.JOINID
    FROM "person-map".PERSON P
   *     /++ "person-map".PERSON.__SCAN_ ++/*
    INNER JOIN TABLE(JOINID VARCHAR(10)=('anilkd1', 'anilkd2')) I
        /++ function: JOINID = P.ID ++/
        ON 1=1
    WHERE P.ID = I.JOINID)
    UNION
    (SELECT
        P._KEY,
        P._VAL,
        P.NAME,
        P.ID,
        P.COMPANYID,
        I.NAME
    FROM "person-map".PERSON P
       * /++ "person-map".PERSON.__SCAN_ ++/*
    INNER JOIN TABLE(NAME VARCHAR(10)=('Anil1', 'Anil5')) I
        /++ function: NAME = P.ID ++/
        ON 1=1
    WHERE P.ID = I.NAME)
     */
ORDER BY 4], [SELECT
    __C0 AS _KEY,
    __C1 AS _VAL,
    __C2 AS NAME,
    __C3 AS ID,
    __C4 AS COMPANYID,
    __C5 AS JOINID
FROM PUBLIC.__T0
    /* "person-map"."merge_scan" */
ORDER BY 4]]

Cache configuration :

CacheConfiguration<String , Person> pCache = new
CacheConfiguration<>("person-map");
pCache.setIndexedTypes(String.class, Person.class);
pCache.setBackups(0);
pCache.setCacheMode(CacheMode.PARTITIONED);
pCache.setCopyOnRead(false);
pCache.setSwapEnabled(true);
pCache.setOffHeapMaxMemory(100);
pCache.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);


public class Person{
@QuerySqlField(index = true)
private String name;
@QuerySqlField(index = true)
private String id;
@QuerySqlField
private String companyId;
private String value;

// removed the getters and setters
}

SqlFieldsQuery sqlQuery = new SqlFieldsQuery("explain select * from ((
select * from Person p join table(joinId varchar(10) =
('anilkd1','anilkd2')) i on p.id = i.joinId) UNION (select * from Person p
join table(name varchar(10) = ('Anil1', 'Anil5')) i on p.name = i.name))
order by id");

List<List<?>> all = testMap.query(sqlQuery).getAll();

Thanks