This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:indexing:strategies:joins [2011/03/02 18:37] 127.0.0.1 external edit |
admin:indexing:strategies:joins [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ~~NOTOC~~ | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 16: | Line 17: | ||
| ==== Optimizing Table Joins ==== | ==== Optimizing Table Joins ==== | ||
| - | Tables joins are usually optimized by creating indexes on the table constraints. For relational databases, it is only necessary to index the foreign keys since the relational database's indexes on the primary keys will suffice. For raw data files, both the primary and foreign keys should be indexed. | + | Table joins are usually optimized by creating indexes on the table constraints. For relational databases, it is only necessary to index the foreign keys since the relational database's indexes on the primary keys will suffice. For raw data files, both the primary and foreign keys should be indexed. |
| Multi-column constraints should be indexed using multi-column Omnidex indexes. These are custom indexes that combine multiple columns into an index. The order of the columns in the indexes should match the order of the columns in the constraint. | Multi-column constraints should be indexed using multi-column Omnidex indexes. These are custom indexes that combine multiple columns into an index. The order of the columns in the indexes should match the order of the columns in the constraint. | ||
| Line 28: | Line 29: | ||
| In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS. | In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS. | ||
| - | <code sql> | + | <code> |
| select I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE | select I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE | ||
| from INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | from INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | ||
| where H.STATE = 'CO' and I.NAME = 'John'; | where H.STATE = 'CO' and I.NAME = 'John'; | ||
| </code> | </code> | ||
| + | |||
| For relational databases, the HOUSEHOLD column in INDIVIDUALS should be an Omnidex index since it is a foreign key. For raw data files, the HOUSEHOLD column in HOUSEHOLDS should also be an Omnidex index since it is a primary key. | For relational databases, the HOUSEHOLD column in INDIVIDUALS should be an Omnidex index since it is a foreign key. For raw data files, the HOUSEHOLD column in HOUSEHOLDS should also be an Omnidex index since it is a primary key. | ||
| Line 40: | Line 43: | ||
| In this example, the STATE and COUNTY_CODE is a primary constraint in COUNTIES and a foreign constraint in HOUSEHOLDS. | In this example, the STATE and COUNTY_CODE is a primary constraint in COUNTIES and a foreign constraint in HOUSEHOLDS. | ||
| - | <code sql> | + | <code> |
| select H.ADDRESS, H.CITY, H.STATE, H.ZIP | select H.ADDRESS, H.CITY, H.STATE, H.ZIP | ||
| from HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and | from HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and | ||
| Line 46: | Line 50: | ||
| where C.STATE = 'CO' and C.COUNTY = 'Boulder'; | where C.STATE = 'CO' and C.COUNTY = 'Boulder'; | ||
| </code> | </code> | ||
| + | |||
| For relational databases, the STATE and COUNTY_CODE columns of HOUSEHOLD should be indexed using a multi-column Omnidex index. For raw data files, the STATE and COUNTY_CODE columns of COUNTIES should also be a multi-column Omnidex index. For all databases, the COUNTY column of COUNTIES should be a QuickText index. | For relational databases, the STATE and COUNTY_CODE columns of HOUSEHOLD should be indexed using a multi-column Omnidex index. For raw data files, the STATE and COUNTY_CODE columns of COUNTIES should also be a multi-column Omnidex index. For all databases, the COUNTY column of COUNTIES should be a QuickText index. | ||
| Line 53: | Line 58: | ||
| This sample environment file shows the Omnidex indexes that will optimize these queries. | This sample environment file shows the Omnidex indexes that will optimize these queries. | ||
| - | <code sql> | + | <code> |
| create environment | create environment | ||
| in "simple.xml" | in "simple.xml" | ||
| Line 59: | Line 64: | ||
| create database "SIMPLE" | create database "SIMPLE" | ||
| - | type FLATFILE | + | type FILE |
| index_directory "idx" | index_directory "idx" | ||
| in "simple.xml"; | in "simple.xml"; | ||
| Line 76: | Line 81: | ||
| create table "HOUSEHOLDS" | create table "HOUSEHOLDS" | ||
| - | physical "dat\households.dat" | + | physical "dat/households.dat" |
| ( | ( | ||
| "HOUSEHOLD" CHARACTER(12) omnidex, | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| Line 91: | Line 96: | ||
| create table "INDIVIDUALS" | create table "INDIVIDUALS" | ||
| - | physical "dat\individuals.dat" | + | physical "dat/individuals.dat" |
| ( | ( | ||
| "INDIVIDUAL" CHARACTER(12), | "INDIVIDUAL" CHARACTER(12), | ||