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 22:57] doc |
admin:features:attach:queries [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 16: | Line 16: | ||
| ==== Query Optimization ==== | ==== Query Optimization ==== | ||
| - | Omnidex allows entire databases to be attached to the current Omnidex Environment, incorporating all of their tables and Omnidex indexes. This can greatly expand the data available to query, and enhance the ability to optimize queries across multiple databases within the enterprise. Any type of database from any Omnidex Environment can be attached. This allows great flexibility in linking multiple applications together, such as linking a SQL Server customer database with a Raw Data File data warehouse. | + | 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. |
| - | Attaching databases can also be useful for applications that reference the results from previous queries in future queries. If the result set consists of a single column, such as a primary key, this can be done simply using [[admin:features:segments:home|Omnidex Segments]]; however, some applications identify the results of a previous query using multiple columns, or even multiple tables. Omnidex allows these results to be rendered into a miniature Omnidex database, complete with Omnidex indexing. Future queries can attach as many result set databases as needed to perform order suppression or other tasks, all taking advantage of the Omnidex 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. |
| - | Attaching a database is simple, and uses the ATTACH DATABASE statement, as shown below: | + | 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> | <code> | ||
| - | attach database GEO | + | > connect to simple |
| - | from geo.xml; | + | 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> | </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> | ||
| + | |||
| ==== ==== | ==== ==== | ||
| - | **[[admin:features:attach:tables|Prev]]** | | + | **[[admin:features:attach:databases|Prev]]** |
| - | **[[admin:features:attach:queries|Next]]** | + | |