This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
admin:indexing:indexes:groups [2011/03/18 16:49] deb |
admin:indexing:indexes:groups [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 24: | Line 24: | ||
If these three columns are added to an Index Group, then the following search will locate the row: | If these three columns are added to an Index Group, then the following search will locate the row: | ||
- | <code sql> | + | <code> |
select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | ||
from CUSTOMERS | from CUSTOMERS | ||
Line 32: | Line 32: | ||
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: | 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> | + | <code> |
select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | select NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 | ||
from CUSTOMERS | from CUSTOMERS | ||
Line 73: | Line 73: | ||
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.** | 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> | + | <code> |
select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
from HOUSEHOLDS | from HOUSEHOLDS | ||
Line 79: | Line 79: | ||
</code> | </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: | + | As discussed above, care should be taken when considering where to use Index Groups across tables. It can be confusing when a table is actually searched without it being referenced 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> | + | <code> |
select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
from HOUSEHOLDS | from HOUSEHOLDS |