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
