This shows you the differences between two versions of the page.
dev:sql:statements:create_table:home [2010/05/13 00:27] tdo |
dev:sql:statements:create_table:home [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
- | ====== Omnidex SQL: CREATE TABLE ====== | ||
- | {{page>:sql_bar&nofooter&noeditbtn}} | ||
- | ===== Description ===== | ||
- | The CREATE TABLE is used to specify metadata to the Omnidex Environment or to alternatively create temporary tables in the underlying data base or temporary operating system files. | ||
- | |||
- | Note that unlike typical RDBMS CREATE TABLE statements, the Omnidex SQL CREATE TABLE statement only manipulates the Omnidex metadata layer and does not create underlying RDBMS tables. | ||
- | |||
- | Even when Omnidex is used on a collection of Operating System files (POSIX or Windows/DOS files) the CREATE TABLE statement does not create the underlying operating system file or purge an existing file specified in the PHYSICAL clause of the CREATE TABLE statement. | ||
- | |||
- | When the GLOBAL TEMPORARY, LOCAL TEMPORARY or TEMPORARY modifiers are used before the TABLE keyword, then Omnidex will attempt to create a temporary table in the underlying RDBMS or an operating system temporary files. These files will be deleted when the current Omnidex session that created the temporary table or file terminates. | ||
- | |||
- | ===== Typical Example ===== | ||
- | Most CREATE STATEMENTS will typically look as follows: | ||
- | |||
- | <code> | ||
- | |||
- | CREATE TABLE mytable | ||
- | PHYSICAL "mydir/mytable" | ||
- | ( | ||
- | user_id INTEGER OMNIDEX, | ||
- | region CHARACTER(2) OMNIDEX, | ||
- | full_name CHARACTER(30) QUICKTEXT, | ||
- | address CHARACTER(60) QUICKTEXT, | ||
- | city CHARACTER(30) QUICKTEXT, | ||
- | state CHARACTER(2) OMNIDEX | ||
- | ); | ||
- | | ||
- | |||
- | ===== Syntax ===== | ||
- | |||
- | <code> | ||
- | 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] | ||
- | </code> | ||
- | |||
- | ===== Discussion ===== | ||
- | |||
- | 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. | ||
- | |||
- | ===== Examples ===== | ||
- | ==== Simple CREATE TABLE with no Omnidex Indexing specified ==== | ||
- | <code> | ||
- | 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"; | ||
- | </code> | ||
- | | ||
- | ==== CREATE TABLE with Omnidex Indexing specified inline with the columns ==== | ||
- | |||
- | Here is the same CREATE TABLE statement as above but with the Omnidex Index type specified for each column. | ||
- | |||
- | <code> | ||
- | 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"; | ||
- | </code> | ||
- | ==== CREATE TABLE with an Omnidex Composite Index ==== | ||
- | |||
- | 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. | ||
- | |||
- | <code> | ||
- | 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"; | ||
- | </code> | ||
- | |||
- | ==== Types of Omnidex Indexes ==== | ||
- | |||
- | 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 | ||
- | - Omnidex | ||
- | bitmap | ||
- | |||
- | ==== Column Data Types ==== | ||
- | ^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 | | ||
- | ==== Indexing Recommendations ==== | ||
- | |||
- | - Generally Omnidex indexes should be put on the Parent primary key and the child foreign key. | ||
- | - Create a composite index for group bys of multiple columns. | ||
- | - Create a composite index of all group by columns plus the aggregate columns. | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |