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