Thanks for reply. @Taras I have set QuerySqlField(index=true) on the indexing fields. @Alex, I've used the EXPLAIN with query I make to Gridgain Cache but I don't know how to verify if the indexes are invoked on not. Here is the output when I used explain with the Query:
SELECT DISTINCT M1 __C0, M1_TYP __C1 FROM (SELECT ENTRY AS A5, QS5.A1 AS A1, QS5.A4 AS A4, QS5.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTITY AS M1, TYP AS M1_TYP, ELEM AS A5, QS4.A1 AS A1, QS4.A4 AS A4 FROM RS.RS T INNER JOIN (SELECT ENTRY AS A2, QS3.A1 AS A1, QS3.A4 AS A4, QS3.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTRY AS A4, QS2.A1 AS A1, QS2.A2 AS A2, QS2.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTRY AS A3, QS1.A1 AS A1, QS1.A2 AS A2, QS1.A4 AS A4, QS1.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT A1 AS A1, M1 AS M1, M1_TYP AS M1_TYP, COALESCE(S2.ELEM, VAL2) AS A2, COALESCE(S3.ELEM, VAL3) AS A3, COALESCE(S4.ELEM, VAL4) AS A4 FROM (SELECT ENTRY AS A1, T.VAL2 AS M1, T.TYP2 AS M1_TYP, T.VAL8 AS VAL2, T.VAL6 AS VAL3, T.VAL6 AS VAL4 FROM (SELECT ELEM AS A1 FROM RS.RS T WHERE (PROP = '1oh~#has_neighbours') AND ((ENTITY = '3') AND (TYP = 5001))) QS0 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL0 = '7a~') AND ((ENTRY = QS0.A1) AND ((T.PROP2 = '1oh~#is_atom_of') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP0 = '4xm~#type') AND (T.PROP8 = '1oh~#has_double_bond'))))))) Q1 LEFT OUTER JOIN DS.DS S2 ON Q1.VAL2 = S2.LIST_ID LEFT OUTER JOIN DS.DS S3 ON Q1.VAL3 = S3.LIST_ID LEFT OUTER JOIN DS.DS S4 ON Q1.VAL4 = S4.LIST_ID WHERE A1 <> COALESCE(S4.ELEM, VAL4)) QS1 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL8 = '6o7~') AND ((T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS1.A3) AND ((T.PROP8 = '1oh~#has_charge') AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))))) QS2 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '1') AND ((T.VAL0 = '7a~') AND ((ENTRY = QS2.A4) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS3 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS3.A2) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS4 ON TRUE WHERE (PROP = '1oh~#is_atom_of') AND ((ENTITY = QS4.M1) AND (TYP = QS4.M1_TYP))) QS5 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '0') AND ((T.VAL0 = '1eg~') AND ((ENTRY = QS5.A5) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS6 LIMIT 100"; expected "ANALYZE, PLAN, SELECT, FROM, (, DELETE, UPDATE, INSERT, MERGE, WITH"; SQL statement: EXPLAIN EXPLAIN SELECT DISTINCT M1 __C0, M1_TYP __C1 FROM (SELECT ENTRY AS A5, QS5.A1 AS A1, QS5.A4 AS A4, QS5.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTITY AS M1, TYP AS M1_TYP, ELEM AS A5, QS4.A1 AS A1, QS4.A4 AS A4 FROM RS.RS T INNER JOIN (SELECT ENTRY AS A2, QS3.A1 AS A1, QS3.A4 AS A4, QS3.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTRY AS A4, QS2.A1 AS A1, QS2.A2 AS A2, QS2.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT ENTRY AS A3, QS1.A1 AS A1, QS1.A2 AS A2, QS1.A4 AS A4, QS1.M1 AS M1, M1_TYP AS M1_TYP FROM (SELECT A1 AS A1, M1 AS M1, M1_TYP AS M1_TYP, COALESCE(S2.ELEM, VAL2) AS A2, COALESCE(S3.ELEM, VAL3) AS A3, COALESCE(S4.ELEM, VAL4) AS A4 FROM (SELECT ENTRY AS A1, T.VAL2 AS M1, T.TYP2 AS M1_TYP, T.VAL8 AS VAL2, T.VAL6 AS VAL3, T.VAL6 AS VAL4 FROM (SELECT ELEM AS A1 FROM RS.RS T WHERE (PROP = '1oh~#has_neighbours') AND ((ENTITY = '3') AND (TYP = 5001))) QS0 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL0 = '7a~') AND ((ENTRY = QS0.A1) AND ((T.PROP2 = '1oh~#is_atom_of') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP0 = '4xm~#type') AND (T.PROP8 = '1oh~#has_double_bond'))))))) Q1 LEFT OUTER JOIN DS.DS S2 ON Q1.VAL2 = S2.LIST_ID LEFT OUTER JOIN DS.DS S3 ON Q1.VAL3 = S3.LIST_ID LEFT OUTER JOIN DS.DS S4 ON Q1.VAL4 = S4.LIST_ID WHERE A1 <> COALESCE(S4.ELEM, VAL4)) QS1 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL8 = '6o7~') AND ((T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS1.A3) AND ((T.PROP8 = '1oh~#has_charge') AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))))) QS2 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '1') AND ((T.VAL0 = '7a~') AND ((ENTRY = QS2.A4) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS3 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS3.A2) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS4 ON TRUE WHERE (PROP = '1oh~#is_atom_of') AND ((ENTITY = QS4.M1) AND (TYP = QS4.M1_TYP))) QS5 INNER JOIN DPH.DPH T ON TRUE WHERE (T.VAL5 = '0') AND ((T.VAL0 = '1eg~') AND ((ENTRY = QS5.A5) AND ((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS6 LIMIT 100 On Thu, Sep 29, 2016 at 11:05 PM, Alexey Kuznetsov <[email protected]> wrote: > Alex, > > it is better to give a link to docs when you are sugesting EXPLAIN > SELECT :) > > https://apacheignite.readme.io/docs/sql-queries#using-explain > > Hope this help :) > > -- > Alexey Kuznetsov > -- Thanks & Regard Manish Mishra Software Consultant, Knoldus Software, LLP
