This shows you the differences between two versions of the page.
|
dev:sql:statements:create_table:examples [2010/07/06 17:39] els created |
dev:sql:statements:create_table:examples [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 ====== | ||
| - | |||
| - | **[[dev:sql:statements:create_table:home|Description]]** -> [[dev:sql:statements:create_table:syntax|Syntax]] -> [[dev:sql:statements:create_table:examples|Examples]] | ||
| - | |||
| - | ===== Examples ===== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | ===== Typical Example ===== | ||
| - | |||
| - | Most CREATE TABLE 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.dat" | ||
| - | ( | ||
| - | 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> | ||
| - | |||
| - | ===== 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 ^ Custom ^ | ||
| - | ^ 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. | ||
| - | ==== Omnidex Datatypes ==== | ||
| - | |||
| - | === Standard Datatypes === | ||
| - | |||
| - | Omnidex supports the following standard datatypes: | ||
| - | |||
| - | ^ Datatype ^ Description ^ | ||
| - | | CHARACTER(//n//) | Space-filled character string of //n// characters | | ||
| - | | CHAR(//n//) | Synonym for CHARACTER(//n//) | | ||
| - | | C STRING(//n//) | Null-terminated character string of //n// characters | | ||
| - | | VARCHAR(//n//)* | String of max //n// characters, with length variable | | ||
| - | | CLOB(//n//)* | Character large object of max //n// characters, with length variable | | ||
| - | | [SIGNED] TINYINT | 1-byte, signed integer ( -128 to 127 ) | | ||
| - | | UNSIGNED TINYINT | 1-byte, unsigned integer ( 0 to 255 ) | | ||
| - | | [SIGNED] SMALLINT | 2-byte, signed integer ( -32768 to 32767 ) | | ||
| - | | UNSIGNED SMALLINT | 2-byte, unsigned integer ( 0 to 65535 ) | | ||
| - | | [SIGNED] INTEGER | 4-byte, signed integer ( 2147483648 to 2147483647 ) | | ||
| - | | UNSIGNED INTEGER | 4-byte, unsigned integer ( 0 to 4294967295 ) | | ||
| - | | [SIGNED] BIGINT | 8-byte, signed integer ( %%-2^63 to 2^63-1%% ) | | ||
| - | | UNSIGNED BIGINT | 8-byte, unsigned integer ( %%0 to 2^64-1%% ) | | ||
| - | | FLOAT | IEEE four-byte, single-precision floating point | | ||
| - | | DOUBLE | IEEE eight-byte, double-precision floating point | | ||
| - | | DATE | 10-byte ANSI date | | ||
| - | | TIME | 11-byte ANSI time | | ||
| - | | DATETIME | 24-byte ANSI datetime | | ||
| - | | ASCII DATE | 8-byte ASCII date ( YYYYMMDD ) | | ||
| - | | ASCII DATE(6) | 6-byte ASCII date ( YYMMDD ) | | ||
| - | | ODBC DATE | 6-byte ODBC proprietary date | | ||
| - | | ODBC TIME | 6-byte ODBC proprietary time | | ||
| - | | ODBC DATETIME | 6-byte ODBC proprietary datetime | | ||
| - | | ORACLE DATE | 7-byte Oracle proprietary date | | ||
| - | | ORACLE TIME | 7-byte Oracle proprietary time | | ||
| - | | ORACLE DATETIME | 7-byte Oracle proprietary datetime | | ||
| - | | OMNIDEX DATE(//n//) | Omnidex proprietary date supporting //n// digits of YYYYMMDD | | ||
| - | | OMNIDEX TIME(//n//) | Omnidex proprietary time supporting //n// digits of HHMMSSNN | | ||
| - | | OMNIDEX DATETIME(//n//) | Omnidex proprietary datetime supporting //n// digits of YYYYMMDDHHMMSSNN | | ||
| - | | * Omnidex recommends use of the CHARACTER and C STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. || | ||
| - | |||
| - | === National Character Datatypes === | ||
| - | |||
| - | Omnidex also provides partial support for National Character Datatypes (sometimes called | ||
| - | Unicode datatypes or wide characters). The Omnidex SQL Engine will process these | ||
| - | datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these | ||
| - | datatypes. | ||
| - | |||
| - | ^ Datatype ^ Description ^ | ||
| - | | NATIONAL CHARACTER(//n//) | Space-filled character string of //n// characters | | ||
| - | | NATIONAL CHAR(//n//) | Synonym for CHARACTER(//n//) | | ||
| - | | NATIONAL C STRING(//n//) | Null-terminated character string of //n// characters | | ||
| - | | NATIONAL VARCHAR(//n//)* | String of max //n// characters, with length variable | | ||
| - | | NATIONAL CLOB(//n//)* | Character large object of max //n// characters, with length variable | | ||
| - | | * Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL C STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable. || | ||
| - | |||
| - | ==== 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. | ||
| - | - Create a composite index of group bys in order of reduced cardinality | ||
| - | - consider a catch all index of all group by and metric columns | ||
| - | - add a foreign key to the composite index when grouping on a column in the snowflaked table. | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||