This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:attach:queries [2012/02/29 23:12] doc |
admin:features:attach:queries [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 22: | Line 22: | ||
| No special syntax is required to reference attached tables. It may be necessary to qualify a table by its database if there are duplicates among the databases. | No special syntax is required to reference attached tables. It may be necessary to qualify a table by its database if there are duplicates among the databases. | ||
| - | In the following example, a GEO database is attached along with its Omnidex indexes, and a standalone AREACODES table is attached as well. | + | In the following example, the GEO database is attached along with its Omnidex indexes, and a standalone AREACODES table is attached as well. |
| <code> | <code> | ||
| Line 62: | Line 62: | ||
| </code> | </code> | ||
| - | The following query then accesses all of these tables, without regard for whether the table was attached or not. Note that the tables have database qualifiers to insure that the correct table is referenced. This is important since some table names are used in both the GEO database and the SIMPLE database: | + | The following query then accesses all of these tables, without regard for whether the table was attached or not. Note that the tables have database qualifiers to insure that the correct table is referenced. This is important since some table names are used in both the GEO database and the SIMPLE database. |
| - | <code> | + | The [[admin:optimization:plans:home|query plan]] for this query shows that Omnidex indexes from both the SIMPLE and GEO databases are used to optimize this query. No indexing exists on the AREACODES table, so this part of the query is done without indexing. |
| - | > select GEO.COUNTIES.STATE, | + | |
| - | >> GEO.COUNTIES.COUNTY, | + | |
| - | >> count(*) | + | |
| - | >> from SIMPLE.INDIVIDUALS | + | |
| - | >> join SIMPLE.HOUSEHOLDS on SIMPLE.INDIVIDUALS.HOUSEHOLD = | + | |
| - | >> SIMPLE.HOUSEHOLDS.HOUSEHOLD | + | |
| - | >> join GEO.ZIPCODES on SIMPLE.HOUSEHOLDS.ZIP = GEO.ZIPCODES.ZIP | + | |
| - | >> join GEO.COUNTIES on GEO.ZIPCODES.STATE = GEO.COUNTIES.STATE and | + | |
| - | >> GEO.ZIPCODES.COUNTY_CODE = GEO.COUNTIES.COUNTY_CODE | + | |
| - | >> where GEO.ZIPCODES.AREACODES in | + | |
| - | >> (select AREACODE | + | |
| - | >> from AREACODES | + | |
| - | >> where STATES = 'CO') | + | |
| - | >> group by GEO.COUNTIES.STATE, | + | |
| - | >> GEO.COUNTIES.COUNTY; | + | |
| - | + | ||
| - | ST COUNTY COUNT('*') | + | |
| - | -- ------------------------------- ---------- | + | |
| - | CO Adams 10 | + | |
| - | CO Arapahoe 8 | + | |
| - | CO Bent 1 | + | |
| - | CO Boulder 10 | + | |
| - | CO Chaffee 4 | + | |
| - | CO Costilla 2 | + | |
| - | CO Denver 31 | + | |
| - | CO Dolores 1 | + | |
| - | CO El Paso 7 | + | |
| - | CO Gunnison 3 | + | |
| - | CO Huerfano 2 | + | |
| - | CO Jefferson 18 | + | |
| - | CO La Plata 4 | + | |
| - | CO Larimer 10 | + | |
| - | CO Logan 2 | + | |
| - | CO Mesa 18 | + | |
| - | CO Moffat 5 | + | |
| - | CO Montezuma 2 | + | |
| - | CO Montrose 2 | + | |
| - | CO Morgan 2 | + | |
| - | CO Otero 1 | + | |
| - | CO Pueblo 6 | + | |
| - | CO Rio Blanco 1 | + | |
| - | CO Weld 7 | + | |
| - | </code> | + | |
| - | + | ||
| - | The query plan for this query shows that Omnidex indexes from both the SIMPLE and GEO databases are used to optimize this query. No indexing exists on the AREACODES table, so this part of the query is done without indexing. | + | |
| <code> | <code> | ||
| Line 139: | Line 94: | ||
| Returning subquery 1 into {SQ1}; | Returning subquery 1 into {SQ1}; | ||
| - | Qualify (ZIPCODES)ZIPCODES where AREACODES = '{SQ1=in (303,719,720,970)}' on 1 | + | Qualify (ZIPCODES)ZIPCODES where AREACODES = '{SQ1=in (303,719,720,970)}' on 1; |
| - | with NOAUTORESET; | + | Join ZIPCODES using ZIP to (HOUSEHOLDS)HOUSEHOLDS using ZIP on 1 ; |
| - | Join ZIPCODES using ZIP to (HOUSEHOLDS)HOUSEHOLDS using ZIP on 1 with | + | Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD on 1; |
| - | NOAUTORESET; | + | |
| - | Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD on | + | |
| - | 1 with NOAUTORESET; | + | |
| Build cache {1} as (SELECT HOUSEHOLD, ZIP FROM HOUSEHOLDS) on 3; | Build cache {1} as (SELECT HOUSEHOLD, ZIP FROM HOUSEHOLDS) on 3; | ||
| Build cache {2} as (SELECT ZIP, STATE, COUNTY_CODE FROM ZIPCODES) on 4; | Build cache {2} as (SELECT ZIP, STATE, COUNTY_CODE FROM ZIPCODES) on 4; | ||