This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:indexing:advanced:joins [2012/02/15 21:20] doc created |
admin:indexing:advanced:joins [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 12: | Line 12: | ||
| [[admin:indexing:advanced:subqueries|Subqueries]] | | [[admin:indexing:advanced:subqueries|Subqueries]] | | ||
| [[admin:indexing:advanced:distinct|Distinct]] | | [[admin:indexing:advanced:distinct|Distinct]] | | ||
| + | [[admin:indexing:advanced:aggregations|Aggregations]] | | ||
| [[admin:indexing:advanced:ordering|Ordering]] | [[admin:indexing:advanced:ordering|Ordering]] | ||
| ---- | ---- | ||
| - | ==== Overview ==== | + | ==== Joins ==== |
| - | A basic indexing strategy accommodates most queries, but there are advanced techniques that optimize more unusual situations. Queries with particularly complex criteria or complex table relationships can be optimized using these techniques. Omnidex also has features designed specifically to optimize distinct counts and other complex aggregations. | + | 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. |
| - | Before embarking on these techniques, it is important to understand [[admin:optimization:plans:home|Omnidex Query Plans]]. These plans describe the steps that are involved in processing a query, including the use of indexes. These query plans are essential to insuring the best performance for queries. | + | === Pre-joined Indexes === |
| - | == Criteria == | + | 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. |
| - | * Composite indexes | + | |
| - | * Expression-based Indexes | + | |
| - | * Rollup tables | + | |
| - | * Categorizing data | + | |
| - | == Joins == | + | 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. |
| - | * Prejoined indexes | + | |
| - | * Denormalization | + | |
| - | * Multi-level hierarchies | + | |
| - | * Record-complex indexing | + | |
| - | == Subqueries == | + | 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. |
| - | * Structure of subqueries | + | |
| - | * Joins instead of subqueries in FROM | + | |
| - | * | + | |
| - | == Distinct operations == | + | 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. |
| - | * Composite indexes | + | |
| - | * Grids: Distinct constraints | + | |
| - | == Group By == | + | === Record-complex indexes === |
| - | * Composite indexes | + | |
| - | * Rollup tables | + | 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. |
| - | * Grids: Distinct constraints | + | |
| - | + | 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. | |
| - | == Order By == | + | |
| - | * Composite indexes | + | 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. | ||