This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:advanced:criteria [2012/02/16 18:21] doc |
admin:indexing:advanced:criteria [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| === Multi-Column Indexes === | === Multi-Column Indexes === | ||
| - | Most Omnidex indexes are placed on a simple column; however, Omnidex indexes can also consist of multiple columns. This can be beneficial when certain combinations of criteria are repeated for a large percentage of queries. Normally, Omnidex would first search the COUNTRY index, and then intersect with the ACTIVE_CUSTOMER index; however, if a [[admin:indexing:creation:declaring|multi-column index]] exists that includes both of those columns, Omnidex can resolve both criteria in one index search. This will save time, and can make a noticeable difference across an entire application. | + | Most Omnidex indexes are placed on a simple column; however, Omnidex indexes can also consist of multiple columns. This can be beneficial when certain combinations of criteria are repeated for a large percentage of queries. When processing criteria, Omnidex normally searches one index and then intersects it with another index; however, if a [[admin:indexing:creation:declaring|multi-column index]] exists that includes both of those columns, Omnidex can resolve both criteria in one index search. This will save time and can make a noticeable difference across an entire application. |
| + | |||
| + | At the same time, this strategy implies redundant indexing. It is typical that the individual columns will be indexed independently so that they can be intersected with other columns as well. This strategy must produce enough benefit that it justifies the added indexing time and added disk space of redundant indexing. | ||
| This strategy makes the most sense in the following scenarios: | This strategy makes the most sense in the following scenarios: | ||
| Line 29: | Line 31: | ||
| * The table is very large, with at least a million rows. | * The table is very large, with at least a million rows. | ||
| * The combination of criteria is used in a majority of queries. | * The combination of criteria is used in a majority of queries. | ||
| - | * At least one of the columns has cardinality greater than 32, meaning that it will not be an Omnidex Bitmap | + | * At least one of the columns has cardinality greater than 32, meaning that it will not be an Omnidex Bitmap. |
| + | |||
| + | In these scenarios, it can make sense to create a small number of multi-column indexes to satisfy the most frequent combinations. | ||
| - | It is also typical that the columns are both indexed individually, and also indexed in a multi-column index. Although this causes redundant indexing, it allows criteria to be processed against the columns even when both columns are not referenced. | ||
| === Using Expression-based Columns === | === Using Expression-based Columns === | ||
| Line 44: | Line 47: | ||
| In very large databases, it can be beneficial to partition the data into an [[admin:features:grids:home|Omnidex Grid]]. Each Grid Node is then limited to rows that meet certain criteria. This means that queries that reference the partition column can be restricted to accessing only those nodes that match that criteria. This can have a substantial impact on performance, allowing many queries to be limited to just a node on an Omnidex Grid, rather than the entire database. For example, if an Omnidex Grid is partitioned by the STATE column, then any query with criteria against the STATE column will only need to access the node(s) for those states. | In very large databases, it can be beneficial to partition the data into an [[admin:features:grids:home|Omnidex Grid]]. Each Grid Node is then limited to rows that meet certain criteria. This means that queries that reference the partition column can be restricted to accessing only those nodes that match that criteria. This can have a substantial impact on performance, allowing many queries to be limited to just a node on an Omnidex Grid, rather than the entire database. For example, if an Omnidex Grid is partitioned by the STATE column, then any query with criteria against the STATE column will only need to access the node(s) for those states. | ||
| - | |||
| - | === Using Rollup Tables === | ||
| - | |||
| - | In very large databases, it can be beneficial to create [[admin:features:rollups:home|Rollup Tables]] to | ||
| - | |||
| ===== ===== | ===== ===== | ||