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);
}
}
