public override CGroupMember Apply(CGroupMember expr)
{
LogicJoin a_bc = expr.logic_ as LogicJoin;
LogicNode a = (a_bc.lchild_() as LogicMemoRef).Deref <LogicNode>();
LogicJoin bc = (a_bc.rchild_() as LogicMemoRef).Deref <LogicJoin>();
Expr bcfilter = bc.filter_;
var ab = new LogicJoin(a_bc.lchild_(), bc.lchild_());
var c = bc.rchild_();
var ab_c = new LogicJoin(ab, c);
Debug.Assert(!a.LeftReferencesRight(bc));
if (ab.LeftReferencesRight(c))
{
return(expr);
}
// pull up all join filters and re-push them back
Expr allfilters = bcfilter;
if (a_bc.filter_ != null)
{
allfilters = allfilters.AddAndFilter(a_bc.filter_);
}
if (allfilters != null)
{
var andlist = allfilters.FilterToAndList();
andlist.RemoveAll(e => ab_c.PushJoinFilter(e));
if (andlist.Count > 0)
{
ab_c.filter_ = andlist.AndListToExpr();
}
}
// Ideally if there is no cross join in the given plan but cross join
// in the new plan, we shall return the original plan. However, stop
// exploration now will prevent generating other promising plans. So
// we have to return the new plan.
//
if (expr.QueryOption().optimize_.memo_disable_crossjoin_)
{
if (a_bc.filter_ != null && bcfilter != null)
{
if (ab_c.filter_ is null || ab.filter_ is null)
{
return(expr);
}
}
}
return(new CGroupMember(ab_c, expr.group_));
}
// classic formula is:
// A X B => |A|*|B|/max(dA, dB) where dA,dB are distinct values of joining columns
// This however does not consider join key distribution. In SQL Server 2014, it introduced
// histogram join to better the estimation.
//
public override ulong LogicJoinCE(LogicJoin node)
{
ulong getDistinct(Expr key)
{
if (key is ColExpr col)
{
var tr = col.tabRef_;
if (tr is FromQueryRef fqr && fqr.MapOutputName(col.colName_) != null)
{
if (fqr.MapOutputName(col.colName_) is ColExpr ce)
{
tr = ce.tabRef_;
}
}
if (tr is BaseTableRef btr)
{
var stats = Catalog.sysstat_.GetColumnStat(btr.relname_, col.colName_);
return(stats?.EstDistinct() ?? 0);
}
}
return(0);
}
ulong card;
node.CreateKeyList();
var cardl = node.lchild_().Card();
var cardr = node.rchild_().Card();
ulong dl = 0, dr = 0, mindlr = 1;
for (int i = 0; i < node.leftKeys_.Count; i++)
{
var lv = node.leftKeys_[i];
dl = getDistinct(lv);
var rv = node.rightKeys_[i];
dr = getDistinct(rv);
if (node.ops_[i] != "=")
{
mindlr = 0;
break;
}
mindlr = mindlr * Math.Min(dl, dr);
}
if (mindlr != 0)
{
card = Math.Max(1, (cardl * cardr) / mindlr);
}
else
{
// fall back to the old estimator
card = DefaultEstimate(node);
}
return(card);
}
public override CGroupMember Apply(CGroupMember expr)
{
LogicJoin log = expr.logic_ as LogicJoin;
var l = new PhysicMemoRef(log.lchild_());
var r = new PhysicMemoRef(log.rchild_());
PhysicNode phy = new PhysicNLJoin(log, l, r);
return(new CGroupMember(phy, expr.group_));
}
public override CGroupMember Apply(CGroupMember expr)
{
LogicJoin log = expr.logic_ as LogicJoin;
var l = new PhysicMemoRef(log.lchild_());
var r = new PhysicMemoRef(log.rchild_());
var hashjoin = new PhysicHashJoin(log, l, r);
return(new CGroupMember(hashjoin, expr.group_));
}
public override CGroupMember Apply(CGroupMember expr)
{
LogicJoin join = expr.logic_ as LogicJoin;
var l = join.lchild_(); var r = join.rchild_(); var f = join.filter_;
Debug.Assert(!l.LeftReferencesRight(r));
if (r.LeftReferencesRight(l))
{
return(expr);
}
LogicJoin newjoin = new LogicJoin(r, l, f);
return(new CGroupMember(newjoin, expr.group_));
}
public override bool Appliable(CGroupMember expr)
{
LogicJoin join = expr.logic_ as LogicJoin;
if (join is null || join is LogicMarkJoin || join is LogicSingleJoin)
{
return(false);
}
if (join.filter_.FilterHashable())
{
var stmt = expr.Stmt() as SelectStmt;
bool lhasSubqCol = stmt.PlanContainsCorrelatedSubquery() &&
TableRef.HasColsUsedBySubquries(join.lchild_().InclusiveTableRefs());
if (!lhasSubqCol)
{
return(true);
}
}
return(false);
}