Administration: Omnidex Features

Omnidex Segments

Data Segments

Overview

Applications often need to pass blocks of data between queries, and even between connections. For example, many applications maintain a permanent record of the results of a query, such as recording all of the customer numbers associated a mailing. In this example, the customer numbers may be reused at a later date to fulfill a similar order, or may be used as criteria to control whether a current order overlaps a previous order. Applications often resort to inserting rows into a table to record these results; however, these inserts are time-consuming. Moreover, these tables get quite large, and accessing them in future queries becomes a performance issue.

Omnidex Data Segments provide an high-performing, alternate approach. An Omnidex Data Segment is simply a file of values, optionally given an object name within the Omnidex connection. Segments can then be referenced in future queries without requiring any temporary tables or joins. This can dramatically improve the performance of the application.

Some applications receive receive blocks of data from their users to be applied as criteria in a query. Some applications may receive a file of preselected customers; other applications may receive a file of zip codes or similar data. These files can be treated as an Omnidex Data Segment as well, making it very easy to apply that data as criteria in a query.

Omnidex Data Segments can be given an object name so there is a convenient level of abstraction. This is helpful when the application's query engine needs to avoid dealing with specifics such as filenames. At the same time, Omnidex Data Segments can also be referenced directly by their filename when convenient.

Creating Data Segments

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 data segment PART_1
>>  as (select        INDIVIDUALS.INDIVIDUAL
>>        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

Using Index Segments in Queries

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

Dropping Segments

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

Additional Resources

See also:

 
Back to top
admin/features/segments/data.txt ยท Last modified: 2016/06/28 22:38 (external edit)