This shows you the differences between two versions of the page.
|
admin:indexing:strategies:criteria [2011/04/13 03:32] 127.0.0.1 external edit |
admin:indexing:strategies:criteria [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Indexing Strategies ====== | ||
| - | |||
| - | ===== Basic Strategies ===== | ||
| - | |||
| - | [[admin:indexing:strategies:home|Overview]] | | ||
| - | **[[admin:indexing:strategies:criteria|Criteria]]** | | ||
| - | [[admin:indexing:strategies:joins|Table Joins]] | | ||
| - | [[admin:indexing:strategies:aggregations|Aggregations]] | | ||
| - | [[admin:indexing:strategies:orderby|Ordering]] | ||
| - | ---- | ||
| - | |||
| - | ==== Optimizing Criteria ==== | ||
| - | |||
| - | Criteria is usually optimized by creating indexes on each column involved in the WHERE clause of a SQL statement. This is true regardless of the use of Boolean operators or parentheses. Normally, these will be installed with basic Omnidex indexes; however, some columns may contain textual data and would benefit from QuickText indexes. | ||
| - | |||
| - | The following examples show how to evaluate queries and choose the Omnidex indexes. Below the examples is an Omnidex Environment File that will generate these indexes. | ||
| - | |||
| - | == Example 1. Basic Criteria == | ||
| - | |||
| - | In the following statement, look for the columns used as criteria in the WHERE clause | ||
| - | |||
| - | <code sql> | ||
| - | select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP | ||
| - | from HOUSEHOLDS | ||
| - | where ((STATE = 'CO' and CITY = 'Boulder') or | ||
| - | (STATE = 'IL' and CITY = 'Chicago')); | ||
| - | </code> | ||
| - | |||
| - | The STATE and CITY columns should be Omnidex indexes. | ||
| - | |||
| - | == Example 2. Textual Criteria == | ||
| - | |||
| - | In this example, you might want to use QuickText indexes. QuickText indexes will parse and index each word in the column and will allow them to be searched case-insensitively. | ||
| - | |||
| - | <code sql> | ||
| - | select INDIVIDUAL, NAME, PHONE | ||
| - | from INDIVIDUALS | ||
| - | where BIRTHDATE = 'Jan 10, 1986' and NAME = 'John'; | ||
| - | </code> | ||
| - | |||
| - | The BIRTHDATE column should be an Omnidex index and the NAME column should be a Quicktext index. | ||
| - | |||
| - | == Example 3. Low-cardinality Criteria == | ||
| - | |||
| - | If a column has low cardinality, meaning that it has less than 32 distinct values, it should be indexed as an Omnidex Bitmap index. This improves performance and saves disk space. | ||
| - | |||
| - | <code sql> | ||
| - | select INDIVIDUAL, NAME, PHONE | ||
| - | from INDIVIDUALS | ||
| - | where GENDER = 'M' and NAME = 'John'; | ||
| - | </code> | ||
| - | |||
| - | The GENDER column should be an Omnidex Bitmap, and the NAME column should be a QuickText index. | ||
| - | |||
| - | == Sample Environment File == | ||
| - | |||
| - | This sample environment file shows the Omnidex indexes that will optimize these queries. | ||
| - | |||
| - | <code sql> | ||
| - | create environment | ||
| - | in "simple.xml" | ||
| - | with delete; | ||
| - | |||
| - | create database "SIMPLE" | ||
| - | type FILE | ||
| - | index_directory "idx" | ||
| - | in "simple.xml"; | ||
| - | |||
| - | create table "HOUSEHOLDS" | ||
| - | physical "dat/households.dat" | ||
| - | ( | ||
| - | "HOUSEHOLD" CHARACTER(12), | ||
| - | "ADDRESS" CHARACTER(50), | ||
| - | "CITY" CHARACTER(28) quicktext, | ||
| - | "STATE" CHARACTER(2) omnidex, | ||
| - | "ZIP" CHARACTER(5), | ||
| - | "COUNTRY" CHARACTER(2), | ||
| - | constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD") | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | |||
| - | create table "INDIVIDUALS" | ||
| - | physical "dat/individuals.dat" | ||
| - | ( | ||
| - | "INDIVIDUAL" CHARACTER(12), | ||
| - | "HOUSEHOLD" CHARACTER(12), | ||
| - | "NAME" CHARACTER(50) quicktext, | ||
| - | "GENDER" CHARACTER(1) omnidex bitmap, | ||
| - | "BIRTHDATE" ANSI DATE omnidex, | ||
| - | "PHONE" CHARACTER(14), | ||
| - | "EMAIL" CHARACTER(60), | ||
| - | constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| - | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS" | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[admin:indexing:strategies:home|Prev]]** | | ||
| - | **[[admin:indexing:strategies:joins|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||