TSCreatePatternIndex function

The TSCreatePatternIndex function indexes time series data for pattern matching searches.

Syntax

TSCreatePatternIndex(
                ts              TimeSeries,
                table_name      LVARCHAR,
                primary_key     LVARCHAR,
                key_value       LVARCHAR,
                begin_tstamp    DATETIME YEAR TO FRACTION(5),
                end_tstamp      DATETIME YEAR TO FRACTION(5),
                col_name        LVARCHAR,
                index_settings  LVARCHAR,
                scope           INTEGER DEFAULT 0)
returns INTEGER;
ts
The time series value for the specified primary key.
table_name
The name of the time series table. The table must contain a TimeSeries column and have a primary key.
primary_key
The primary key column name.
If the primary key consists of multiple columns, concatenate the column names. Include the table name qualifier before the column name and separate the table and column name combinations with a pipe character (|). For example, if the primary key consists of two columns, the format is: 
'table_name.column_name1|table_name.column_name2'
key_value
The value of the primary key for the time series instance to index.
If the primary key consists of multiple columns, concatenate the column names. Include the table name qualifier before the column name and cast the table and column name combinations to LVARCHAR. Separate the table and column name combinations with concatenation operators (|| '|' ||). For example, if the primary key consists of two columns, the format is: 
'table_name.column_name1:LVARCHAR || '|' || 
table_name.column_name2:LVARCHAR'
begin_stamp
The beginning point of the range to index. Can be NULL, which represents the first element in the time series.
If you are updating an existing index, the value of begin_stamp must be before the value of end_stamp for the existing index.
end_stamp
The ending point of the range to index. Can be NULL, which represents the last element in the time series.
If you are updating an existing index, the value of end_stamp must be after the value of begin_stamp for the existing index.
col_name
The name of the column in the TimeSeries subtype to index.
If you are indexing a BSON column, include the field name within the BSON document to index after the column name with dot notation. For example, if the subtype column is named value and the BSON document field that you want to index is named v1, set the col_name parameter to value.v1.
index_settings
A string of index properties in one of the following formats:
'idx_metatype=0 slidewin=value'

'idx_metatype=1 partitionlen=value'
idx_metatype = The pattern match method. 0 = Default. Subsequence pattern match. 1 = Whole pattern match.
partitionlen = An integer 1-40 that represents the partition length for a whole pattern match. Sets how many successive values to process together as a DFT point.
slidewin = An integer 1-40 that represents the sliding window size for a subsequence pattern match. Sets how many successive values to process together as a DFT point and affects the length of the query pattern. The query pattern length must be an integer multiple of the sliding window size to use the index.
scope
Whether to update an existing index or replace the existing index:
0 = Default. Update the existing index.
1 = Any existing index is deleted and a new index is created.

Usage

Run the TSCreatePatternIndex function to index the data in the specified time series column for the specified time range. You can customize the index settings to obtain the appropriate search results. When you run a pattern matching query for which a pattern matching index exists, the query uses the index to speed the search.

For each time series instance, as designated by a primary key value, you can create both a whole pattern match index and a subsequence pattern match index on each column in the TimeSeries subtype that contains numeric data.

If you want to extend the time range of an existing index, conform to the following rules:

  • Set the scope value to 0.
  • You cannot have gaps in the time range of the index. Set the begin_stamp value to before the end_stamp of the existing index. Set the end_stamp value to after the begin_stamp of the existing index.
  • You can extend the index time range in either direction, or both directions. The value of begin_stamp can be earlier than the begin_stamp of the existing index. The value of end_stamp can be after the end_stamp of the existing index.

When you update an index, you can set the isolation level to Last Committed.

Returns

  • 0 = The index was created.
  • An error.

Example: Create an index

The example uses the following TimeSeries subtype definition, table, and data:

CREATE ROW TYPE myrow(tstamp datetime year to fraction(5), value1 real);

CREATE TABLE tsdata(id int primary key, ts1 timeseries(myrow));

INSERT INTO tsdata VALUES(1000, 
               "origin(2011-01-01 00:00:00.00000), calendar(ts_1month), 
                container(container), threshold(0), regular, 
                [(1),(1),(55),(55),(55),(55),(1),(45),(45),(45),(45),(1)]");

The following statement creates a pattern matching index on the value1 column in the TimeSeries subtype for the time series instance that has a primary key value of 1000:

SELECT TSCreatePatternIndex(ts1, 'tsdata', 'id',
                      1000,'2011-01-01 00:00:00.00000','2011-12-01 00:00:00.00000', 
                      'value1', 'idx_metatype=0 slidewin=4')
      FROM  tsdata WHERE id = 1000;

Example: Extend the index time range

The following statement extends the time range of the index from the previous example:

SELECT TSCreatePatternIndex(ts1, 'tsdata', 'id',
                      1000,'2011-12-01 00:00:00.00000','2014-12-01 00:00:00.00000', 
                      'value1', 'idx_metatype=0 slidewin=4')
      FROM  tsdata WHERE id = 1000;

The index is extended to December 1, 2014.

Example: Indexing a BSON field

The example uses the following TimeSeries subtype definition, table, and time series definition:

CREATE ROW TYPE ts_int (tstamp  datetime year to fraction(5), value bson);
    
    CREATE TABLE tsdata (id int primary key, ts timeseries(ts_int));

    EXECUTE PROCEDURE TSContainerCreate('container', 'rootdbs', 'ts_int', 00, 50);
    INSERT INTO tsdata VALUES(3000, 'origin(2011-01-01 00:00:00.00000), 
      calendar(ts_1min), container(container), threshold(0), regular, []);

The BSON document in the value column contains a field that is named v1 that contains numeric data, for example, {"v1":10}.

The following statement creates a pattern matching index on the v1 field in the value column of the TimeSeries subtype for the time series instance that has a primary key value of 3000:

SELECT TSCreatePatternIndex(ts,'tsdata','id','3000', 
           '2011-01-01 00:00:00.00000','2011-11-01 02:18:00.00000',
           'value.v1', 
           'idx_metatype=0 partitionlen=5 aggwin=1 slidewin=4 uerror=1.0',1)
      FROM tsdata 
      WHERE id = 3000;