David Wayne Birdsall created TRAFODION-3055:
-----------------------------------------------
Summary: Inefficient plan for subqueries + indexes on salted tables
Key: TRAFODION-3055
URL: https://issues.apache.org/jira/browse/TRAFODION-3055
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.3
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
Attachments: JiraMdam.log, JiraMdam.sql
This is follow-on work to Jira TRAFODION-3031 and Jira TRAFODION-3050. With the
changes of those two Jiras in place, there is yet another case where similar
queries fail to get an efficient nested join plan by default. The example here
is identical to that in TRAFODION-3050, except that both tables are salted. In
this example, the additional salting requires MDAM to be chosen on one of the
indexes to obtain an efficient plan, but one of our heuristics is preventing us
from considering MDAM.
The attached script and log demonstrate the problem (it assumes the fixes for
the other two Jiras are present). When CQD MDAM_UNDER_NJ_PROBES_THRESHOLD is
set to '1', we get a good nested join plan. (The current default for this CQD
is '0', which prevents MDAM from being considered on the inner table of a
nested join in most circumstances.)
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
