Overview | Index Segments | Data Segments | Creating Segments | Querying Segments | Dropping Segments

Some applications process their queries in multiple steps. The first step isolates some rows based on a first group of criteria. The second step either refines those rows, or isolates a new set of rows based on a second group of criteria. At the end of the query, all of the steps are combined to produce a final result set.

With relational databases, these multi-step queries are often done by managing a series of temporary tables, usually containing only one column such as a primary key. Each step of the query refines these temporary tables, ultimately producing a final table with the final result set. This approach is significantly slower due to the writing and rewriting of the temporary tables; however, the need for this approach can be a fundamental requirement of the application.

Omnidex Segments provide a higher-speed approach to multi-step queries. Instead of writing and rewriting temporary tables, Omnidex Segments will record the index pointers for each step into temporary objects and allow those segments to be reused in future queries. These index segments are much more efficient than temporary tables, and can be directly applied against Omnidex indexes in future queries. This greatly improves the performance of multi-step queries.

Segments are easily creating using the CREATE SEGMENT statement. The following example shows a series of segments being created in a multi-step query.

> ; Create an index segment for Individuals in Denver, CO
> create index segment PART_1
>>  as (select        INDIVIDUALS.$UNIQUEKEY
>>        from        INDIVIDUALS
>>        join        HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD
>>        where       STATE = 'CO' and
>>                    CITY = 'Denver');
Segment PART_1 created with 37 rows

> ; Create an index segment for Individuals in Phoenix, AZ;
-> create index segment PART_2
>>  as (select        INDIVIDUALS.$UNIQUEKEY
>>        from        INDIVIDUALS
>>        join        HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD
>>        where       STATE = 'AZ' and
>>                    CITY = 'Phoenix');
Segment PART_2 created with 27 rows

> ; Create an index segment for Individuals born since 1990;
> create index segment PART_3
>>  as (select        INDIVIDUALS.$UNIQUEKEY
>>        from        INDIVIDUALS
>>        where       BIRTHDATE >= 'January 1, 1990');
Segment PART_3 created with 1,198 rows

Omnidex Segments can be referenced as criteria in a future query using the $SEGMENT function. The $SEGMENT function references the object name for any segment previously created in the same connection. Since Omnidex Segments already contain index pointers, they are incorporated into a search with very high performance.

The following example shows several segments that were above being referenced in a query using the $SEGMENT function:

> ; Fulfill the query based on the final segment;
> select        INDIVIDUAL,
>>               BIRTHDATE,
>>               CITY,
>>               STATE
>>   from        INDIVIDUALS
>>   join        HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD
>>   where       INDIVIDUAL in ($segment(PART_1), $segment(PART_2)) and
>>               INDIVIDUAL = $segment(PART_3);

INDIVIDUAL    BIRTHDATE     CITY                          ST
------------  ------------  ----------------------------  --
000400191550  1996-04-26    PHOENIX                       AZ
000400575367  1996-11-19    PHOENIX                       AZ
000400575366  1995-09-26    PHOENIX                       AZ
000400575369  2005-06-29    PHOENIX                       AZ
000400575368  2004-07-30    PHOENIX                       AZ
000403467439  1994-08-28    PHOENIX                       AZ
000403467441  2003-04-19    PHOENIX                       AZ
000403467440  1997-02-28    PHOENIX                       AZ
000800345452  1998-08-31    DENVER                        CO
000800345453  2003-12-25    DENVER                        CO
000801610627  2004-03-17    DENVER                        CO
000802420583  1990-03-18    DENVER                        CO
000802420584  1993-10-06    DENVER                        CO
000803400688  1990-06-16    DENVER                        CO
000803400689  1990-10-25    DENVER                        CO
000803760388  2006-07-30    DENVER                        CO
000803760386  1995-12-09    DENVER                        CO
000803760387  1997-09-24    DENVER                        CO

18 rows returned

Omnidex Segments can be dropped, resulting in the removal of the object from the connection, and also the removal of the underlying data. It is not required to drop segments, as they will be automatically dropped when the connection ends. Dropping segments is required when reusing the same connection name within the same connection.

Omnidex Segments are dropped using the DROP SEGMENT statement, as shown in the example below:

> drop segment                   PART_1;
Segment PART_1 dropped
> drop segment                   PART_2;
Segment PART_2 dropped
> drop segment                   PART_3;
Segment PART_3 dropped

Next

See also: