The short answer is “yes”. It is possible to set up heuristic join ordering (that’s what Hive uses Calcite, for instance). And yes, that is the right approach to take; the exhaustive approach requires an exponential number of rule firings, so is not practical for “big” joins.
I’m not sure of the exact details for how to invoke heuristic join ordering. Did you try running the relevant tests? Julian > On Oct 19, 2016, at 9:29 PM, Nir Pedhazur <[email protected]> wrote: > > Hi, > > We have been encountering issues resulting with endless query planning phases > in VolcanoPlanner when running queries involving (7+) large number of joins. > Below is a sample dump of a relevant logical plan - > > 40:LogicalProject(ref_id=[$11], parent_id=[$10], title=[$12]) > 39:LogicalFilter(condition=[AND(=(CAST($6):VARCHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($2):VARCHAR(1) > CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), > =(CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary", CAST($15):VARCHAR(1) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($16):VARCHAR(1) > CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), > =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary", CAST($20):VARCHAR(1) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary"), =(CAST($23):VARCHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($19):VARCHAR(1) > CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), > =(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary", CAST($32):VARCHAR(1) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($33):VARCHAR(1) > CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), > <>(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary", CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary"), <>(CAST($27):VARCHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($10):VARCHAR(1) > CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), > =(CAST($7):BIGINT, 0), =(CAST($3):VARCHAR(13) CHARACTER SET "ISO-8859-1" > COLLATE "ISO-8859-1$en_US$primary", 'BLG_CUST_TYPE'))]) > 38:LogicalJoin(condition=[true], joinType=[inner]) > 36:LogicalJoin(condition=[true], joinType=[inner]) > 34:LogicalJoin(condition=[true], joinType=[inner]) > 32:LogicalJoin(condition=[true], joinType=[inner]) > 30:LogicalJoin(condition=[true], joinType=[inner]) > 28:LogicalJoin(condition=[true], joinType=[inner]) > 26:LogicalJoin(condition=[true], joinType=[inner]) > 24:LogicalTableScan(table=[[crm, table_hgbst_lst]]) > 25:LogicalTableScan(table=[[crm, table_hgbst_show]]) > 27:LogicalTableScan(table=[[crm, table_hgbst_elm]]) > 29:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]]) > 31:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]]) > 33:LogicalTableScan(table=[[crm, table_hgbst_show]]) > 35:LogicalTableScan(table=[[crm, table_hgbst_elm]]) > 37:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]]) > > Seeing https://issues.apache.org/jira/browse/CALCITE-349, > https://issues.apache.org/jira/browse/CALCITE-302 we have been trying to > activate the heuristic join optimizer to avoid these issues but have not been > successful so far (either through Hook.PROGRAM.add (resulting in > NullPointerExceptions) or through FrameworkConfig which didn't have effect on > prepare phase which unless a hook is used uses that the standard sequence of > programs). > > Is it possible to setup Calcite to reliably handle these types of queries ? > Any assistance will be welcome. > > Thanks, > Nir > > > > This message and the information contained herein is proprietary and > confidential and subject to the Amdocs policy statement, > you may review at http://www.amdocs.com/email_disclaimer.asp
