internal static bool IsIndexApplicableForQuery(StatementQueryExtractedData extractedData, IndexDefinition index) { return(extractedData.WhereAttributes.All .Union(extractedData.JoinAttributes.All) .Union(extractedData.GroupByAttributes.All) .Union(extractedData.OrderByAttributes.All).Contains(index.Attributes.First())); }
private void GenerateIndices(StatementQueryExtractedData extractedData, NormalizedStatement normalizedStatement, StatementQuery query) { var possibleIndices = context.IndicesDesignData.PossibleIndices; var whereJoinAttributesToUse = GroupAttributesByRelationAndPrepare(extractedData.WhereAttributes.BTreeApplicable.Union(extractedData.JoinAttributes.BTreeApplicable)); var groupByAttributesToUse = GroupAttributesByRelationAndPrepare(extractedData.GroupByAttributes.BTreeApplicable); var orderByAttributesToUse = GroupAttributesByRelationAndPrepare(extractedData.OrderByAttributes.BTreeApplicable); // order by indices foreach (var kv in orderByAttributesToUse) { var relation = kv.Key; var attributes = kv.Value; var permutations = GenerateAllPermutations(relation, attributes); possibleIndices.TryAddPossibleIndices(permutations.AllPermutations, normalizedStatement, query); } // group by indices foreach (var kv in groupByAttributesToUse) { var relation = kv.Key; var attributes = kv.Value; var permutations = GenerateAllPermutations(relation, attributes); possibleIndices.TryAddPossibleIndices(permutations.AllPermutations, normalizedStatement, query); } // where U join, where U join + group by, where U join + order by // note: where U join + group by + order by is not needed (any atrribute in order by must be already in group by) foreach (var kv in whereJoinAttributesToUse) { var relation = kv.Key; var attributes = kv.Value; var whereJoinPermutations = GenerateAllPermutations(relation, attributes); // where U join possibleIndices.TryAddPossibleIndices(whereJoinPermutations.AllPermutations, normalizedStatement, query); if (whereJoinPermutations.LastGenerationNumber < settings.IndexMaxAttributesCount) { var maxAttributesCountToConcat = settings.IndexMaxAttributesCount - whereJoinPermutations.LastGenerationNumber; // where U join + group by if (groupByAttributesToUse.ContainsKey(relation)) { var attributesToConcat = groupByAttributesToUse[relation].Except(whereJoinAttributesToUse[relation]).OrderBy(x => x.CardinalityIndicator).Take(maxAttributesCountToConcat).ToHashSet(); var permutationsToConcat = GenerateAllPermutations(relation, attributesToConcat); var newPermutations = ConcatPermutations(whereJoinPermutations.AllPermutations, permutationsToConcat.AllPermutations); possibleIndices.TryAddPossibleIndices(newPermutations, normalizedStatement, query); } // where U join + order by if (orderByAttributesToUse.ContainsKey(relation)) { var attributesToConcat = orderByAttributesToUse[relation].Except(whereJoinAttributesToUse[relation]).OrderBy(x => x.CardinalityIndicator).Take(maxAttributesCountToConcat).ToHashSet(); var permutationsToConcat = GenerateAllPermutations(relation, attributesToConcat); var newPermutations = ConcatPermutations(whereJoinPermutations.AllPermutations, permutationsToConcat.AllPermutations); possibleIndices.TryAddPossibleIndices(newPermutations, normalizedStatement, query); } } } }
private void ExtractDataFromStatement(NormalizedWorkloadStatement statement) { foreach (var query in statement.NormalizedStatement.StatementDefinition.IndependentQueries) { var allWhereOperatorsByAttribute = new Dictionary <AttributeData, ISet <string> >(); var btreeWhereAttributes = new HashSet <AttributeData>(); var allJoinOperatorsByAttribute = new Dictionary <AttributeData, ISet <string> >(); var btreeJoinAttributes = new HashSet <AttributeData>(); var allGroupByOperatorsByAttribute = new Dictionary <AttributeData, ISet <string> >(); var btreeGroupByAttributes = new HashSet <AttributeData>(); var allOrderByOperatorsByAttribute = new Dictionary <AttributeData, ISet <string> >(); var btreeOrderByAttributes = new HashSet <AttributeData>(); FillAllAttributesAndOperatorsFromExpressions(allWhereOperatorsByAttribute, btreeWhereAttributes, query.WhereExpressions, new HashSet <string>()); FillAllAttributesAndOperatorsFromExpressions(allJoinOperatorsByAttribute, btreeJoinAttributes, query.JoinExpressions, new HashSet <string>()); FillAllAttributesAndOperatorsFromExpressions(allGroupByOperatorsByAttribute, btreeGroupByAttributes, query.GroupByExpressions, new HashSet <string>()); FillAllAttributesAndOperatorsFromExpressions(allOrderByOperatorsByAttribute, btreeOrderByAttributes, query.OrderByExpressions, new HashSet <string>()); var allProjectionOperatorsByAttribute = new Dictionary <AttributeData, ISet <string> >(); var btreeProjectionAttributes = new HashSet <AttributeData>(); foreach (var t in query.ProjectionAttributes) { var attribute = attributesRepository.Get(t.RelationID, t.AttributeNumber); if (attribute != null) { if (context.RelationsData.TryGetRelation(t.RelationID, out var relationData)) { var toAdd = CreateIndexAttribute(relationData, attribute); if (!allProjectionOperatorsByAttribute.ContainsKey(toAdd)) { allProjectionOperatorsByAttribute.Add(toAdd, new HashSet <string>()); } if (supportedOperators.Intersect(attribute.SupportedOperators).Count() > 0 && // is comparable operator !context.Workload.Definition.Relations.ForbiddenValues.Contains(t.RelationID)) // relation is not forbidden { btreeProjectionAttributes.Add(toAdd); if (t.WithAppliedAggregateFunction) { btreeGroupByAttributes.Add(toAdd); if (!allGroupByOperatorsByAttribute.ContainsKey(toAdd)) { allGroupByOperatorsByAttribute.Add(toAdd, new HashSet <string>()); } } } } } } var data = new StatementQueryExtractedData(new SetOfAttributes(allWhereOperatorsByAttribute, btreeWhereAttributes), new SetOfAttributes(allJoinOperatorsByAttribute, btreeJoinAttributes), new SetOfAttributes(allGroupByOperatorsByAttribute, btreeGroupByAttributes), new SetOfAttributes(allOrderByOperatorsByAttribute, btreeOrderByAttributes), new SetOfAttributes(allProjectionOperatorsByAttribute, btreeProjectionAttributes)); context.StatementsExtractedData.Add(statement.NormalizedStatement, query, data); } }
public void Add(NormalizedStatement normalizedStatement, StatementQuery query, StatementQueryExtractedData queryExtractedData) { if (!normalizedStatementQueriesData.ContainsKey(normalizedStatement.ID)) { normalizedStatementQueriesData.Add(normalizedStatement.ID, new List <StatementQueryExtractedData>()); } normalizedStatementQueriesData[normalizedStatement.ID].Add(queryExtractedData); dataPerQuery.Add(new NormalizedStatementQueryPair(normalizedStatement.ID, query), queryExtractedData); }
private CreateCoveringIndexResult TryCreateCoveringIndex(DAL.Contracts.StatementQuery query, StatementQueryExtractedData queryExtractedData, IndexDefinition baseIndex, out IndexDefinition coveringIndex) { var allWhereAttributes = queryExtractedData.WhereAttributes.All.Where(x => x.Relation.ID == baseIndex.Relation.ID); var bTreeWhereAttributes = queryExtractedData.WhereAttributes.BTreeApplicable.Where(x => x.Relation.ID == baseIndex.Relation.ID); var allJoinAttributes = queryExtractedData.JoinAttributes.All.Where(x => x.Relation.ID == baseIndex.Relation.ID); var bTreeJoinAttributes = queryExtractedData.JoinAttributes.BTreeApplicable.Where(x => x.Relation.ID == baseIndex.Relation.ID); var allGroupByAttributes = queryExtractedData.GroupByAttributes.All.Where(x => x.Relation.ID == baseIndex.Relation.ID); var bTreeGroupByAttributes = queryExtractedData.GroupByAttributes.BTreeApplicable.Where(x => x.Relation.ID == baseIndex.Relation.ID); var allOrderByAttributes = queryExtractedData.OrderByAttributes.All.Where(x => x.Relation.ID == baseIndex.Relation.ID); var bTreeOrderByAttributes = queryExtractedData.OrderByAttributes.BTreeApplicable.Where(x => x.Relation.ID == baseIndex.Relation.ID); var allProjectionAttributes = queryExtractedData.ProjectionAttributes.All.Where(x => x.Relation.ID == baseIndex.Relation.ID); if (allWhereAttributes.Count() == bTreeWhereAttributes.Count() && allJoinAttributes.Count() == bTreeJoinAttributes.Count() && allGroupByAttributes.Count() == bTreeGroupByAttributes.Count() && allOrderByAttributes.Count() == bTreeOrderByAttributes.Count() && query.CommandType != DAL.Contracts.StatementQueryCommandType.Insert) { var includeAttributes = new HashSet <AttributeData>(bTreeWhereAttributes); includeAttributes.UnionWith(bTreeJoinAttributes); includeAttributes.UnionWith(bTreeGroupByAttributes); includeAttributes.UnionWith(bTreeOrderByAttributes); includeAttributes.UnionWith(allProjectionAttributes); includeAttributes.ExceptWith(baseIndex.Attributes); List <AttributeData> includeSortedAttributes = new List <AttributeData>(includeAttributes.OrderBy(x => x.CardinalityIndicator)); coveringIndex = new IndexDefinition(baseIndex.StructureType, baseIndex.Relation, baseIndex.Attributes, includeSortedAttributes); return(baseIndex.IncludeAttributes.Count != coveringIndex.IncludeAttributes.Count ? CreateCoveringIndexResult.CreatedNew : CreateCoveringIndexResult.CreatedSameAsBaseIndex); } else { coveringIndex = null; return(CreateCoveringIndexResult.NotPossible); } }