This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:creation:declaring [2012/01/25 22:47] doc |
admin:indexing:creation:declaring [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 4: | Line 4: | ||
| ====== Administration: Omnidex Indexing ====== | ====== Administration: Omnidex Indexing ====== | ||
| - | ===== Indexing Creation ===== | + | ===== Index Creation ===== |
| [[admin:indexing:creation:home|Overview]] | | [[admin:indexing:creation:home|Overview]] | | ||
| **[[admin:indexing:creation:declaring|Declaring Indexes]]** | | **[[admin:indexing:creation:declaring|Declaring Indexes]]** | | ||
| [[admin:indexing:creation:updating|Updating Indexes]] | | [[admin:indexing:creation:updating|Updating Indexes]] | | ||
| - | [[admin:indexing:creation:standalone|Standalone Indexes]] | | ||
| [[admin:indexing:creation:files|Index Files]] | | [[admin:indexing:creation:files|Index Files]] | | ||
| - | [[admin:indexing:creation:updating|Maintenance]] | + | [[admin:indexing:creation:performance|Performance]] |
| + | ---- | ||
| ==== Declaring Indexes ==== | ==== Declaring Indexes ==== | ||
| - | The easiest way to add Omnidex Indexing is to add indexing information beside each column. This will not work in all situations, such as when multi-column indexes are being created; however, it will work in many simpler installations. More complex installations will require the more complex features described in [[dev:sql:statements:create_table:home|CREATE TABLE]] and [[dev:sql:statements:create_index:home|CREATE INDEX]]. The example below shows the basic addition of Omnidex indexing to the same database. | + | The easiest way to add Omnidex indexes to a table is to append indexing information at the end of each column in the CREATE TABLE statement. This will suffice for most basic installations of Omnidex. The example below shows adding indexes to a table. Some columns have "omnidex" next to them, indicating that a standard Omnidex index should be created. Other columns have "quicktext" next to them, indicating that QuickText index should be created. |
| - | <code sql> | + | |
| - | > create environment | + | <code> |
| - | >> in "simple.xml" | + | |
| - | >> with delete; | + | |
| - | Environment created in simple.xml | + | |
| - | > | + | |
| - | > | + | |
| - | > create database "SIMPLE" | + | |
| - | >> type FILE | + | |
| - | >> index_directory "idx" | + | |
| - | >> in "simple.xml"; | + | |
| - | Database SIMPLE created in simple.xml | + | |
| - | > | + | |
| - | > | + | |
| > create table "HOUSEHOLDS" | > create table "HOUSEHOLDS" | ||
| >> physical "dat/households.dat" | >> physical "dat/households.dat" | ||
| Line 45: | Line 32: | ||
| Table HOUSEHOLDS created in simple.xml | Table HOUSEHOLDS created in simple.xml | ||
| > | > | ||
| - | > | + | </code> |
| - | > create table "INDIVIDUALS" | + | |
| - | >> physical "dat/individuals.dat" | + | Some indexes require more options or more information. For example, a multi-column index - an index containing more than one column - cannot be created by simply putting a keyword next to a column. These indexes can be created one of two ways. The first, and most common way, is to declare them in the CREATE TABLE statement following all of the constraint declarations as shown in the example below. |
| + | |||
| + | <code> | ||
| + | > create table "HOUSEHOLDS" | ||
| + | >> physical "dat/households.dat" | ||
| >> ( | >> ( | ||
| - | >> "INDIVIDUAL" CHARACTER(12) omnidex, | ||
| >> "HOUSEHOLD" CHARACTER(12) omnidex, | >> "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| - | >> "NAME" CHARACTER(50) quicktext, | + | >> "ADDRESS" CHARACTER(50) quicktext, |
| - | >> "GENDER" CHARACTER(1) omnidex bitmap, | + | >> "CITY" CHARACTER(28) quicktext, |
| - | >> "BIRTHDATE" ANSI DATE omnidex, | + | >> "STATE" CHARACTER(2) omnidex, |
| - | >> "PHONE" CHARACTER(14) omnidex, | + | >> "ZIP" CHARACTER(5) omnidex, |
| - | >> "EMAIL" CHARACTER(60) quicktext, | + | >> "COUNTRY" CHARACTER(2) omnidex, |
| - | >> constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | + | >> constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), |
| - | >> constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS" | + | >> omnidex STATE_CITY_ZIP ("STATE", "CITY", "ZIP") case_insensitive |
| >> ) | >> ) | ||
| >> in "simple.xml"; | >> in "simple.xml"; | ||
| - | Table INDIVIDUALS created in simple.xml | + | Table HOUSEHOLDS created in simple.xml |
| + | > | ||
| </code> | </code> | ||
| + | Omnidex indexes can also be created using CREATE INDEX statement. This is a more traditional approach; however, most administrators prefer to use the CREATE TABLE statement since it is easier to differentiate which columns are indexed and which are not. In either case, these statements only declare the indexes, but do not populate the indexes. | ||
| + | |||
| + | <code> | ||
| + | > create omnidex index "STATE_CITY_ZIP" | ||
| + | >> on "HOUSEHOLDS" | ||
| + | >> ( | ||
| + | >> "STATE", | ||
| + | >> "CITY", | ||
| + | >> "ZIP" | ||
| + | >> ) | ||
| + | >> in "simple.xml"; | ||
| + | Index STATE_CITY_ZIP created in simple.xml | ||
| + | > | ||
| + | </code> | ||
| ==== ==== | ==== ==== | ||