This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
|
admin:indexing:indexes:groups [2011/01/18 21:20] els |
admin:indexing:indexes:groups [2011/03/18 16:49] deb |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 6: | Line 8: | ||
| [[admin:indexing:indexes:home|Overview]] | | [[admin:indexing:indexes:home|Overview]] | | ||
| - | [[admin:indexing:indexes:types|Types]] | | + | [[admin:indexing:indexes:types|Index Types]] | |
| - | [[admin:indexing:indexes:options|Options]] | | + | [[admin:indexing:indexes:options|Index Options]] | |
| **[[admin:indexing:indexes:groups|Index Groups]]** | **[[admin:indexing:indexes:groups|Index Groups]]** | ||
| ==== Index Groups ==== | ==== Index Groups ==== | ||
| + | Index Groups are used to combine multiple indexes into one single index. This allows a search against any one index within the group to automatically search all indexes within the group. For example, if a table has three lines, ADDRESS_LINE_1, ADDRESS_LINE_2, and ADDRESS_LINE_3, they can be indexed as an Index Group. Criteria against any one of those three columns will automatically search all address lines. | ||
| + | |||
| + | In the example below, a row displays an address: | ||
| + | |||
| + | ADDRESS_LINE_1 1254 Broadway Street | ||
| + | ADDRESS_LINE_2 Suite 100 | ||
| + | ADDRESS_LINE_3 Yonkers, NY 10710 | ||
| + | |||
| + | If these three columns are added to an Index Group, then the following search will locate the row: | ||
| + | |||
| + | <code sql> | ||
| + | select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | ||
| + | from CUSTOMERS | ||
| + | where ADDRESS_LINE_1 = '1254 Broadway, Yonkers, NY' | ||
| + | </code> | ||
| + | |||
| + | Care should be taken when considering where to use Index Groups. When indexes are combined into an Index Group, they lose the ability to distinguish which column originally contained the values. This can lead to confusion. For example, the following search will also locate the row: | ||
| + | |||
| + | <code sql> | ||
| + | select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | ||
| + | from CUSTOMERS | ||
| + | where ADDRESS_LINE_1 = '10710 Yonkers Street Suite 100' | ||
| + | </code> | ||
| + | |||
| + | === Common Uses of Index Groups === | ||
| + | |||
| + | Index Groups can be used in many different situations. Here is a common situation where Index Groups are used: | ||
| + | |||
| + | * Combining indexes for multiple address lines | ||
| + | * Combining indexes for a short-description column and a long-description column. | ||
| + | * Combining indexes for given names, nicknames, and aliases | ||
| + | * Combining indexes for multiple telephone numbers | ||
| + | * Combining indexes for multiple email addresses | ||
| + | * Combining indexes for multiple SIC or NAICS codes, or similar identifiers | ||
| + | |||
| + | === Index Groups with Multiple Tables === | ||
| + | |||
| + | Index Groups can combine indexes from multiple pre-joined tables. This can be particularly valuable when a search in a parent table needs to also search a child table at the same time. In this example, a HOUSEHOLDS table has a primary phone number and an INDIVIDUALS table has a mobile phone number for each member of the household. | ||
| + | |||
| + | Parent row: | ||
| + | |||
| + | HOUSEHOLDS.HOUSEHOLD 1234 | ||
| + | HOUSEHOLDS.HOME_PHONE 303 449 2198 | ||
| + | |||
| + | Child row: | ||
| + | |||
| + | INDIVIDUALS.HOUSEHOLD 1234 | ||
| + | INDIVIDUALS.INDIVIDUAL 5678 | ||
| + | INDIVIDUALS.MOBILE_PHONE 720 298 3162 | ||
| + | |||
| + | Child row: | ||
| + | |||
| + | INDIVIDUALS.HOUSEHOLD 1234 | ||
| + | INDIVIDUALS.INDIVIDUAL 5679 | ||
| + | INDIVIDUALS.MOBILE_PHONE 720 675 2238 | ||
| + | |||
| + | |||
| + | If HOUSEHOLDS.HOME_PHONE and INDIVIDUALS.MOBILE_PHONE were added to an Index Group, then this household would be returned in the following query. Note that the query searches the indexes in the INDIVIDUALS table **even though the INDIVIDUALS table is not referenced in the query.** | ||
| + | |||
| + | <code sql> | ||
| + | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
| + | from HOUSEHOLDS | ||
| + | where HOME_PHONE = '720 298 3162' | ||
| + | </code> | ||
| + | |||
| + | As discussed above, care should be taken when considering where to use Index Groups across tables. It can be confusing that when a table is searched without it being included in the SQL statement. A row can also be qualified when a portion of the criteria exists in the parent and another portion exists in a child row. For example, the following search will also locate this household: | ||
| + | |||
| + | <code sql> | ||
| + | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
| + | from HOUSEHOLDS | ||
| + | where HOME_PHONE = '303 298 2238' | ||
| + | </code> | ||
| + | |||
| + | Because of this characteristic, Index Groups are best suited for a) Omnidex Indexes, b) QuickText Indexes containing free-form text where the location of keywords is not material, or c) combinations of indexes where the data between columns is so different it would not be confused. | ||
| ==== ==== | ==== ==== | ||