Overview | Attaching Tables | Attaching Databases | Query Optimization

Omnidex applications begin by connecting to an Omnidex Environment, providing access to all of the databases and tables declared in the environment. This can be temporarily expanded by attaching additional tables to the current connection. Attaching a table allows it to be accessed in statements along with the other tables in the environment.

Raw data files can be attached to any connection to an Omnidex Environment. If the database in the environment is already a FILE type, meaning that it references raw data files, then the attached table can be placed directly into that database. If the database in the environment is a relational type, then the raw data files can be attached in the temp database, $TEMPDB, which is always present for all connections.

Relational tables can be attached to a connection as long as it matches the database type declared in the Omnidex environment. For example, if an Omnidex Environment references a Microsoft SQL Server database, then additional SQL Server tables can be attached; however, Oracle tables cannot since they are not the same database type. If multiple database types are required, then each database can be declared in a separate Omnidex environment and attached as needed. Alternatively, a single Omnidex environment can permanently declare multiple database, each of a different type.

The following restrictions apply to attached tables:

• Attached tables cannot have Omnidex indexing declared.
• Attached tables cannot be partitioned as part of an Omnidex Grid.
• Attached tables can only be attached into the current connection.

Attaching a table is simple, and uses the ATTACH TABLE statement. The ATTACH TABLE syntax is nearly identical to CREATE TABLE, as shown below:

> connect to simple
Connected to D:\class\labs\attach\simple.xml

> attach table                   "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 newly attached tables can be seen using the SHOW TABLES command in OdxSQL:

> show tables

                                    Tables
                                    ------

Environment
  Database
    Table                               Length Cols         Rows Type
----------------------------------------------------------------------------
USER_ENVIRONMENT
  SIMPLE
    COUNTRIES                               98    7          239 FIXED
    STATES                                  44    6           76 FIXED
    GENDERS                                 33    2            2 FIXED
    HOUSEHOLDS                              99    6        1,909 FIXED
    INDIVIDUALS                            159    7        5,000 FIXED
  $TEMPDB
   *AREACODES                              747   21          800 FIXED

Prev | Next

See also: