Improved Clustered Indexes

Clustered indexes are a feature of InMemory, that help you speed up certain queries, when you filter by a common dimension.

Suppose you have a main fact table called SalesTable, and a date dimension called InvoiceDate, you could create a clustered index, by running the following statement in IRDBImport or IRDB-SQL


In our earlier builds, our tool would search out range filters or simple list of equality expressions involving the clustered index, on the first table ( or driving table). This would then speed up queries by only scanning those records.

We have enhanced our clustered index support. We can now analyze joins (LEFT or INNER ) involving the clustered column. We will analyze the where clause, and try and extract a filter that just applies to the joined table. We will then execute a simplified query on the joined table, and then use it to generate a subset of the records on the driving table, to query.

This means if you have a lookup table that joins on the clustered column , and you filter on attributes in that table, it should still be able to use the clustered index to speed your query.

%d bloggers like this: