This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:features:attach:queries [2012/02/29 21:49] doc created |
admin:features:attach:queries [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 16: | Line 16: | ||
| ==== Query Optimization ==== | ==== Query Optimization ==== | ||
| + | Queries can reference any table declared in the current Omnidex Environment File, any table declared in an attached database, or any table that has been directly attached. Omnidex will automatically optimize the query as well as possible using all available indexes. | ||
| + | |||
| + | Tables that have been individually attached do not have Omnidex indexing. Omnidex will still process queries that reference these tables, but the optimization will be more limited due to the lack of Omnidex indexing. Tables that are part of an attached database can have Omnidex indexing, allowing for more flexibility in optimization. | ||
| + | |||
| + | 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, the GEO database is attached along with its Omnidex indexes, and a standalone AREACODES table is attached as well. | ||
| + | |||
| + | <code> | ||
| + | > connect to simple | ||
| + | Connected to D:\class\labs\attach\simple.xml | ||
| + | |||
| + | > attach database GEO | ||
| + | >> from geo.xml; | ||
| + | Database GEO attached | ||
| + | |||
| + | > attach table "GEO"."AREACODES" | ||
| + | >> physical "dat/arc.dat" | ||
| + | >> ( | ||
| + | >> "AREACODE" character(3), | ||
| + | >> "TYPE" string(7), | ||
| + | >> "TYPE_DESC" string(31), | ||
| + | >> "ASSIGNABLE" character(1), | ||
| + | >> "EXPLANATION" string(31), | ||
| + | >> "RESERVED" character(1), | ||
| + | >> "ASSIGNED" character(1), | ||
| + | >> "ASSIGN_DATE" ascii date, | ||
| + | >> "GEOGRAPHIC" character(1), | ||
| + | >> "SERVICE" string(39), | ||
| + | >> "STATES" character(10), | ||
| + | >> "LOCATION" string(31), | ||
| + | >> "COUNTRY" character(2), | ||
| + | >> "AREA_SERVED" string(511), | ||
| + | >> "IN_SERVICE" character(1), | ||
| + | >> "IN_SERVICE_DATE" ascii date, | ||
| + | >> "OVERLAY" character(1), | ||
| + | >> "OVERLAY_COMPLEX" character(20), | ||
| + | >> "PARENT" character(32), | ||
| + | >> "TIME_ZONE1" character(1), | ||
| + | >> "TIME_ZONE2" character(1), | ||
| + | >> constraint AREACODES_AREACODE_PK primary ("AREACODE") | ||
| + | >> ); | ||
| + | Table AREACODES attached | ||
| + | |||
| + | </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 [[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. | ||
| + | |||
| + | <code> | ||
| + | ----------------------------------- SUMMARY ----------------------------------- | ||
| + | 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; | ||
| + | |||
| + | Version: 5.2.01 (Compiled Feb 29 2012 12:31:44) | ||
| + | Warnings: UNOPTIMIZED_CRITERIA, SEQUENTIAL_SCAN | ||
| + | Notes: Filter on column STATES will not be optimized because there is | ||
| + | not an Omnidex index installed on the column. | ||
| + | ----------------------------------- DETAILS ----------------------------------- | ||
| + | Processing subquery 1 into {SQ1}; | ||
| + | Retrieve AREACODES sequentially; | ||
| + | Filter AREACODES.STATES = 'CO'; | ||
| + | Returning subquery 1 into {SQ1}; | ||
| + | |||
| + | Qualify (ZIPCODES)ZIPCODES where AREACODES = '{SQ1=in (303,719,720,970)}' on 1; | ||
| + | Join ZIPCODES using ZIP to (HOUSEHOLDS)HOUSEHOLDS using ZIP on 1 ; | ||
| + | Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD on 1; | ||
| + | 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 {3} as (SELECT STATE, COUNTY, COUNTY_CODE FROM COUNTIES) on 5; | ||
| + | Aggregate INDIVIDUALS using HOUSEHOLD for GROUP(HOUSEHOLD), COUNT(*) on 1; | ||
| + | Retrieve {1} using PK_HOUSEHOLD = INDIVIDUALS.HOUSEHOLD; | ||
| + | Retrieve {2} using PK_ZIP = HOUSEHOLDS.ZIP; | ||
| + | Retrieve {3} using PK_MULTI2 = ZIPCODES.STATE, ZIPCODES.COUNTY_CODE; | ||
| + | Pass to queue {4} [COUNTIES.STATE, COUNTIES.COUNTY, COUNT('*')]; | ||
| + | Sort {4} for GROUP BY [COUNTIES.STATE, COUNTIES.COUNTY]; | ||
| + | Retrieve {4} sequentially; | ||
| + | Return COUNTIES.STATE, COUNTIES.COUNTY, COUNT('*'); | ||
| + | ------------------------------------------------------------------------------- | ||
| + | </code> | ||
| Line 23: | Line 117: | ||
| ====== Additional Resources ====== | ====== Additional Resources ====== | ||
| - | |||
| See also: | See also: | ||