This shows you the differences between two versions of the page.
|
dev:sql:statements:create_table:home [2010/05/13 00:28] 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 like the following example where Omnidex indexing is specified after the column_name and column_type. | ||
| - | |||
| - | <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 | ||
| - | ); | ||
| - | </code> | ||
| - | |||
| - | ===== 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}} | ||