This is an old revision of the document!
CREATE TABLE table /* table options specified before the column and constraint specifications */ [NODE node] [OPTIONS “options”] [PHYSICAL “physical_file” | "filespec" | shell_command ] [AUTOFILTER “criteria” ] [DATA_CACHING < cache_size | DYNAMIC | NONE >] [PARTITION_BY “criteria” ] [INDEX_MAINTENANCE index_maintenance ] /* prefix table options to create a temporary table inserted before TABLE keyword */ [[ <GLOBAL | LOCAL> ] TEMPORARY ] /* column specifications */ ( column_name column_datatype optional_omnidex_index /* Omnidex Index Types */ [< OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE> [ INDEX ] /* column options */ [DEFAULT < literal | niladic-function | NULL >] [USAGE usage] [FORMAT format] [ , ] /* separator between column and constraint definitions */ /* Constraint Specifications - native indexes and Omnidex indexes */ [CONSTRAINT constraint] /* RDBMS Keys */ <[NOT] NULL | UNIQUE [KEY] | DISTINCT [KEY] | PRIMARY [KEY] | [FOREIGN [KEY]] REFERENCES table(column[, column …]) [PREJOIN]>] [KEYWORDING] [PROXIMITY] [<CASE_INSENSITIVE | CASE_SENSITIVE>] [STANDALONE] [BITMAP] [EXCLUDED_WORDS] [PHONETIC] [PREJOIN table] [<RECORD_SPECIFIC | RECORD_COMPLEX>]] [AS “select_item”] [[,] [CONSTRAINT constraint] <UNIQUE [KEY] (column [, column …]) | DISTINCT [KEY] (column [, column …]) | PRIMARY [KEY] (column [, column …]) | FOREIGN [KEY] (column [, column …]) REFERENCES table(column [, column …] [PREJOIN]>] [[,] <OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE> [INDEX] index (<column | substring> [, <column | substring>…]) [KEYWORDING] [PROXIMITY] [ <CASE_INSENSITIVE | CASE_SENSITIVE> ] [STANDALONE] [BITMAP] [EXCLUDED_WORDS] [PHONETIC] [PREJOIN table] [<RECORD_SPECIFIC | RECORD_COMPLEX>]] [[,] INDEX GROUP [(owner)]group (index, index [, index ...]) )] [AS “sql_statement”] [< IN “filename” | ON [INSTANCE] instance >] [WITH options]
CREATE ENVIRONMENT and CREATE DATABASE have to be specified before specifying CREATE TABLE.
The IN “filespec.xml” clause has to be specified after the column definitions.
Tables must be declared in order of their constraints.
Parent tables should be declared before children tables.
create table "HOUSEHOLDS" physical "dat\households*.dat" ( "HOUSEHOLD" character(12), "ADDRESS" character(50), "CITY" character(28), "STATE" character(2), "ZIP" character(5), "COUNTRY" character(2), constraint HOUSEHOLD_HOUSEHOLD_PK primay ("HOUSEHOLD"), constraint HOUSEHOLD_STATE_fk FOREIGN ("STATE") references "states", constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" ) in "simple.xml";
Here is the same CREATE TABLE statement as above but with the Omnidex Index type specified for each column.
create table "HOUSEHOLDS" physical "dat\households*.dat" ( "HOUSEHOLD" character(12) omnidex, "ADDRESS" character(50) quicktext, "CITY" character(28) quicktext, "STATE" character(2) omnidex, "ZIP" character(5) omnidex, "COUNTRY" character(2) omnidex, constraint HOUSEHOLD_HOUSEHOLD_PK primay ("HOUSEHOLD"), constraint HOUSEHOLD_STATE_fk FOREIGN ("STATE") references "states", constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" ) in "simple.xml";
Here is the same CREATE TABLE statement as above but with the Omnidex Index type specified for each column.
Additionally, it creates an Omnidex Composite index and combines the STATE and CITY columns into a single index.
create table "HOUSEHOLDS" physical "dat\households*.dat" ( "HOUSEHOLD" character(12) omnidex, "ADDRESS" character(50) quicktext, "CITY" character(28) quicktext, "STATE" character(2) omnidex, "ZIP" character(5) omnidex, "COUNTRY" character(2) omnidex, constraint HOUSEHOLD_HOUSEHOLD_PK primay ("HOUSEHOLD"), constraint HOUSEHOLD_STATE_fk FOREIGN ("STATE") references "states", constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" omnidex index "HSHD_STATE_CITY" ("STATE","CITY") ) in "simple.xml";
There are three types of Omnidex indexes called Omnidex, QuickText, and FullText.
The following table shows the key types of retrieval properties of these Omnidex index types.
Omnidex | QuickText | FullText | |
---|---|---|---|
Criteria | Yes | Yes | Yes |
Counts | Yes | Yes | Yes |
Sum, Average, Min, Max | Yes | No | No |
Table Joins | Yes | No | No |
Group By | Yes | No | No |
Order By | Yes | No | No |
Geographic Searches | Yes | No | No |
Textual Searches | No | Yes | Yes |
Proximity Searches | No | No | Yes |
Relevancy Scoring | No | No | Yes |
Indexing Overhead | Low | Low | High |
In addition to the three basic Omnidex Index Types: Omnidex, QuickText, and FullText, there is a Custom Index Type that can be used to specify advanced indexing options.
Options
bitmap
Number | Data Type | Length | |
---|---|---|---|
100 | CHAR(ACTER) | # of characters | |
201 | C STRING | # of characters | |
1700 | VARCHAR | # of characters | |
1800 | CLOB | # of characters | |
102 | NCHAR | # of characters * 2 | |
202 | NC STRING | # of characters | |
1701 | NVARCHAR | # of characters | |
1801 | NCLOB | (# of characters | |
1799 | OMNIDEX VARCHAR | # of characters | |
1899 | OMNIDEX CLOB | # of characters | |
301 | [SIGNED] TINYINT | 1 | |
401 | UNSIGNED TINYINT | 1 | |
302 | [SIGNED] SMALLINT | 2 | |
402 | UNSIGNED SMALLINT | 2 | |
300 | [SIGNED] INTEGER | 1, 2, 4, 8 | |
400 | UNSIGNED INTEGER | 1, 2, 4, 8 | |
303 | [SIGNED] BIGINT | 8 | |
403 | UNSIGNED BIGINT | 8 | |
600 | FLOAT | 4, 8 | |
605 | DOUBLE | 8 | |
1000 | DATE | 10 | |
1007 | ODBC DATE | 6 | |
1009 | DB2 DATE | 6 | |
1100 | INFORMIX DATE | 4 | |
1101 | ASCII DATE | 6, 8 | 6, 8 (optional, default 8) |
1102 | OMNIDEX DATE | 1 - 4 | 2 - 8 (optional, default 8) |
1199 | TIME | 11 | |
1200 | ODBC TIME | 6 | |
1202 | DB2 | TIME 6 | |
1207 | OMNIDEX TIME | 1 - 4 | 2 - 8 (optional, default 8) |
1208 | DATETIME | 22 | |
1206 | ORACLE DATETIME | 7 | |
1205 | ODBC DATETIME | 6 | |
1208 | DB2 DATETIME | 16 | |
1206 | INFORMIX DATETIME | 24 | |
1205 | C DATETIME | 4 | |
1299 | OMNIDEX DATETIME | 1 - 8 | 2 - 16 (optional, default 16) |
1900 | BLOB | # of bytes | |
1999 | OMNIDEX BLOB | # of bytes |