This shows you the differences between two versions of the page.
|
dev:sql:statements:create_table:home [2010/07/03 17:37] 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 statement declares a table in an Omnidex Environment File. Tables that are declared in an environment can be indexed and retrieved using SQL statements. An Omnidex table can point to an underlying Oracle, SQL Server, MySQL or ODBC table or view. An Omnidex table can also point to one or more raw data files that match the column layout of the table. | ||
| - | |||
| - | The basic CREATE TABLE statement records information about the underlying table or raw data files in the Omnidex Environment Catalog, but it does not actually create an underlying relational table or raw data file. Normally, the relational table or view file is created using the relational database's tools. In the case of raw data files, the files are usually received from a data provider, and application or an ETL tool. The CREATE TABLE statement simply stores meta-data about the table in the Omnidex Environment File. | ||
| - | |||
| - | === Creating Temporary Tables === | ||
| - | |||
| - | A variation of the CREATE TABLE statement does create an underlying table or raw data file. The CREATE TEMPORARY TABLE statement causes Omnidex to create a temporary table in the relational database. In the case of raw data files, the statement creates a new file to fulfill the table. This temporary table will be recognized by Omnidex for the duration of the connection, but will not be recognized by other connections or subsequent connections. It is incumbent on the application to drop the temporary table before terminating the connection; otherwise, the table will remain in the underlying database but will not be accessible. It will have to be dropped using the relational database's tools or the operating system. | ||
| - | |||
| - | === Declaring Columns === | ||
| - | |||
| - | Regular columns | ||
| - | Expression-based columns | ||
| - | Column datatypes | ||
| - | Column usages | ||
| - | |||
| - | === Table Constraints === | ||
| - | |||
| - | The CREATE TABLE statement allows the declaration of primary and foreign key constraints. These constraints establish parent-child relationships between tables and are essentially to declaring a relational data model in a database. Omnidex allows these table constraints to be declared, but it does not verify or enforce these constraints. | ||
| - | |||
| - | === Omnidex Indexing === | ||
| - | |||
| - | The CREATE TABLE statement can only be used in an Omnidex Environment File that already has a database declared with the [[dev:sql:statements:create_database:home | CREATE DATABASE ]] statement. | ||
| - | |||
| - | ===== 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.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> | ||
| - | ===== Syntax ===== | ||
| - | |||
| - | <code> | ||
| - | CREATE TABLE table_spec | ||
| - | /* 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 are enclosed with parenthesis. */ | ||
| - | ( | ||
| - | column_name column_datatype [ omnidex_index_spec ] | ||
| - | |||
| - | /* 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 */ | ||
| - | |||
| - | /* Inline Constraint Specifications - native indexes and Omnidex indexes */ | ||
| - | [CONSTRAINT constraint] | ||
| - | <[NOT] NULL | UNIQUE [KEY] | DISTINCT [KEY] | PRIMARY [KEY] | [FOREIGN [KEY]] | ||
| - | REFERENCES table(column[, column …]) [PREJOIN]>] | ||
| - | |||
| - | /* Inline Omnidex custom index options */ | ||
| - | [KEYWORDING] [PROXIMITY] [<CASE_INSENSITIVE | CASE_SENSITIVE>][STANDALONE] | ||
| - | [BITMAP][EXCLUDED_WORDS][PHONETIC] | ||
| - | [PREJOIN table][<RECORD_SPECIFIC | RECORD_COMPLEX>]] | ||
| - | |||
| - | /* Inline Omnidex pseudo or derived columns */ | ||
| - | [AS “select_item”] | ||
| - | |||
| - | /* Separately defined Constraints and Omnidex Indexes */ | ||
| - | [[,] [CONSTRAINT constraint] | ||
| - | < UNIQUE [KEY] (column_spec [, column_spec …]) | | ||
| - | DISTINCT [KEY] (column_spec [, column_spec …]) | | ||
| - | PRIMARY [KEY] (column_spec [, column_spec …]) | | ||
| - | FOREIGN [KEY] (column_spec [, column_spec …]) | ||
| - | REFERENCES table(column_spec [, column_spec …] [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 > ] ] | ||
| - | |||
| - | /* place the column in a previously declared Omnidex index group */ | ||
| - | [[,] INDEX GROUP [(owner)]group (index, index [, index ...]) | ||
| - | )] | ||
| - | |||
| - | [AS “sql_statement”] | ||
| - | |||
| - | /* Specifics the physical Omnidex Environment file. This has to be the last clause */ | ||
| - | [< 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 ^ 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. | ||
| - | |||
| - | |||
| - | ==== Column Data Types ==== | ||
| - | ^Number ^ Data Type ^ Length ^ | ||
| - | | CHAR(ACTER) |# of characters| | ||
| - | | C STRING |# of characters| | ||
| - | | VARCHAR |# of characters| | ||
| - | |CLOB|# of characters| | ||
| - | |NCHAR|# of characters * 2| | ||
| - | | NC STRING | # of characters | | ||
| - | | NVARCHAR | # of characters | | ||
| - | | NCLOB |(# of characters | | ||
| - | | OMNIDEX VARCHAR | # of characters | | ||
| - | | OMNIDEX CLOB | # of characters | | ||
| - | | [SIGNED] TINYINT | 1 | | ||
| - | | UNSIGNED TINYINT | 1 | | ||
| - | | [SIGNED] SMALLINT | 2 | | ||
| - | | UNSIGNED SMALLINT | 2 | | ||
| - | | [SIGNED] INTEGER | 1, 2, 4, 8 | | ||
| - | | UNSIGNED INTEGER | 1, 2, 4, 8 | | ||
| - | | [SIGNED] BIGINT | 8 | | ||
| - | | UNSIGNED BIGINT | 8 | | ||
| - | | FLOAT | 4, 8 | | ||
| - | | DOUBLE | 8 | | ||
| - | | DATE | 10 | | ||
| - | |ODBC DATE | 6 | | ||
| - | | DB2 DATE | 6 | | ||
| - | |INFORMIX DATE | 4 | | ||
| - | | ASCII DATE | 6, 8 | 6, 8 (optional, default 8) | | ||
| - | |OMNIDEX DATE | 1 - 4 | 2 - 8 (optional, default 8) | | ||
| - | | TIME | 11 | | ||
| - | | ODBC TIME | 6 | | ||
| - | | DB2 | TIME 6 | | ||
| - | | OMNIDEX TIME | 1 - 4 | 2 - 8 (optional, default 8) | | ||
| - | | DATETIME | 22 | | ||
| - | | ORACLE DATETIME | 7 | | ||
| - | | ODBC DATETIME | 6 | | ||
| - | | DB2 DATETIME | 16 | | ||
| - | | INFORMIX DATETIME | 24 | | ||
| - | | C DATETIME | 4 | | ||
| - | | OMNIDEX DATETIME | 1 - 8 | 2 - 16 (optional, default 16) | | ||
| - | | BLOB | # of bytes | | ||
| - | | 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. | ||
| - | - 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}} | ||