This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:advanced:joins [2012/02/16 20:17] doc |
admin:indexing:advanced:joins [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 19: | Line 19: | ||
| ==== Joins ==== | ==== Joins ==== | ||
| - | Table joins can add time to a query using any database system. | + | Table joins can add time to a query using any database system. Omnidex has several techniques for optimizing joins. By default, indexing the primary and foreign key constraints with Omnidex allows many joins to be optimized without further effort. This is especially true for joins where a child table is being filtered based on criteria in a parent table. Some join scenarios are more complex than this, requiring some of the following techniques. |
| === Pre-joined Indexes === | === Pre-joined Indexes === | ||
| + | Omnidex indexes normally record information about the column and table in which they reside. For example, and index on the NAME column would contain keywords from the NAME column along with either primary key or rowid information. Omnidex can also pre-join indexes, which means that they will also record the primary key from a parent table as well. Pre-joining will improve the speed of joining from that child to the owning parent. | ||
| + | Pre-joined indexes are most commonly used when searching a parent table and it's many child tables. For example, CUSTOMERS table make have many child tables that describe the customer, such as orders, characters, mailings, demographics, and so forth. In these situations, the child tables are often pre-joined to the parent, resulting in fast navigation between the family of tables. | ||
| + | |||
| + | Pre-joined indexes can also be organized into a hierarchy. A child table can be pre-joined to a parent, and that parent can be pre-joined to a grandparent. This is helpful with more complex, relational data models. | ||
| + | |||
| + | Pre-joined indexes require about twice the disk space and are about half as fast in actual qualification the data. Additionally, Omnidex Bitmap indexes cannot be pre-joined. For these reasons, pre-joined indexes are generally not the first course of action for indexing. Nevertheless, if a query is experiencing substantial overhead in processing the joins, then the costs of pre-joined indexes can be justified. | ||
| + | |||
| + | === Record-complex indexes === | ||
| + | |||
| + | Record-complex indexes are a variation of pre-joined indexes. Record-complex indexes in a child table contain the parent's primary key, but no identifying information about the child itself. These indexes are useful when a parent is being qualified using criteria from a child, but without retrieving rows from the child. | ||
| + | |||
| + | Since record-complex indexes only contain the parent's primary key, only one index can be used in the child table before returning to the parent. If multiple columns are to be searched in the child table, a multi-column, record-complex index must be created so that only one index is searched before returning to the parent. | ||
| + | |||
| + | If these requirements can be met, record-complex indexes are extremely fast, essentially removing all costs of table joins. | ||
| + | |||
| + | === Denormalization === | ||
| + | |||
| + | The fasted way to improve the speed of a table join is to not do it at all. If the data model allows, denormalizing a child and one or more parents can be extremely efficient. Omnidex enables this solution more frequently since Omnidex applications are often separate and derived from the underlying database. This allows the Omnidex database to enjoy flexibility with the data model that may not be possible with the underlying database. | ||
| - | * Prejoined indexes | ||
| - | * Denormalization | ||
| - | * Multi-level hierarchies | ||
| - | * Record-complex indexing | ||
| ===== ===== | ===== ===== | ||