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