private static int GetCrossJoinCount(JoinOrder joinOrder) { int result = 0; for (int i = 0; i < joinOrder.Joins.Length; i++) { if (i > 0 && joinOrder.Joins[i].JoinCondition == null) result++; } return result; }
private static int GetCrossJoinCount(JoinOrder joinOrder) { int result = 0; for (int i = 0; i < joinOrder.Joins.Length; i++) { if (i > 0 && joinOrder.Joins[i].JoinCondition == null) { result++; } } return(result); }
private static JoinOrder GetBestJoinOrder(JoinOrder[] orders, ICollection <ExpressionNode> andParts, TableRefBinding[] tables) { // OK, now we try to find the best join order. // // To choose the best join order we will use a point system and score each possibility. // The more points a possibility has the better it is. // // To ensure we exclude as much rows as possible and as early as possible // we will use the count of conditions applicable to each combinations. // // A condition is said to be applicable if all referenced tables are already // joined. For example if we have to join four tables T1, T2, T3, T4. Accordding to // the join order T1 -> T2 -> T3 -> T4 the condition c is said to be applicable // at join index 0 if it only references columns of table T1, at index 1 if it // only references columns of tables T1 and T2 and so on. // Determine minimal count of cross joins int minCrossJoinCount = int.MaxValue; for (int i = 0; i < orders.Length; i++) { int crossJoinCount = GetCrossJoinCount(orders[i]); if (crossJoinCount < minCrossJoinCount) { minCrossJoinCount = crossJoinCount; } } int[] points = new int[orders.Length]; for (int i = 0; i < orders.Length; i++) { points[i] = GetJoinOrderScore(orders[i], andParts, tables); if (GetCrossJoinCount(orders[i]) > minCrossJoinCount) { // If the current join order uses more cross joins than // the minimal one we reject this join order by setting // its score to minus one. points[i] = -1; } } Array.Sort(points, orders); JoinOrder bestJoinOrder = orders[orders.Length - 1]; return(bestJoinOrder); }
private static JoinOrder GetBestJoinOrder(TableRefBinding[] tables, JoinCondition[] joinConditions, ICollection <ExpressionNode> andParts) { // Compute all join orders JoinOrderGenerator generator = new JoinOrderGenerator(joinConditions, tables); JoinOrder[] allJoinOrders = generator.GenerateAll(); // Compute best join order JoinOrder bestJoinOrder = GetBestJoinOrder(allJoinOrders, andParts, tables); return(bestJoinOrder); }
private static JoinOrder GetBestJoinOrder(JoinOrder[] orders, ICollection<ExpressionNode> andParts, TableRefBinding[] tables) { // OK, now we try to find the best join order. // // To choose the best join order we will use a point system and score each possibility. // The more points a possibility has the better it is. // // To ensure we exclude as much rows as possible and as early as possible // we will use the count of conditions applicable to each combinations. // // A condition is said to be applicable if all referenced tables are already // joined. For example if we have to join four tables T1, T2, T3, T4. Accordding to // the join order T1 -> T2 -> T3 -> T4 the condition c is said to be applicable // at join index 0 if it only references columns of table T1, at index 1 if it // only references columns of tables T1 and T2 and so on. // Determine minimal count of cross joins int minCrossJoinCount = int.MaxValue; for (int i = 0; i < orders.Length; i++) { int crossJoinCount = GetCrossJoinCount(orders[i]); if (crossJoinCount < minCrossJoinCount) minCrossJoinCount = crossJoinCount; } int[] points = new int[orders.Length]; for (int i = 0; i < orders.Length; i++) { points[i] = GetJoinOrderScore(orders[i], andParts, tables); if (GetCrossJoinCount(orders[i]) > minCrossJoinCount) { // If the current join order uses more cross joins than // the minimal one we reject this join order by setting // its score to minus one. points[i] = -1; } } Array.Sort(points, orders); JoinOrder bestJoinOrder = orders[orders.Length - 1]; return bestJoinOrder; }
private static int GetJoinOrderScore(JoinOrder joinOder, ICollection <ExpressionNode> andParts, TableRefBinding[] tables) { int result = 0; Dictionary <RowBufferEntry, ColumnValueDefinition> introducedColumns = GetIntroducedColumns(joinOder); for (int joinIndex = 0; joinIndex < tables.Length; joinIndex++) { ExpressionNode[] applicableConditions = GetAndPartsApplicableToJoin(introducedColumns, joinOder, joinIndex, andParts, false); result *= 2; result += applicableConditions.Length; if (joinOder.Joins[joinIndex].JoinCondition != null) { result += 1; } } return(result); }
public override AlgebraNode VisitJoinAlgebraNode(JoinAlgebraNode node) { // Check if node only consists of INNER join nodes and simple table reference nodes. // This algorithm assumes that the table references have been lineraized so that // // - JoinedTableReference appear on the LHS only (the last one must be NamedTableReference ovbiviously) // - NamedTableReference appear on the RHS only // // While scanning the node's children we create a list of all JoinedTableReferences and // NamedTableReferences. InnerJoinTableExtractor innerJoinTableExtractor = new InnerJoinTableExtractor(); innerJoinTableExtractor.Visit(node); if (!innerJoinTableExtractor.ConsistsOnlyOfInnerJoinsFiltersAndTables) { node.Left = VisitAlgebraNode(node.Left); node.Right = VisitAlgebraNode(node.Right); return(node); } else { TableAlgebraNode[] algebraNodes = innerJoinTableExtractor.GetTableNodes(); Dictionary <TableRefBinding, TableAlgebraNode> tableRefToNodeDictionary = new Dictionary <TableRefBinding, TableAlgebraNode>(); List <TableRefBinding> tableList = new List <TableRefBinding>(); foreach (TableAlgebraNode algebraNode in algebraNodes) { tableRefToNodeDictionary.Add(algebraNode.TableRefBinding, algebraNode); tableList.Add(algebraNode.TableRefBinding); } // Create a mapping RowBufferEntry -> ColumnRefBinding Dictionary <RowBufferEntry, ColumnRefBinding> rowBufferColumnDictionary = new Dictionary <RowBufferEntry, ColumnRefBinding>(); foreach (TableRefBinding tableRefBinding in tableList) { foreach (ColumnRefBinding columnRefBinding in tableRefBinding.ColumnRefs) { rowBufferColumnDictionary.Add(columnRefBinding.ValueDefinition.Target, columnRefBinding); } } // Create list of all possible join conditions and remaining AND-parts. List <JoinCondition> joinConditionList = new List <JoinCondition>(); List <ExpressionNode> andPartList = new List <ExpressionNode>(); ExpressionNode filter = AstUtil.CombineConditions(LogicalOperator.And, innerJoinTableExtractor.GetFilters()); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, filter)) { JoinCondition joinCondition = ConvertToJoinCondition(rowBufferColumnDictionary, andPart); if (joinCondition != null) { joinConditionList.Add(joinCondition); } else { andPartList.Add(andPart); } } // After creating the list of all join conditions and AND-parts we have all we need to create // an optimimal join order between all tables of this part of the table tree. JoinOrder bestJoinOrder = GetBestJoinOrder(tableList.ToArray(), joinConditionList.ToArray(), andPartList.ToArray()); // Get all tables that are introduced by this join order Dictionary <RowBufferEntry, ColumnValueDefinition> introducedColumns = GetIntroducedColumns(bestJoinOrder); // Combine AND-part list with all unused join conditions. andPartList.AddRange(bestJoinOrder.UnusedConditions); // Now we will re-create this part of the tree using the this join order. AlgebraNode lastAlgebraNode = null; for (int joinIndex = 0; joinIndex < bestJoinOrder.Joins.Length; joinIndex++) { Join join = bestJoinOrder.Joins[joinIndex]; AlgebraNode tableInput; TableAlgebraNode tableNode = tableRefToNodeDictionary[join.TableRefBinding]; ExpressionNode tableFilter = ExtractConditionsApplicableToTable(introducedColumns, andPartList, join.TableRefBinding); if (tableFilter == null) { tableInput = tableNode; } else { FilterAlgebraNode filterAlgebraNode = new FilterAlgebraNode(); filterAlgebraNode.Input = tableNode; filterAlgebraNode.Predicate = tableFilter; tableInput = filterAlgebraNode; } if (lastAlgebraNode == null) { // This was the first one. lastAlgebraNode = tableInput; } else { // Not the first one, we can create a join with the current table reference // and last table reference. // Get all AND-parts that can be applied to the tables already joined. // This expression is merged to one condition. ExpressionNode[] applicableAndParts = GetAndPartsApplicableToJoin(introducedColumns, bestJoinOrder, joinIndex, andPartList, true); ExpressionNode condition = AstUtil.CombineConditions(LogicalOperator.And, applicableAndParts); ExpressionNode joinCondition; if (join.JoinCondition == null) { joinCondition = null; } else { joinCondition = join.JoinCondition.ToExpression(); } ExpressionNode completeCondition = AstUtil.CombineConditions(LogicalOperator.And, condition, joinCondition); JoinAlgebraNode joinAlgebraNode = new JoinAlgebraNode(); joinAlgebraNode.Op = JoinAlgebraNode.JoinOperator.InnerJoin; joinAlgebraNode.Left = lastAlgebraNode; joinAlgebraNode.Right = tableInput; joinAlgebraNode.Predicate = completeCondition; // Next time this newly created join is the last table reference. lastAlgebraNode = joinAlgebraNode; } } return(lastAlgebraNode); } }
private static ExpressionNode[] GetAndPartsApplicableToJoin(IDictionary <RowBufferEntry, ColumnValueDefinition> introducedColumns, JoinOrder joinOrder, int joinIndex, ICollection <ExpressionNode> andParts, bool removeApplicableAndParts) { List <RowBufferEntry> columnsInJoin = new List <RowBufferEntry>(); for (int i = 0; i <= joinIndex; i++) { foreach (ColumnRefBinding columnRefBinding in joinOrder.Joins[i].TableRefBinding.ColumnRefs) { columnsInJoin.Add(columnRefBinding.ValueDefinition.Target); } } List <ExpressionNode> applicableAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in andParts) { RowBufferEntry[] rowBufferEntries = AstUtil.GetRowBufferEntryReferences(andPart); bool allDependenciesInJoin = true; foreach (RowBufferEntry entry in rowBufferEntries) { if (!columnsInJoin.Contains(entry)) { // Row buffer entry not available in the join the current position. // It could be an outer reference. Check if the row buffer entry // is in the set of introduced row buffer entries. if (introducedColumns.ContainsKey(entry)) { allDependenciesInJoin = false; break; } } } if (allDependenciesInJoin) { applicableAndParts.Add(andPart); } } if (removeApplicableAndParts) { foreach (ExpressionNode andPart in applicableAndParts) { andParts.Remove(andPart); } } return(applicableAndParts.ToArray()); }
private static Dictionary <RowBufferEntry, ColumnValueDefinition> GetIntroducedColumns(JoinOrder bestJoinOrder) { Dictionary <RowBufferEntry, ColumnValueDefinition> introducedColumns = new Dictionary <RowBufferEntry, ColumnValueDefinition>(); foreach (Join join in bestJoinOrder.Joins) { foreach (ColumnRefBinding columnRefBinding in join.TableRefBinding.ColumnRefs) { introducedColumns.Add(columnRefBinding.ValueDefinition.Target, columnRefBinding.ValueDefinition); } } return(introducedColumns); }
private void Join(TableRefBinding table, JoinCondition joinCondition) { _usedTableList.Add(table); _usedJoinConditionList.Add(joinCondition); if (_usedTableList.Count == _tables.Length) { // We have joined all tables, create the join order. // // First we create a list of the join conditions we used. List<Join> joinList = new List<Join>(); for (int i = 0; i < _usedTableList.Count; i++) { TableRefBinding usedTable = _usedTableList[i]; JoinCondition usedJoinCondition = _usedJoinConditionList[i]; // The first entry will be null, since we do not join from anywhere if (usedJoinCondition != null) { // Since we swapping the join sides according to the direction // we are joining from we need to create a clone. The clone // is a flat copy meaning that the tables and expressions // themselves are not cloned. usedJoinCondition = usedJoinCondition.Clone(); // If the right table is not the table we are joining to // swap the join sides. if (usedJoinCondition.RightTable != usedTable) usedJoinCondition.SwapSides(); } Join join = new Join(); join.JoinCondition = usedJoinCondition; join.TableRefBinding = usedTable; joinList.Add(join); } // Secondly and very important: We also have to create a list of all // join conditions NOT used. Later theses conditions will be combined // with the and-parts since they must also be checked. List<ExpressionNode> unusedConditionList = new List<ExpressionNode>(); foreach (JoinCondition jc in _joinConditions) { if (!_usedJoinConditionList.Contains(jc)) unusedConditionList.Add(jc.ToExpression()); } JoinOrder joinOrder = new JoinOrder(); joinOrder.Joins = joinList.ToArray(); joinOrder.UnusedConditions = unusedConditionList.ToArray(); _joinOrderList.Add(joinOrder); } else { // We are not yet finished with all tables. Find next table // to join with. bool hasJoin = false; foreach (JoinCondition nextJoin in _joinConditions) { if (!_usedJoinConditionList.Contains(nextJoin)) { TableRefBinding nextTable; if (_usedTableList.Contains(nextJoin.LeftTable)) { nextTable = nextJoin.RightTable; } else if (_usedTableList.Contains(nextJoin.RightTable)) { nextTable = nextJoin.LeftTable; } else { continue; } if (_usedTableList.Contains(nextTable)) continue; Join(nextTable, nextJoin); hasJoin = true; } } if (!hasJoin) { foreach (TableRefBinding t in _tables) { if (!_usedTableList.Contains(t)) Join(t, null); } } } _usedJoinConditionList.RemoveAt(_usedJoinConditionList.Count - 1); _usedTableList.RemoveAt(_usedTableList.Count - 1); }
private void Join(TableRefBinding table, JoinCondition joinCondition) { _usedTableList.Add(table); _usedJoinConditionList.Add(joinCondition); if (_usedTableList.Count == _tables.Length) { // We have joined all tables, create the join order. // // First we create a list of the join conditions we used. List <Join> joinList = new List <Join>(); for (int i = 0; i < _usedTableList.Count; i++) { TableRefBinding usedTable = _usedTableList[i]; JoinCondition usedJoinCondition = _usedJoinConditionList[i]; // The first entry will be null, since we do not join from anywhere if (usedJoinCondition != null) { // Since we swapping the join sides according to the direction // we are joining from we need to create a clone. The clone // is a flat copy meaning that the tables and expressions // themselves are not cloned. usedJoinCondition = usedJoinCondition.Clone(); // If the right table is not the table we are joining to // swap the join sides. if (usedJoinCondition.RightTable != usedTable) { usedJoinCondition.SwapSides(); } } Join join = new Join(); join.JoinCondition = usedJoinCondition; join.TableRefBinding = usedTable; joinList.Add(join); } // Secondly and very important: We also have to create a list of all // join conditions NOT used. Later theses conditions will be combined // with the and-parts since they must also be checked. List <ExpressionNode> unusedConditionList = new List <ExpressionNode>(); foreach (JoinCondition jc in _joinConditions) { if (!_usedJoinConditionList.Contains(jc)) { unusedConditionList.Add(jc.ToExpression()); } } JoinOrder joinOrder = new JoinOrder(); joinOrder.Joins = joinList.ToArray(); joinOrder.UnusedConditions = unusedConditionList.ToArray(); _joinOrderList.Add(joinOrder); } else { // We are not yet finished with all tables. Find next table // to join with. bool hasJoin = false; foreach (JoinCondition nextJoin in _joinConditions) { if (!_usedJoinConditionList.Contains(nextJoin)) { TableRefBinding nextTable; if (_usedTableList.Contains(nextJoin.LeftTable)) { nextTable = nextJoin.RightTable; } else if (_usedTableList.Contains(nextJoin.RightTable)) { nextTable = nextJoin.LeftTable; } else { continue; } if (_usedTableList.Contains(nextTable)) { continue; } Join(nextTable, nextJoin); hasJoin = true; } } if (!hasJoin) { foreach (TableRefBinding t in _tables) { if (!_usedTableList.Contains(t)) { Join(t, null); } } } } _usedJoinConditionList.RemoveAt(_usedJoinConditionList.Count - 1); _usedTableList.RemoveAt(_usedTableList.Count - 1); }
private static int GetJoinOrderScore(JoinOrder joinOder, ICollection<ExpressionNode> andParts, TableRefBinding[] tables) { int result = 0; Dictionary<RowBufferEntry, ColumnValueDefinition> introducedColumns = GetIntroducedColumns(joinOder); for (int joinIndex = 0; joinIndex < tables.Length; joinIndex++) { ExpressionNode[] applicableConditions = GetAndPartsApplicableToJoin(introducedColumns, joinOder, joinIndex, andParts, false); result *= 2; result += applicableConditions.Length; if (joinOder.Joins[joinIndex].JoinCondition != null) result += 1; } return result; }
private static ExpressionNode[] GetAndPartsApplicableToJoin(IDictionary<RowBufferEntry, ColumnValueDefinition> introducedColumns, JoinOrder joinOrder, int joinIndex, ICollection<ExpressionNode> andParts, bool removeApplicableAndParts) { List<RowBufferEntry> columnsInJoin = new List<RowBufferEntry>(); for( int i = 0; i <= joinIndex; i++) { foreach (ColumnRefBinding columnRefBinding in joinOrder.Joins[i].TableRefBinding.ColumnRefs) columnsInJoin.Add(columnRefBinding.ValueDefinition.Target); } List<ExpressionNode> applicableAndParts = new List<ExpressionNode>(); foreach (ExpressionNode andPart in andParts) { RowBufferEntry[] rowBufferEntries = AstUtil.GetRowBufferEntryReferences(andPart); bool allDependenciesInJoin = true; foreach (RowBufferEntry entry in rowBufferEntries) { if (!columnsInJoin.Contains(entry)) { // Row buffer entry not available in the join the current position. // It could be an outer reference. Check if the row buffer entry // is in the set of introduced row buffer entries. if (introducedColumns.ContainsKey(entry)) { allDependenciesInJoin = false; break; } } } if (allDependenciesInJoin) applicableAndParts.Add(andPart); } if (removeApplicableAndParts) { foreach (ExpressionNode andPart in applicableAndParts) andParts.Remove(andPart); } return applicableAndParts.ToArray(); }
private static Dictionary<RowBufferEntry, ColumnValueDefinition> GetIntroducedColumns(JoinOrder bestJoinOrder) { Dictionary<RowBufferEntry, ColumnValueDefinition> introducedColumns = new Dictionary<RowBufferEntry, ColumnValueDefinition>(); foreach (Join join in bestJoinOrder.Joins) { foreach (ColumnRefBinding columnRefBinding in join.TableRefBinding.ColumnRefs) introducedColumns.Add(columnRefBinding.ValueDefinition.Target, columnRefBinding.ValueDefinition); } return introducedColumns; }