Hi, Can you run EXPLAIN for these queries so we can see the indexes that were used?
Evgenii чт, 8 нояб. 2018 г. в 21:23, Ranjit Sahu <ranjit.s...@gmail.com>: > Hi guys, > > I have the following query which is slow. Below is the explain plan. Any > clue whats going wrong ? > > > > [WARNING][client-connector-#62][IgniteH2Indexing] *Query execution is too > long [time=9649 ms*, sql='SELECT > 2018-10-31T17:09:25.071Z __Z0.ACCOUNTNUMBER __C0_0, > 2018-10-31T17:09:25.071Z __Z0.COMPANYID __C0_1, > 2018-10-31T17:09:25.071Z __Z1.SUBSNUMBER __C0_2, > 2018-10-31T17:09:25.071Z __Z1.APPLICATIONID __C0_3, > 2018-10-31T17:09:25.071Z __Z1.TYPE __C0_4, > 2018-10-31T17:09:25.071Z __Z1.STARTDATE __C0_5, > 2018-10-31T17:09:25.071Z __Z1.ENDDATE __C0_6, > 2018-10-31T17:09:25.071Z __Z1.MLANUMBER __C0_7, > 2018-10-31T17:09:25.071Z __Z1.TRIALOFFER __C0_8, > 2018-10-31T17:09:25.071Z __Z2.MATERIALNUMBER __C0_9, > 2018-10-31T17:09:25.071Z __Z2.TYPE __C0_10, > 2018-10-31T17:09:25.071Z __Z2.DESCRIPTION __C0_11, > 2018-10-31T17:09:25.071Z __Z2.INTERNALDESC __C0_12, > 2018-10-31T17:09:25.071Z __Z3.MATERIALNUMBER __C0_13, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONID __C0_14, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONNAME __C0_15, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONDESC __C0_16, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONTYPE __C0_17, > 2018-10-31T17:09:25.071Z __Z4.LINENUMBER __C0_18, > 2018-10-31T17:09:25.071Z __Z4.STARTDATE __C0_19, > 2018-10-31T17:09:25.071Z __Z4.ENDDATE __C0_20, > 2018-10-31T17:09:25.071Z __Z4.QUANTITY __C0_21, > 2018-10-31T17:09:25.071Z __Z5.BANDLOW __C0_22, > 2018-10-31T17:09:25.071Z __Z5.BANDHIGH __C0_23, > 2018-10-31T17:09:25.071Z __Z5.BASECOUNT __C0_24, > 2018-10-31T17:09:25.071Z __Z5.USERCAP __C0_25, > 2018-10-31T17:09:25.071Z __Z5.USERAVAILABLE __C0_26, > 2018-10-31T17:09:25.071Z __Z5.USERCOUNT __C0_27 > 2018-10-31T17:09:25.071Z FROM PUBLIC.ACCOUNT __Z0 > 2018-10-31T17:09:25.071Z LEFT OUTER JOIN PUBLIC.SUBSCRIPTION __Z1 > 2018-10-31T17:09:25.071Z ON __Z0.ACCOUNTNUMBER = __Z1.ACCOUNTNUMBER > 2018-10-31T17:09:25.071Z LEFT OUTER JOIN PUBLIC.MATERIALS __Z2 > 2018-10-31T17:09:25.071Z ON __Z1.MATERIALNUMBER = __Z2.MATERIALNUMBER > 2018-10-31T17:09:25.071Z LEFT OUTER JOIN PUBLIC.CLASSIFICATIONS __Z3 > 2018-10-31T17:09:25.071Z ON __Z2.MATERIALNUMBER = __Z3.MATERIALNUMBER > 2018-10-31T17:09:25.071Z LEFT OUTER JOIN PUBLIC.SUBSCRIPTIONLINES __Z4 > 2018-10-31T17:09:25.071Z ON __Z1.SUBSNUMBER = __Z4.SUBSNUMBER > 2018-10-31T17:09:25.071Z LEFT OUTER JOIN PUBLIC.BANDRANGE __Z5 > 2018-10-31T17:09:25.071Z ON __Z1.SUBSNUMBER = __Z5.SUBSNUMBER > 2018-10-31T17:09:25.071Z WHERE __Z0.ACCOUNTNUMBER = 'daaaaaaaaa'', plan= > 2018-10-31T17:09:25.071Z SELECT > 2018-10-31T17:09:25.071Z __Z0.ACCOUNTNUMBER AS __C0_0, > 2018-10-31T17:09:25.071Z __Z0.COMPANYID AS __C0_1, > 2018-10-31T17:09:25.071Z __Z1.SUBSNUMBER AS __C0_2, > 2018-10-31T17:09:25.071Z __Z1.APPLICATIONID AS __C0_3, > 2018-10-31T17:09:25.071Z __Z1.TYPE AS __C0_4, > 2018-10-31T17:09:25.071Z __Z1.STARTDATE AS __C0_5, > 2018-10-31T17:09:25.071Z __Z1.ENDDATE AS __C0_6, > 2018-10-31T17:09:25.071Z __Z1.MLANUMBER AS __C0_7, > 2018-10-31T17:09:25.071Z __Z1.TRIALOFFER AS __C0_8, > 2018-10-31T17:09:25.071Z __Z2.MATERIALNUMBER AS __C0_9, > 2018-10-31T17:09:25.071Z __Z2.TYPE AS __C0_10, > 2018-10-31T17:09:25.071Z __Z2.DESCRIPTION AS __C0_11, > 2018-10-31T17:09:25.071Z __Z2.INTERNALDESC AS __C0_12, > 2018-10-31T17:09:25.071Z __Z3.MATERIALNUMBER AS __C0_13, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONID AS __C0_14, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONNAME AS __C0_15, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONDESC AS __C0_16, > 2018-10-31T17:09:25.071Z __Z3.CLASSIFICATIONTYPE AS __C0_17, > 2018-10-31T17:09:25.071Z __Z4.LINENUMBER AS __C0_18, > 2018-10-31T17:09:25.071Z __Z4.STARTDATE AS __C0_19, > 2018-10-31T17:09:25.071Z __Z4.ENDDATE AS __C0_20, > 2018-10-31T17:09:25.071Z __Z4.QUANTITY AS __C0_21, > 2018-10-31T17:09:25.071Z __Z5.BANDLOW AS __C0_22, > 2018-10-31T17:09:25.071Z __Z5.BANDHIGH AS __C0_23, > 2018-10-31T17:09:25.071Z __Z5.BASECOUNT AS __C0_24, > 2018-10-31T17:09:25.071Z __Z5.USERCAP AS __C0_25, > 2018-10-31T17:09:25.071Z __Z5.USERAVAILABLE AS __C0_26, > 2018-10-31T17:09:25.072Z __Z5.USERCOUNT AS __C0_27 > 2018-10-31T17:09:25.072Z FROM PUBLIC.ACCOUNT __Z0 > 2018-10-31T17:09:25.072Z /* PUBLIC."_key_PK_proxy": ACCOUNTNUMBER = > 'daaaaaaaaa' */ > 2018-10-31T17:09:25.072Z /* WHERE __Z0.ACCOUNTNUMBER = 'daaaaaaaaa' > 2018-10-31T17:09:25.072Z */ > 2018-10-31T17:09:25.072Z LEFT OUTER JOIN PUBLIC.SUBSCRIPTION __Z1 > 2018-10-31T17:09:25.072Z /* PUBLIC.SUBSCRIPTION_ACCOUNTNUMBER_IDX: > ACCOUNTNUMBER = __Z0.ACCOUNTNUMBER */ > 2018-10-31T17:09:25.072Z ON __Z0.ACCOUNTNUMBER = __Z1.ACCOUNTNUMBER > 2018-10-31T17:09:25.072Z LEFT OUTER JOIN PUBLIC.MATERIALS __Z2 > 2018-10-31T17:09:25.072Z /* PUBLIC."_key_PK_proxy": MATERIALNUMBER = > __Z1.MATERIALNUMBER */ > 2018-10-31T17:09:25.072Z ON __Z1.MATERIALNUMBER = __Z2.MATERIALNUMBER > 2018-10-31T17:09:25.072Z LEFT OUTER JOIN PUBLIC.CLASSIFICATIONS __Z3 > 2018-10-31T17:09:25.072Z /* PUBLIC.CLASSIFICATIONS_MATERIALNUMBER_IDX: > MATERIALNUMBER = __Z2.MATERIALNUMBER */ > 2018-10-31T17:09:25.072Z ON __Z2.MATERIALNUMBER = __Z3.MATERIALNUMBER > 2018-10-31T17:09:25.072Z LEFT OUTER JOIN PUBLIC.SUBSCRIPTIONLINES __Z4 > 2018-10-31T17:09:25.072Z /* PUBLIC.SUBSCRIPTIONLINES_SUBSNUMBER_IDX: > SUBSNUMBER = __Z1.SUBSNUMBER */ > 2018-10-31T17:09:25.072Z ON __Z1.SUBSNUMBER = __Z4.SUBSNUMBER > 2018-10-31T17:09:25.072Z LEFT OUTER JOIN PUBLIC.BANDRANGE __Z5 > 2018-10-31T17:09:25.072Z /* PUBLIC.BANDRANGE_SUBSNUMBER_IDX: SUBSNUMBER = > __Z1.SUBSNUMBER */ > 2018-10-31T17:09:25.072Z ON __Z1.SUBSNUMBER = __Z5.SUBSNUMBER > 2018-10-31T17:09:25.072Z WHERE __Z0.ACCOUNTNUMBER = 'daaaaaaaaa' > 2018-10-31T17:09:25.072Z , parameters=[]] > > > > PLAN SELECT > __Z0.ACCOUNTNUMBER AS __C0_0, > __Z0.COMPANYID AS __C0_1, > __Z1.SUBSNUMBER AS __C0_2, > __Z1.APPLICATIONID AS __C0_3, > __Z1.TYPE AS __C0_4, > __Z1.STARTDATE AS __C0_5, > __Z1.ENDDATE AS __C0_6, > __Z1.MLANUMBER AS __C0_7, > __Z1.TRIALOFFER AS __C0_8, > __Z2.MATERIALNUMBER AS __C0_9, > __Z2.TYPE AS __C0_10, > __Z2.DESCRIPTION AS __C0_11, > __Z2.INTERNALDESC AS __C0_12, > __Z3.MATERIALNUMBER AS __C0_13, > __Z3.CLASSIFICATIONID AS __C0_14, > __Z3.CLASSIFICATIONNAME AS __C0_15, > __Z3.CLASSIFICATIONDESC AS __C0_16, > __Z3.CLASSIFICATIONTYPE AS __C0_17, > __Z4.LINENUMBER AS __C0_18, > __Z4.STARTDATE AS __C0_19, > __Z4.ENDDATE AS __C0_20, > __Z4.QUANTITY AS __C0_21, > __Z5.BANDLOW AS __C0_22, > __Z5.BANDHIGH AS __C0_23, > __Z5.BASECOUNT AS __C0_24, > __Z5.USERCAP AS __C0_25, > __Z5.USERAVAILABLE AS __C0_26, > __Z5.USERCOUNT AS __C0_27 > FROM PUBLIC.ACCOUNT __Z0 > /* PUBLIC."_key_PK_proxy": ACCOUNTNUMBER = 'daaaaaaaaa' */ > /* WHERE (__Z0.PARTNERTYPE = 'sy') > AND ((__Z0.ACCOUNTNUMBER = 'daaaaaaaaa') > AND (__Z0.COMPANYID = 'mlyl')) > */ > LEFT OUTER JOIN PUBLIC.SUBSCRIPTION __Z1 > /* PUBLIC.SUBSCRIPTION_ACCOUNTNUMBER_IDX: ACCOUNTNUMBER = > __Z0.ACCOUNTNUMBER */ > ON __Z0.ACCOUNTNUMBER = __Z1.ACCOUNTNUMBER > LEFT OUTER JOIN PUBLIC.MATERIALS __Z2 > /* PUBLIC."_key_PK_proxy": MATERIALNUMBER = __Z1.MATERIALNUMBER */ > ON __Z1.MATERIALNUMBER = __Z2.MATERIALNUMBER > LEFT OUTER JOIN PUBLIC.CLASSIFICATIONS __Z3 > /* PUBLIC.CLASSIFICATIONS_MATERIALNUMBER_IDX: MATERIALNUMBER = > __Z2.MATERIALNUMBER */ > ON __Z3.MATERIALNUMBER = __Z2.MATERIALNUMBER > LEFT OUTER JOIN PUBLIC.SUBSCRIPTIONLINES __Z4 > /* PUBLIC.SUBSCRIPTIONLINES_SUBSNUMBER_IDX: SUBSNUMBER = __Z1.SUBSNUMBER */ > ON __Z1.SUBSNUMBER = __Z4.SUBSNUMBER > LEFT OUTER JOIN PUBLIC.BANDRANGE __Z5 > /* PUBLIC.BANDRANGE_SUBSNUMBER_IDX: SUBSNUMBER = __Z1.SUBSNUMBER */ > ON __Z1.SUBSNUMBER = __Z5.SUBSNUMBER > WHERE (__Z0.ACCOUNTNUMBER = 'daaaaaaaaa') > AND ((__Z0.COMPANYID = 'mlyl') > AND (__Z0.PARTNERTYPE = 'sy')) > > PLAN SELECT > __C0_0 AS ACCOUNTNUMBER, > __C0_1 AS COMPANYID, > __C0_2 AS SUBSNUMBER, > __C0_3 AS APPLICATIONID, > __C0_4 AS TYPE, > __C0_5 AS STARTDATE, > __C0_6 AS ENDDATE, > __C0_7 AS MLANUMBER, > __C0_8 AS TRIALOFFER, > __C0_9 AS MATERIALNUMBER, > __C0_10 AS MATTYPE, > __C0_11 AS DESCRIPTION, > __C0_12 AS INTERNALDESC, > __C0_13 AS CLAS_MATNUM, > __C0_14 AS CLASSIFICATIONID, > __C0_15 AS CLASSIFICATIONNAME, > __C0_16 AS CLASSIFICATIONDESC, > __C0_17 AS CLASSIFICATIONTYPE, > __C0_18 AS LINENUMBER, > __C0_19 AS SUB_STARTDATE, > __C0_20 AS SUB_ENDDATE, > __C0_21 AS QUANTITY, > __C0_22 AS BANDLOW, > __C0_23 AS BANDHIGH, > __C0_24 AS BASECOUNT, > __C0_25 AS USERCAP, > __C0_26 AS USERAVAILABLE, > __C0_27 AS USERCOUNT > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > > > 0: jdbc:ignite:thin://a2054-Ignit-1KRMQSP9AWH> !indexes > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME CLASSIFICATIONS > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME CLASSIFICATIONS_MATERIALNUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME MATERIALNUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME SUBSCRIPTIONLINES > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME SUBSCRIPTIONLINES_SUBSNUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME SUBSNUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME BANDRANGE > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME BANDRANGE_SUBSNUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME SUBSNUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME BANDRANGE > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME BANDRANGE_LINENUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME LINENUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME SUBSCRIPTION > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME SUBSCRIPTION_ACCOUNTNUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME ACCOUNTNUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > TABLE_CAT > TABLE_SCHEM PUBLIC > TABLE_NAME SUBSCRIPTION > NON_UNIQUE true > INDEX_QUALIFIER > INDEX_NAME SUBSCRIPTION_MATERIALNUMBER_IDX > TYPE 3 > ORDINAL_POSITION 0 > COLUMN_NAME MATERIALNUMBER > ASC_OR_DESC A > CARDINALITY 0 > PAGES 0 > FILTER_CONDITION > > Sent from my iPhone >
