This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:features:attach:tables [2012/02/29 18:57] doc created |
admin:features:attach:tables [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 7: | Line 7: | ||
| ===== Attaching Data ===== | ===== Attaching Data ===== | ||
| - | **[[admin:features:attach:home|Overview]]** | | + | [[admin:features:attach:home|Overview]] | |
| - | [[admin:features:attach:tables|Attaching Tables]] | | + | **[[admin:features:attach:tables|Attaching Tables]]** | |
| - | [[admin:features:attach:tables|Attaching Databases]] | | + | [[admin:features:attach:databases|Attaching Databases]] | |
| [[admin:features:attach:queries|Query Optimization]] | [[admin:features:attach:queries|Query Optimization]] | ||
| Line 16: | Line 16: | ||
| ==== Attaching Tables ==== | ==== Attaching Tables ==== | ||
| - | Most Omnidex applications reference a single Omnidex environment, and that Omnidex environment represents one or more underlying databases. Sometimes applications need to access data outside of the Omnidex environment, such as connecting to additional tables or even additional databases. This can be done by attaching tables and databases to the current environment. | + | 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. |
| - | There are many uses for attaching tables and database. Omnidex queries can reference any of the tables in the connected environment, as well as those tables and databases that have been attached to the environment. This greatly expands the scope of queries that are possible. Attached databases can also have Omnidex indexing, allowing the query to be optimized using Omnidex indexes from all databases. | + | 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. |
| - | Some applications receive data from their users which is to be applied as search criteria. With Omnidex, this search criteria can be applied using two methods. If the data is simple, such as a file of customer numbers or zipcodes, then these can be easily referenced using [[admin:features:segments:home|Omnidex Segments]]. If the data is more complex and contains multiple columns or multiple tables, then these can be referenced as attached tables or databases. | + | 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 [[admin:features:attach:databases|attached]] as needed. Alternatively, a single Omnidex environment can permanently declare multiple database, each of a different type. |
| - | Some applications retain the result sets of searches performed against their databases. These results sets can be as simple as a file of customer numbers, or they can be more complex with multiple columns. If the result set is simple, it can be stored as a permanent Omnidex Segment; however, if the result set is complex, applications can export it to a small, indexed Omnidex database. This database can be attached in later queries to provide services like order suppression or statistical analysis. | + | |
| + | 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 [[dev:sql:statements:attach_table:home|ATTACH TABLE]] statement. The ATTACH TABLE syntax is nearly identical to CREATE TABLE, as shown below: | ||
| + | |||
| + | <code> | ||
| + | > 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 | ||
| + | </code> | ||
| + | |||
| + | The newly attached tables can be seen using the SHOW TABLES command in OdxSQL: | ||
| + | |||
| + | <code> | ||
| + | > 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 | ||
| + | </code> | ||
| ==== ==== | ==== ==== | ||
| - | **[[admin:features:attach:tables|Next]]** | + | **[[admin:features:attach:home|Prev]]** | |
| + | **[[admin:features:attach:databases|Next]]** | ||