This shows you the differences between two versions of the page.
|
admin:indexing:strategies:joins [2011/03/02 18:37] 127.0.0.1 external edit |
admin:indexing:strategies:joins [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Indexing Strategies ====== | ||
| - | |||
| - | ===== Basic Strategies ===== | ||
| - | |||
| - | [[admin:indexing:strategies:home|Overview]] | | ||
| - | [[admin:indexing:strategies:criteria|Criteria]] | | ||
| - | **[[admin:indexing:strategies:joins|Table Joins]]** | | ||
| - | [[admin:indexing:strategies:aggregations|Aggregations]] | | ||
| - | [[admin:indexing:strategies:orderby|Ordering]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== 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. | ||
| - | |||
| - | 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. | ||
| - | |||
| - | Not all joins follow actual constraints. Sometimes there is a parent-child relationship between tables but no formal constraint is declared. In these cases, create Omnidex indexes as though a constraint was declared. | ||
| - | |||
| - | The following examples show how to evaluate queries and choose the Omnidex indexes. Below the examples is an Omnidex Environment File that will generate these indexes. | ||
| - | |||
| - | == Example 1. Table joins on primary and foreign constraints == | ||
| - | |||
| - | In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS. | ||
| - | |||
| - | <code sql> | ||
| - | select I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE | ||
| - | from INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | ||
| - | where H.STATE = 'CO' and I.NAME = 'John'; | ||
| - | </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. | ||
| - | |||
| - | == Example 2. Table joins along multi-column constraints == | ||
| - | |||
| - | In this example, the STATE and COUNTY_CODE is a primary constraint in COUNTIES and a foreign constraint in HOUSEHOLDS. | ||
| - | |||
| - | <code sql> | ||
| - | select H.ADDRESS, H.CITY, H.STATE, H.ZIP | ||
| - | from HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and | ||
| - | H.COUNTY_CODE = C.COUNTY_CODE | ||
| - | where C.STATE = 'CO' and C.COUNTY = 'Boulder'; | ||
| - | </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. | ||
| - | |||
| - | == Sample Environment File == | ||
| - | |||
| - | This sample environment file shows the Omnidex indexes that will optimize these queries. | ||
| - | |||
| - | <code sql> | ||
| - | create environment | ||
| - | in "simple.xml" | ||
| - | with delete; | ||
| - | |||
| - | create database "SIMPLE" | ||
| - | type FLATFILE | ||
| - | index_directory "idx" | ||
| - | in "simple.xml"; | ||
| - | |||
| - | create table "COUNTIES" | ||
| - | physical "dat/counties.dat" | ||
| - | data_caching none | ||
| - | ( | ||
| - | "STATE" CHARACTER(2), | ||
| - | "COUNTY_CODE" CHARACTER(3), | ||
| - | "COUNTY" STRING(31) quicktext, | ||
| - | constraint COUNTIES_STATE_PK primary ("STATE", "COUNTY_CODE"), | ||
| - | omnidex "COUNTIES_PK" ("STATE", "COUNTY_CODE") | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | |||
| - | create table "HOUSEHOLDS" | ||
| - | physical "dat\households.dat" | ||
| - | ( | ||
| - | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| - | "ADDRESS" CHARACTER(50) quicktext, | ||
| - | "CITY" CHARACTER(28), | ||
| - | "STATE" CHARACTER(2) omnidex, | ||
| - | "ZIP" CHARACTER(5), | ||
| - | "COUNTY_CODE" CHARACTER(3), | ||
| - | "COUNTRY" CHARACTER(2), | ||
| - | constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), | ||
| - | omnidex "COUNTIES_FK" ("STATE", "COUNTY_CODE") | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | |||
| - | create table "INDIVIDUALS" | ||
| - | physical "dat\individuals.dat" | ||
| - | ( | ||
| - | "INDIVIDUAL" CHARACTER(12), | ||
| - | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| - | "NAME" CHARACTER(50) quicktext, | ||
| - | "GENDER" CHARACTER(1), | ||
| - | "BIRTHDATE" ANSI DATE, | ||
| - | "PHONE" CHARACTER(14), | ||
| - | "EMAIL" CHARACTER(60), | ||
| - | constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| - | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS" | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[admin:indexing:strategies:criteria|Prev]]** | | ||
| - | **[[admin:indexing:strategies:aggregations|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||