Filtered Indices and Their Use in Flexible Schema Scenarios

Abstract

Efficient and convenient handling of heterogeneous data is a current challenge for data management systems. In this paper, we discuss several common relational approaches to represent heterogeneity and argue for a design based on a single wide-table, referred to as a flexible schema. For this scenario, we focus on partial indexation and its support for efficient data storage and processing.Filtered indices provide partial indexation functionality in the Microsoft SQL Server product. We describe here the implementation of this feature, including index utilization in queries, index maintenance and query parameterization issues. Our performance experiments validate the expected benefits of the approach in our implementation.

Relational support for flexible schema scenarios

“…1). The columns that do not pertain to the content type of a particular row are equal to null and the whole table remains sparse ( [3,17]). …”

“…The classic relational approaches of a property bag or ISA relation decomposition fail to handle heterogeneous collections efficiently ( [3,17]). For example, storing arbitrary objects in relational databases usually results in complex multi-table schemas and requires relevant translation instructions in object-relational mappers.…”

“…In [17], two categories of flexible schemas are distinguished: unclustered and clustered. While the first one refers to strongly heterogeneous entities that do not form any classes in terms of attributes they are characterized with, for the second one, such classification is possible, e.g.…”

“…Moreover, these applications usually require hierarchies, multi-valued properties and frequent schema changes. We use the term flexible schema to reflect such extensibility and heterogeneity of a data model [17].…”

“…In order to enable the efficient retrieval and manipulation of a large numbers of columns, we introduced a logical scalar representation that groups sparse columns in a column set. Economical data indexation in terms of space and DML is addressed with filtered indices ( [17]). Besides mechanisms used for efficient query execution, adequate selection and query optimization support is crucial.…”