Hi, Please, try to create secondary indices on join columns, otherwise query will fall into full table scan.
Then if you will see SCANs, as next step, you can try to rewrite your query with different tables join order. Sometimes, underline H2 can change join order to non optimal. In that case qry.setEnforceJoinOrder(true) may be helpful. Looks like there should be a single lookup on ID column, and 2 index scans for joining. пт, 28 сент. 2018 г., 19:02 Skollur <skol...@idbny.com>: > Here is the explain query > > # PLAN > 1 "SELECT > ADDR__Z2.ADDRESS_LINE_1 AS __C0_0, > ADDR__Z2.ADDRESS_LINE_2 AS __C0_1, > ADDR__Z2.ADDRESS_LINE_3 AS __C0_2, > ADDR__Z2.STREET AS __C0_3, > ADDR__Z2.CITY AS __C0_4, > ADDR__Z2.STATE AS __C0_5, > ADDR__Z2.COUNTRY AS __C0_6, > ADDR__Z2.ZIP_POSTAL AS __C0_7 > FROM "GroupAddressCache".GROUP_ADDRESS GA__Z1 > /* "GroupAddressCache".GROUP_ADDRESS.__SCAN_ */ > /* WHERE (GA__Z1.ADDRESS_TYPE = 'Mailing') > AND (GA__Z1.RECORD_IS_VALID = 'Y') > */ > INNER JOIN "GroupCache"."[GROUP]" GRP__Z0 > /* "GroupCache"."[GROUP]".__SCAN_ */ > ON 1=1 > /* WHERE (GRP__Z0.RECORD_IS_VALID = 'Y') > AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44) > AND (GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID)) > */ > INNER JOIN "AddressCache".ADDRESS ADDR__Z2 > /* "AddressCache"."_key_PK_proxy": DW_ID = GA__Z1.ADDRESS_ID */ > ON 1=1 > WHERE (GA__Z1.ADDRESS_ID = ADDR__Z2.DW_ID) > AND ((GA__Z1.ADDRESS_TYPE = 'Mailing') > AND ((GA__Z1.RECORD_IS_VALID = 'Y') > AND ((GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID) > AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44) > AND (GRP__Z0.RECORD_IS_VALID = 'Y')))))" > 2 "SELECT > __C0_0 AS ADDRESS_LINE_1, > __C0_1 AS ADDRESS_LINE_2, > __C0_2 AS ADDRESS_LINE_3, > __C0_3 AS STREET, > __C0_4 AS CITY, > __C0_5 AS STATE, > __C0_6 AS COUNTRY, > __C0_7 AS ZIP_POSTAL > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */" > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
