Mainish, Your EXPLAIN query has EXPLAIN word twice (in the very beginning) and thus is not synctactically correct.
2016-09-30 15:26 GMT+03:00 Manish Mishra <[email protected]>: > 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 >
