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/23 21:47] doc |
admin:indexing:advanced:joins [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 31: | Line 31: | ||
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. | 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 === |
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. | 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. | ||
Line 39: | Line 39: | ||
If these requirements can be met, record-complex indexes are extremely fast, essentially removing all costs of table joins. | If these requirements can be met, record-complex indexes are extremely fast, essentially removing all costs of table joins. | ||
- | ==== Denormalization ==== | + | === 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. | 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. |