Ejemplo n.º 1
0
        private static int AssertBeNaturalJoin(TablePlan plan1, TablePlan plan2)
        {
            if (plan1.LeftPlan == plan2 || plan1.RightPlan == plan2)
            {
                return(0);
            }
            if (plan1.LeftPlan != null && plan2.LeftPlan != null)
            {
                // This is a left clash
                return(2);
            }
            if (plan1.RightPlan != null && plan2.RightPlan != null)
            {
                // This is a right clash
                return(1);
            }
            if ((plan1.LeftPlan == null && plan2.RightPlan == null) ||
                (plan1.RightPlan == null && plan2.LeftPlan == null))
            {
                // This means a merge between the plans is fine
                return(0);
            }

            // Must be a left and right clash
            return(2);
        }
Ejemplo n.º 2
0
        public void MergeJoin(TablePlan left, TablePlan right)
        {
            if (left.RightPlan != right)
            {
                if (left.RightPlan != null)
                {
                    RightJoin(left.RightPlan, left.RightJoinType, left.RightOnExpression);
                    RightPlan.LeftPlan = this;
                }
                if (right.LeftPlan != null)
                {
                    LeftJoin(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
                    LeftPlan.RightPlan = this;
                }
            }

            if (left.LeftPlan != right)
            {
                if (LeftPlan == null && left.LeftPlan != null)
                {
                    LeftJoin(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
                    LeftPlan.RightPlan = this;
                }
                if (RightPlan == null && right.RightPlan != null)
                {
                    RightJoin(right.RightPlan, right.RightJoinType, right.RightOnExpression);
                    RightPlan.LeftPlan = this;
                }
            }
        }
Ejemplo n.º 3
0
 public SingleColumnPlan(TablePlan tablePlan, ObjectName columnName, ObjectName uniqueName, SqlExpression expression)
 {
     TablePlan  = tablePlan;
     ColumnName = columnName;
     UniqueName = uniqueName;
     Expression = expression;
 }
Ejemplo n.º 4
0
        private int IndexOfPlan(TablePlan plan)
        {
            int sz = tablePlans.Count;

            for (int i = 0; i < sz; ++i)
            {
                if (tablePlans[i] == plan)
                {
                    return(i);
                }
            }

            return(-1);
        }
Ejemplo n.º 5
0
        private TablePlan MergePlans(TablePlan left, TablePlan right, IQueryPlanNode mergePlan)
        {
            // Remove the sources from the table list.
            tablePlans.Remove(left);
            tablePlans.Remove(right);

            // Add the concatenation of the left and right tables.
            var newPlan = ConcatPlans(left, right, mergePlan);

            newPlan.MergeJoin(left, right);
            newPlan.SetUpdated();

            AddPlan(newPlan);

            return(newPlan);
        }
Ejemplo n.º 6
0
        private static TablePlan ConcatPlans(TablePlan left, TablePlan right, IQueryPlanNode plan)
        {
            // Merge the variable list
            var newVarList = new ObjectName[left.ColumnNames.Length + right.ColumnNames.Length];

            Array.Copy(left.ColumnNames, 0, newVarList, 0, left.ColumnNames.Length);
            Array.Copy(right.ColumnNames, 0, newVarList, left.ColumnNames.Length, right.ColumnNames.Length);

            // Merge the unique table names list
            var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length];

            Array.Copy(left.UniqueNames, 0, newUniqueList, 0, left.UniqueNames.Length);
            Array.Copy(right.UniqueNames, 0, newUniqueList, left.UniqueNames.Length, right.UniqueNames.Length);

            // Return the new table source plan.
            return(new TablePlan(plan, newVarList, newUniqueList));
        }
Ejemplo n.º 7
0
        private static void AddSingleColumnPlan(IList <SingleColumnPlan> list, TablePlan table, ObjectName columnName, ObjectName uniqueName, SqlExpression[] expParts, SqlExpressionType op)
        {
            var exp = SqlExpression.Binary(expParts[0], op, expParts[1]);

            // Is this source in the list already?
            foreach (var existingPlan in list)
            {
                if (existingPlan.TablePlan == table &&
                    (columnName == null || existingPlan.ColumnName.Equals(columnName)))
                {
                    // Append to end of current expression
                    existingPlan.SetSource(columnName, SqlExpression.And(existingPlan.Expression, exp));
                    return;
                }
            }

            // Didn't find so make a new entry in the list.
            list.Add(new SingleColumnPlan(table, columnName, uniqueName, exp));
        }
Ejemplo n.º 8
0
        private TablePlan FindCommonPlan(IList <ObjectName> columnNames)
        {
            if (columnNames.Count == 0)
            {
                return(null);
            }

            TablePlan pivotPlan = null;

            foreach (var columnName in columnNames)
            {
                var plan = FindPlan(columnName);
                if (pivotPlan == null)
                {
                    pivotPlan = plan;
                }
                else if (plan != pivotPlan)
                {
                    return(null);
                }
            }

            return(pivotPlan);
        }
Ejemplo n.º 9
0
        private int IndexOfPlan(TablePlan plan)
        {
            int sz = tablePlans.Count;
            for (int i = 0; i < sz; ++i) {
                if (tablePlans[i] == plan)
                    return i;
            }

            return -1;
        }
Ejemplo n.º 10
0
        private static TablePlan ConcatPlans(TablePlan left, TablePlan right, IQueryPlanNode plan)
        {
            // Merge the variable list
            var newVarList = new ObjectName[left.ColumnNames.Length + right.ColumnNames.Length];
            Array.Copy(left.ColumnNames, 0, newVarList, 0, left.ColumnNames.Length);
            Array.Copy(right.ColumnNames, 0, newVarList, left.ColumnNames.Length - 1, right.ColumnNames.Length);

            // Merge the unique table names list
            var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length];
            Array.Copy(left.UniqueNames, 0, newUniqueList, 0, left.UniqueNames.Length);
            Array.Copy(right.UniqueNames, 0, newUniqueList, left.UniqueNames.Length - 1, right.UniqueNames.Length);

            // Return the new table source plan.
            return new TablePlan(plan, newVarList, newUniqueList);
        }
Ejemplo n.º 11
0
 public SingleColumnPlan(TablePlan tablePlan, ObjectName columnName, ObjectName uniqueName, SqlExpression expression)
 {
     TablePlan = tablePlan;
     ColumnName = columnName;
     UniqueName = uniqueName;
     Expression = expression;
 }
Ejemplo n.º 12
0
        private static int AssertBeNaturalJoin(TablePlan plan1, TablePlan plan2)
        {
            if (plan1.LeftPlan == plan2 || plan1.RightPlan == plan2)
                return 0;
            if (plan1.LeftPlan != null && plan2.LeftPlan != null)
                // This is a left clash
                return 2;
            if (plan1.RightPlan != null && plan2.RightPlan != null)
                // This is a right clash
                return 1;
            if ((plan1.LeftPlan == null && plan2.RightPlan == null) ||
                (plan1.RightPlan == null && plan2.LeftPlan == null))
                // This means a merge between the plans is fine
                return 0;

            // Must be a left and right clash
            return 2;
        }
Ejemplo n.º 13
0
 public void RightJoin(TablePlan right, JoinType joinType, SqlExpression onExpression)
 {
     RightPlan = right;
     RightJoinType = joinType;
     RightOnExpression = onExpression;
 }
Ejemplo n.º 14
0
        public void MergeJoin(TablePlan left, TablePlan right)
        {
            if (left.RightPlan != right) {
                if (left.RightPlan != null) {
                    RightJoin(left.RightPlan, left.RightJoinType, left.RightOnExpression);
                    RightPlan.LeftPlan = this;
                }
                if (right.LeftPlan != null) {
                    LeftJoin(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
                    LeftPlan.RightPlan = this;
                }
            }

            if (left.LeftPlan != right) {
                if (LeftPlan == null && left.LeftPlan != null) {
                    LeftJoin(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
                    LeftPlan.RightPlan = this;
                }
                if (RightPlan == null && right.RightPlan != null) {
                    RightJoin(right.RightPlan, right.RightJoinType, right.RightOnExpression);
                    RightPlan.LeftPlan = this;
                }
            }
        }
Ejemplo n.º 15
0
 public void LeftJoin(TablePlan left, JoinType joinType, SqlExpression onExpression)
 {
     LeftPlan = left;
     LeftJoinType = joinType;
     LeftOnExpression = onExpression;
 }
Ejemplo n.º 16
0
 public void RightJoin(TablePlan right, JoinType joinType, SqlExpression onExpression)
 {
     RightPlan         = right;
     RightJoinType     = joinType;
     RightOnExpression = onExpression;
 }
Ejemplo n.º 17
0
 public void AddPlan(TablePlan tablePlan)
 {
     tablePlans.Add(tablePlan);
     HasJoin = true;
 }
Ejemplo n.º 18
0
        private TablePlan MergePlans(TablePlan left, TablePlan right, IQueryPlanNode mergePlan)
        {
            // Remove the sources from the table list.
            tablePlans.Remove(left);
            tablePlans.Remove(right);

            // Add the concatenation of the left and right tables.
            var newPlan = ConcatPlans(left, right, mergePlan);
            newPlan.MergeJoin(left, right);
            newPlan.SetUpdated();

            AddPlan(newPlan);

            return newPlan;
        }
Ejemplo n.º 19
0
        private void EvaluateSubLogic(List <SqlBinaryExpression> list, List <ExpressionPlan> plans)
        {
            foreach (var expression in list)
            {
                var orExprs = new[] { expression.Left, expression.Right };

                // An optimizations here;

                // If all the expressions we are ORing together are in the same table
                // then we should execute them before the joins, otherwise they
                // should go after the joins.

                // The reason for this is because if we can lesson the amount of work a
                // join has to do then we should.  The actual time it takes to perform
                // an OR search shouldn't change if it is before or after the joins.

                TablePlan common = null;

                foreach (var orExpr in orExprs)
                {
                    var vars = orExpr.DiscoverReferences().ToArray();

                    bool breakRule = false;

                    // If there are no variables then don't bother with this expression
                    if (vars.Any())
                    {
                        // Find the common table source (if any)
                        var  ts           = FindCommonPlan(vars);
                        bool orAfterJoins = false;
                        if (ts == null)
                        {
                            // No common table, so OR after the joins
                            orAfterJoins = true;
                        }
                        else if (common == null)
                        {
                            common = ts;
                        }
                        else if (common != ts)
                        {
                            // No common table with the vars in this OR list so do this OR
                            // after the joins.
                            orAfterJoins = true;
                        }

                        if (orAfterJoins)
                        {
                            plans.Add(new SubLogicPlan(this, expression, 0.70f));

                            // Continue to the next logic expression
                            breakRule = true;
                        }
                    }

                    if (!breakRule)
                    {
                        // Either we found a common table or there are no variables in the OR.
                        // Either way we should evaluate this after the join.
                        plans.Add(new SubLogicPlan(this, expression, 0.58f));
                    }
                }
            }
        }
Ejemplo n.º 20
0
        private TablePlan NaturallyJoinPlans(TablePlan plan1, TablePlan plan2)
        {
            JoinType      joinType;
            SqlExpression onExpr;
            TablePlan     leftPlan, rightPlan;

            // Are the plans linked by common join information?
            if (plan1.RightPlan == plan2)
            {
                joinType  = plan1.RightJoinType;
                onExpr    = plan1.RightOnExpression;
                leftPlan  = plan1;
                rightPlan = plan2;
            }
            else if (plan1.LeftPlan == plan2)
            {
                joinType  = plan1.LeftJoinType;
                onExpr    = plan1.LeftOnExpression;
                leftPlan  = plan2;
                rightPlan = plan1;
            }
            else
            {
                // Assertion - make sure no join clashes!
                if ((plan1.LeftPlan != null && plan2.LeftPlan != null) ||
                    (plan1.RightPlan != null && plan2.RightPlan != null))
                {
                    throw new InvalidOperationException("Plans can not be naturally join because " +
                                                        "the left/right join plans clash.");
                }

                // Else we must assume a non-dependent join (not an outer join).
                // Perform a natural join
                var node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan);
                return(MergePlans(plan1, plan2, node1));
            }

            // This means plan1 and plan2 are linked by a common join and ON
            // expression which we evaluate now.
            bool outerJoin;

            if (joinType == JoinType.Left)
            {
                // Mark the left plan
                leftPlan.UpdatePlan(new MarkerNode(leftPlan.Plan, "OUTER_JOIN"));
                outerJoin = true;
            }
            else if (joinType == JoinType.Right)
            {
                // Mark the right plan
                rightPlan.UpdatePlan(new MarkerNode(rightPlan.Plan, "OUTER_JOIN"));
                outerJoin = true;
            }
            else if (joinType == JoinType.Inner)
            {
                // Inner join with ON expression
                outerJoin = false;
            }
            else
            {
                throw new InvalidOperationException(String.Format("Join type ({0}) is not supported.", joinType));
            }

            // Make a Planner object for joining these plans.
            var planner = new QueryTablePlanner();

            planner.AddPlan(leftPlan.Clone());
            planner.AddPlan(rightPlan.Clone());

            // Evaluate the on expression
            var node = planner.LogicalEvaluate(onExpr);

            // If outer join add the left outer join node
            if (outerJoin)
            {
                node = new LeftOuterJoinNode(node, "OUTER_JOIN");
            }

            // And merge the plans in this set with the new node.
            return(MergePlans(plan1, plan2, node));
        }
Ejemplo n.º 21
0
        private TablePlan NaturallyJoinPlans(TablePlan plan1, TablePlan plan2)
        {
            JoinType joinType;
            SqlExpression onExpr;
            TablePlan leftPlan, rightPlan;

            // Are the plans linked by common join information?
            if (plan1.RightPlan == plan2) {
                joinType = plan1.RightJoinType;
                onExpr = plan1.RightOnExpression;
                leftPlan = plan1;
                rightPlan = plan2;
            } else if (plan1.LeftPlan == plan2) {
                joinType = plan1.LeftJoinType;
                onExpr = plan1.LeftOnExpression;
                leftPlan = plan2;
                rightPlan = plan1;
            } else {
                // Assertion - make sure no join clashes!
                if ((plan1.LeftPlan != null && plan2.LeftPlan != null) ||
                    (plan1.RightPlan != null && plan2.RightPlan != null)) {
                    throw new InvalidOperationException("Plans can not be naturally join because " +
                                                        "the left/right join plans clash.");
                }

                // Else we must assume a non-dependent join (not an outer join).
                // Perform a natural join
                IQueryPlanNode node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan);
                return MergePlans(plan1, plan2, node1);
            }

            // This means plan1 and plan2 are linked by a common join and ON
            // expression which we evaluate now.
            bool outerJoin;
            if (joinType == JoinType.Left) {
                // Mark the left plan
                leftPlan.UpdatePlan(new MarkerNode(leftPlan.Plan, "OUTER_JOIN"));
                outerJoin = true;
            } else if (joinType == JoinType.Right) {
                // Mark the right plan
                rightPlan.UpdatePlan(new MarkerNode(rightPlan.Plan, "OUTER_JOIN"));
                outerJoin = true;
            } else if (joinType == JoinType.Inner) {
                // Inner join with ON expression
                outerJoin = false;
            } else {
                throw new InvalidOperationException(String.Format("Join type ({0}) is not supported.", joinType));
            }

            // Make a Planner object for joining these plans.
            var planner = new QueryTablePlanner();
            planner.AddPlan(leftPlan.Clone());
            planner.AddPlan(rightPlan.Clone());

            // Evaluate the on expression
            var node = planner.LogicalEvaluate(onExpr);

            // If outer join add the left outer join node
            if (outerJoin)
                node = new LeftOuterJoinNode(node, "OUTER_JOIN");

            // And merge the plans in this set with the new node.
            return MergePlans(plan1, plan2, node);
        }
Ejemplo n.º 22
0
 public void AddPlan(TablePlan tablePlan)
 {
     tablePlans.Add(tablePlan);
     HasJoin = true;
 }
Ejemplo n.º 23
0
        private static void AddSingleColumnPlan(IList<SingleColumnPlan> list, TablePlan table, ObjectName columnName, ObjectName uniqueName, SqlExpression[] expParts, SqlExpressionType op)
        {
            var exp = SqlExpression.Binary(expParts[0], op, expParts[1]);

            // Is this source in the list already?
            foreach (var existingPlan in list) {
                if (existingPlan.TablePlan == table &&
                    (columnName == null || existingPlan.ColumnName.Equals(columnName))) {
                    // Append to end of current expression
                    existingPlan.SetSource(columnName, SqlExpression.And(existingPlan.Expression, exp));
                    return;
                }
            }

            // Didn't find so make a new entry in the list.
            list.Add(new SingleColumnPlan(table, columnName, uniqueName, exp));
        }
Ejemplo n.º 24
0
 public void LeftJoin(TablePlan left, JoinType joinType, SqlExpression onExpression)
 {
     LeftPlan         = left;
     LeftJoinType     = joinType;
     LeftOnExpression = onExpression;
 }