Indexes on patterns

You can create a pattern matching index on a time series to speed pattern matching queries.

If you do not create pattern matching indexes, pattern matching queries run sequential scans to find matching data.

A pattern matching index is used during a pattern matching query if the query and the index match in the following ways:

• The time series instance (the primary key value) in the query and the index must be the same.
• The column in the TimeSeries subtype in the query and the index must be the same.
• The search method (whole pattern match or subsequence pattern match) in the query and the index must be the same.
• The search time range must be within the index time range.
• The query pattern length must be an integer multiple of the sliding window size.

A pattern matching index processes time series values by a Discrete Fourier Transformation (DFT) into vector points, calculates bounding boxes around the vector points, and indexes the bounding boxes with an R-tree index structure. During a query, the pattern match index evaluates the bounding boxes for the target time series against the query pattern.

Before you create a pattern matching index, you must understand your data and the type of search queries that you plan to run. Consider the following factors:

• The time series instance: You must create an index for every time series instance in which you want to search for patterns.
• The search method: Either whole pattern match for searching consecutive sequences of values, or subsequence pattern match for searching every possible subsequence of values. You can create a whole pattern match index and a subsequence match index on the same column.
• The accuracy of the index: A more accurate index results in faster queries but requires more storage space. The accuracy of the index depends on the number of DFT points that are included in a bounding box:
  • For a whole pattern match, set the partition length.
  • For a subsequence pattern match, set the sliding window size.
• The time range: You specify the beginning and ending time stamps of the data that you want to index. A pattern match index is used only if the time range that is specified in the query is within the time range that is indexed.

For each time series instance, you can create one whole pattern match index and one subsequence pattern match index for each numeric column in the TimeSeries row type.

When you create a pattern matching index, an internal table is created to contain the bounding boxes. The internal table names have the following format: TS_Index_table_name_col_position, where table_name is the name of the time series table that is indexed, and col_position is the numeric position of the column in the TimeSeries data type that is indexed. The internal table TS_INDEX_MAPPING tracks the pattern matching indexes.

disk pages = entries/((pagesize - 88)/(colsize +16))*.6