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:14] doc |
admin:features:attach:queries [2016/06/28 22:38] (current) |
||
---|---|---|---|
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> |