This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:concepts:joins [2011/02/18 21:09] deb |
admin:indexing:concepts:joins [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Indexing Strategies ====== | + | ====== Administration: Omnidex Indexing ====== |
| ===== Indexing Concepts ===== | ===== Indexing Concepts ===== | ||
| Line 24: | Line 24: | ||
| It is common to join to a table so that criteria can be processed against it. The following example shows a join that returns columns from the INDIVIDUALS table to the user, but also joins to the HOUSEHOLDS and STATES tables to process criteria. These joins are Qualification Joins since no data is returned to the user from these tables. | It is common to join to a table so that criteria can be processed against it. The following example shows a join that returns columns from the INDIVIDUALS table to the user, but also joins to the HOUSEHOLDS and STATES tables to process criteria. These joins are Qualification Joins since no data is returned to the user from these tables. | ||
| - | <code sql> | + | <code> |
| select I.NAME, I.PHONE | select I.NAME, I.PHONE | ||
| Line 44: | Line 44: | ||
| Some tables are joined so that data can be returned to the user. The following example shows a join that returns columns from the INDIVIDUALS, HOUSEHOLDS and STATES tables. These joins are Retrieval Joins since data is returned from these tables to the user. | Some tables are joined so that data can be returned to the user. The following example shows a join that returns columns from the INDIVIDUALS, HOUSEHOLDS and STATES tables. These joins are Retrieval Joins since data is returned from these tables to the user. | ||
| - | <code sql> | + | <code> |
| select I.NAME, H.ADDRESS, H.CITY, H.STATE, I.PHONE, S.TAX_RATE | select I.NAME, H.ADDRESS, H.CITY, H.STATE, I.PHONE, S.TAX_RATE | ||
| Line 58: | Line 58: | ||
| === When to Avoid Indexes === | === When to Avoid Indexes === | ||
| - | When tables share a fairly specific key, such as a HOUSEHOLD ID, an index makes it easy to retrieve common rows between the two tables. This is only efficient when retrieving a small portion of each table. If two tables are to be joined in their entirety, indexing will actually slow things down for the same reasons discussed in tje [[admin:indexing:concepts:criteria|Criteria]] section. In the example above, the criteria against the NAME column probably isolates a small number of rows, and so using indexes for this join makes sense. | + | When tables share a fairly specific key, such as a HOUSEHOLD ID, an index makes it easy to retrieve common rows between the two tables. This is only efficient when retrieving a small portion of each table. If two tables are to be joined in their entirety, indexing will actually slow things down for the same reasons discussed in the [[admin:indexing:concepts:criteria|Criteria]] section. In the example above, the criteria against the NAME column probably isolates a small number of rows, and so using indexes for this join makes sense. |
| If the query retrieved a large portion of each table, indexing would be less helpful. This query below retrieves all rows in the United States, which probably comprises a large percentage of this database. | If the query retrieved a large portion of each table, indexing would be less helpful. This query below retrieves all rows in the United States, which probably comprises a large percentage of this database. | ||
| - | <code sql> | + | <code> |
| select I.NAME, H.ADDRESS, H.CITY, H.STATE, I.PHONE, S.TAX_RATE | select I.NAME, H.ADDRESS, H.CITY, H.STATE, I.PHONE, S.TAX_RATE | ||
| Line 74: | Line 74: | ||
| In this case, it may be faster to use other techniques than an indexed join. Omnidex provides a feature called a Sort-Merge Join. This technique sorts two tables or result sets and then scans them, matching entries as it goes. This can provide a significant improvement in performance. | In this case, it may be faster to use other techniques than an indexed join. Omnidex provides a feature called a Sort-Merge Join. This technique sorts two tables or result sets and then scans them, matching entries as it goes. This can provide a significant improvement in performance. | ||
| - | Omnidex provides another feature called the Hashed Data Cache. This can be even faster still, and is ideal when joining to a fairly small table, such as a small dimension table. If the required portion of a table can be read into memory with a hashed indexed, then joins to that table will be instantaneous. The disk drives are hit once to load the required portion of a table into memory, but thereafter, the disk drives are not impacted. This optimization is commonly used when joining to dimension tables or code lookup tables. In the example above, the STATES would be small enough to use this technique since it probably has less than 100 entries. | + | Omnidex provides another feature called the Hashed Data Cache. This can be even faster still, and is ideal when joining to a fairly small table, such as a small dimension table. If the required portion of a table can be read into memory with a hashed index, then joins to that table will be instantaneous. The disk drives are hit once to load the required portion of a table into memory, but thereafter, the disk drives are not impacted. This optimization is commonly used when joining to dimension tables or code lookup tables. In the example above, the STATES would be small enough to use this technique since it probably has less than 100 entries. |
| Omnidex automatically chooses between these approaches to table joins, and no intervention is required by the user. | Omnidex automatically chooses between these approaches to table joins, and no intervention is required by the user. | ||