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.
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
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
See also: