Administration: Omnidex Features

Attaching Data

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.

> 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

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 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.

----------------------------------- 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('*');
-------------------------------------------------------------------------------

Additional Resources

See also:

 
Back to top
admin/features/attach/queries.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0