This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
integration:rdbms:odbc:constraints [2011/04/07 03:26] doc |
integration:rdbms:odbc:constraints [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| ==== Constraints and Indexes ==== | ==== Constraints and Indexes ==== | ||
| - | Table constraints and indexes should be declared in the Omnidex Environment File. Omnidex will consider constraints and indexes when optimizing queries, and these are critical to some of the advanced optimization techniques in Omnidex. | + | Table constraints and indexes from the underlying database should be declared in the Omnidex Environment File. Omnidex uses primary and foreign key table constraints to understand the nature of the data and to optimize table joins. Declaring primary keys for a table also allows Omnidex to fulfill more queries solely in the indexes without having to access the database. Omnidex uses an understanding of the underlying indexes in the database to insure that the queries it submits through ODBC will be well optimized. |
| === Declaring Constraints === | === Declaring Constraints === | ||
| - | The table declarations in the Omnidex Environment File should include primary and foreign key constraints that establish parent-child relationships between the tables. While Omnidex does not validate or enforce these constraints; they are heavily used when optimizing queries. | + | The table declarations in the Omnidex Environment File should include primary and foreign key constraints that establish parent-child relationships between the tables. While Omnidex does not validate or enforce these constraints; they are heavily used when optimizing queries. Omnidex does not need to know about column constraints that exist in the underlying database, such as NULL, NOT NULL, CHECK, or DEFAULT constraints. |
| In most situations, the constraints declared in Omnidex should match those declared in the underlying database; however, administrators may alter these if they wish. At times, a parent-child relationship may exist between tables in the underlying database even though a constraint is not formally declared. These can be declared in Omnidex, allowing more advanced optimization of multi-table queries. | In most situations, the constraints declared in Omnidex should match those declared in the underlying database; however, administrators may alter these if they wish. At times, a parent-child relationship may exist between tables in the underlying database even though a constraint is not formally declared. These can be declared in Omnidex, allowing more advanced optimization of multi-table queries. | ||
| - | Omnidex requires that the datatype of a foreign key constraint match the datatype of the referencing constraint. If this is not the case within the ODBC datatype, it may be possible to remap the datatype in Omnidex so that they do match. Consult the documentation on [[integration:rdbms:odbc:datatypes|datatypes]] to see the options for remapping datatypes. | + | Omnidex requires that the datatype of a foreign key constraint match the datatype of the referencing constraint. If this is not the case within the underlying database, it may be possible to remap the datatype in Omnidex so that they do match. Consult the documentation on [[integration:rdbms:odbc:datatypes|datatypes]] to see the options for remapping datatypes. |
| Omnidex is tolerant of violations of foreign key constraints, such as foreign keys for which there are no primary keys in the corresponding tables. Omnidex is not tolerant of violations of primary key constraints, as these can lead to incorrect results returned from queries. | Omnidex is tolerant of violations of foreign key constraints, such as foreign keys for which there are no primary keys in the corresponding tables. Omnidex is not tolerant of violations of primary key constraints, as these can lead to incorrect results returned from queries. | ||
| Line 71: | Line 71: | ||
| === Declaring Indexes === | === Declaring Indexes === | ||
| - | Indexes that exist on primary and foreign key constraints in the underlying database should be declared as NATIVE indexes in the Omnidex Environment File. It can also be beneficial to declare other indexes from the underlying database as well. | + | Indexes that exist on primary and foreign key constraints in the underlying database should be declared as NATIVE indexes in the Omnidex Environment File. It can be beneficial to declare other indexes from the underlying database as well. |
| - | The Omnidex SQL Engine will evaluate the presence of native indexes when deciding how to process a statement. At times, it may be faster to rely on the native ODBC indexes than to use Omnidex indexes. This is especially true for searches on unique values, such as primary keys. | + | The Omnidex SQL Engine will evaluate the presence of native indexes when deciding how to process a statement. At times, it may be faster to rely on the indexes in the underlying database than to use Omnidex indexes. This is especially true for searches on unique values, such as primary keys. |
| Indexes from the underlying database are declared in the Omnidex Environment File using the [[dev:sql:statements:create_table:home|CREATE TABLE]] or [[dev:sql:statements:create_index:home|CREATE INDEX]] statements. The following example shows | Indexes from the underlying database are declared in the Omnidex Environment File using the [[dev:sql:statements:create_table:home|CREATE TABLE]] or [[dev:sql:statements:create_index:home|CREATE INDEX]] statements. The following example shows | ||