This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:statements:create_table:home [2010/07/04 05:28] els |
dev:sql:statements:create_table:home [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | + | |
| + | |||
| + | ^ Omnidex SQL Quicklinks ^^^^^ | ||
| + | |[[dev:sql:overview | Overview ]] | [[dev:sql:statements:home | Statements ]] | [[ dev:sql:functions:home | Functions ]] | [[ dev:sql:examples:home | Examples ]] | [[dev:sql:home | Quick Reference ]] | | ||
| + | |||
| ====== Omnidex SQL: CREATE TABLE ====== | ====== Omnidex SQL: CREATE TABLE ====== | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | + | |
| - | ===== Description ===== | + | **[[dev:sql:statements:create_table:home|Overview]]** | [[dev:sql:statements:create_table:syntax|Syntax]] | [[dev:sql:statements:create_table:examples|Examples]] |
| + | |||
| + | |||
| + | ===== Overview ===== | ||
| The CREATE TABLE statement declares a table in an Omnidex Environment File. Tables that are declared in an environment can then 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 CREATE TABLE statement declares a table in an Omnidex Environment File. Tables that are declared in an environment can then 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. | ||
| Line 9: | Line 17: | ||
| 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. Omnidex does support an exception to this rule for the creation of [[dev:sql:statements:create_table:temporary_tables|temporary tables]]. | 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. Omnidex does support an exception to this rule for the creation of [[dev:sql:statements:create_table:temporary_tables|temporary tables]]. | ||
| - | ==== Table Characteristics ==== | + | ===== Table Characteristics ===== |
| - | The CREATE TABLE statement declares the characteristics of the table. At a minimum, each table has a name and instructions for accessing the physical data. Omnidex supports configuration settings for each table, including settings to for [[dev:sql:statements:create_table:delimited_files | delimited files]], [[dev:sql:statements:create_table:data_cache | data caches ]] and [[dev:sql:statements:create_table:index_maintenance | index maintenance]]. Omnidex also provides [[dev:sql_staetments:create_table:rollup_tables | rollup tables]] which pre-aggregate another table in the database and automatically optimize queries against the table. | + | The CREATE TABLE statement declares the characteristics of the table. At a minimum, each table has a name and instructions for accessing the physical data. Omnidex supports configuration settings for each table, including settings for [[dev:sql:statements:create_table:delimited_files | delimited files]], [[dev:sql:statements:create_table:data_cache | data caches ]] and [[dev:sql:statements:create_table:index_maintenance | index maintenance]]. Omnidex also provides [[admin:features:rollups:home | rollup tables]] which pre-aggregate another table in the database and automatically optimize queries against the table. |
| - | ==== Columns Characteristics ==== | + | ===== Columns Characteristics ===== |
| The CREATE TABLE statement specifies the columns for a table. At a minimum, each column has a name and a datatype; however, it can also have other characteristics as well. Omnidex allows a column to have a different name than the one used in the underlying relational database. Omnidex allows the declaration of [[dev:sql:statements:create_table:column_usages | column usages]] that describe the purpose of the column. Omnidex also allows [[dev:sql:statements:create_table:expression_columns | expression-based columns]] that are the result of a SQL expression, even if it does not exist in the underlying database or data file. | The CREATE TABLE statement specifies the columns for a table. At a minimum, each column has a name and a datatype; however, it can also have other characteristics as well. Omnidex allows a column to have a different name than the one used in the underlying relational database. Omnidex allows the declaration of [[dev:sql:statements:create_table:column_usages | column usages]] that describe the purpose of the column. Omnidex also allows [[dev:sql:statements:create_table:expression_columns | expression-based columns]] that are the result of a SQL expression, even if it does not exist in the underlying database or data file. | ||
| - | ==== Table Constraints ==== | + | ===== Table Constraints ===== |
| The CREATE TABLE statement specifies the constraints for a table. The most common constraints are primary and foreign constraints, used to declare parent-child relationships between tables. Omnidex allows constraints to be declared on both relational tables and raw data files, allowing either to be described using a full relational model. Omnidex allows these constraints to be declared, but it does not verify or enforce these constraints. | The CREATE TABLE statement specifies the constraints for a table. The most common constraints are primary and foreign constraints, used to declare parent-child relationships between tables. Omnidex allows constraints to be declared on both relational tables and raw data files, allowing either to be described using a full relational model. Omnidex allows these constraints to be declared, but it does not verify or enforce these constraints. | ||
| - | ==== Omnidex Indexing ==== | + | ===== Omnidex Indexing ===== |
| Like most databases, Omnidex allows indexes to be declared using the CREATE INDEX statement. For convenience, Omnidex also allows indexes to be declared using the CREATE TABLE statement. In most applications, many (if not all) of the columns are indexed with Omnidex, so it is convenient to declare the indexing directly in the table specification. | Like most databases, Omnidex allows indexes to be declared using the CREATE INDEX statement. For convenience, Omnidex also allows indexes to be declared using the CREATE TABLE statement. In most applications, many (if not all) of the columns are indexed with Omnidex, so it is convenient to declare the indexing directly in the table specification. | ||
| + | ===== Inlining ===== | ||
| - | ==== Inlining ==== | + | Omnidex allows constraints and indexing to be declared alongside each column. With inlining, the constraint and indexing specification is provided as each column is declared. This is a convenience for simple installations where most constraints and indexes consist of a single column. |
| - | + | ||
| - | Omnidex allows constraints and indexing to be declared alongside each column. With [[dev:sql:statements:create_table:inlining | inlining]], the constraint and indexing specification is provided as each column is declared. This is a convenience for simple installations where most constraints and indexes consist of a single column. | + | |
| - | + | ||
| - | ===== Syntax ===== | + | |
| - | + | ||
| - | <code> | + | |
| - | CREATE [ < GLOBAL | LOCAL > ] TEMPORARY ] TABLE table | + | |
| - | [ NODE node] | + | |
| - | [ OPTIONS "options" ] | + | |
| - | [ PHYSICAL " ] | + | |
| - | [ AUTOFILTER "criteria" ] | + | |
| - | [ DATA_CACHING < cache_size | DYNAMIC | NONE > ] | + | |
| - | [ PARTITION_BY "criteria" ] | + | |
| - | [ INDEX_MAINTENANCE <API | DBMS> ] | + | |
| - | ( | + | |
| - | column_specification, | + | |
| - | [ constraint_specification, ] | + | |
| - | [ index_specification, ] | + | |
| - | [ index_group_specification ] | + | |
| - | ) | + | |
| - | [ AS statement ] | + | |
| - | [ IN filename ] | + | |
| - | [ WITH options ] | + | |
| - | + | ||
| - | </code> | + | |
| - | ==== column_specification ==== | + | |
| - | <code> | + | |
| - | column_name column_datatype | + | |
| - | [ PHYSICAL "physical_name" ] | + | |
| - | [ USAGE usage ] | + | |
| - | [ FORMAT format ] | + | |
| - | [ constraint_specification ] | + | |
| - | [ index_specification ] | + | |
| - | [ AS expression ], | + | |
| - | </code> | + | |
| - | ==== constraint_specification ==== | + | |
| - | + | ||
| - | Normal constraint definition: | + | |
| - | + | ||
| - | [ CONSTRAINT constraint ] | + | |
| - | < PRIMARY [ KEY ] ( column [, column ... ] ) | | + | |
| - | UNIQUE [ KEY ] ( column [, column ... ] ) | | + | |
| - | DISTINCT [ KEY ] ( column [, column ... ] ) | | + | |
| - | FOREIGN [ KEY ] ( column [, column ... ] ) | + | |
| - | REFERENCES table [ ( column [, column ... ] ) ] [ PREJOIN ] > | + | |
| - | + | ||
| - | Inlined constraint definition: | + | |
| - | + | ||
| - | [ CONSTRAINT constraint ] | + | |
| - | < PRIMARY [ KEY ] | | + | |
| - | UNIQUE [ KEY ] | | + | |
| - | DISTINCT [ KEY ] | | + | |
| - | FOREIGN [ KEY ] REFERENCES table [ ( column [, column ... ] ) ] [ PREJOIN ] > | + | |
| - | ==== index_specification ==== | + | |
| - | + | ||
| - | Normal index specification: | + | |
| - | + | ||
| - | <code> | + | |
| - | [ < OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE > [INDEX] | + | |
| - | ( < column | substring > [ , < column | substring > ... ] ) | + | |
| - | indexing_options | + | |
| - | </code> | + | |
| - | + | ||
| - | Inlined index specification: | + | |
| - | + | ||
| - | <code> | + | |
| - | [ < OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE > [INDEX] | + | |
| - | indexing_options | + | |
| - | </code> | + | |
| - | + | ||
| - | indexing_options: | + | |
| - | + | ||
| - | <code> | + | |
| - | Options available for OMNIDEX index: | + | |
| - | + | ||
| - | [ BITMAP ] | + | |
| - | [ STANDALONE ] | + | |
| - | [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | + | |
| - | + | ||
| - | Options available for QUICKTEXT indexes: | + | |
| - | + | ||
| - | [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | + | |
| - | + | ||
| - | Options available for FULLTEXT indexes: | + | |
| - | + | ||
| - | [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | + | |
| - | + | ||
| - | Options available for CUSTOM indexes: | + | |
| - | + | ||
| - | [ BITMAP ] | + | |
| - | [ STANDALONE ] | + | |
| - | [ KEYWORDING ] | + | |
| - | [ PROXIMITY ] | + | |
| - | [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | + | |
| - | [ EXCLUDED_WORDS ] | + | |
| - | [ PHONETIC ] | + | |
| - | [ PREJOIN table ] | + | |
| - | [ < RECORD_SPECIFIC | RECORD_COMPLEX > ] | + | |
| - | + | ||
| - | </code> | + | |
| - | ==== index_group_specification ==== | + | |
| - | + | ||
| - | INDEX GROUP [ ( owner ) ] group ( index [, index ... ] ) | + | |
| - | + | ||
| - | ===== 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. | + | |
| - | + | ||
| - | ===== 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 ==== | + | ====== Additional Resources ====== |
| + | See also: | ||
| + | * [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | ||
| + | * [[dev:sql:statements:create_database:home|CREATE DATABASE]] | ||
| + | * [[dev:sql:statements:create_index:home|CREATE INDEX]] | ||
| + | * [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | ||
| - | Here is the same CREATE TABLE statement as above but with the Omnidex Index type specified for each column. | + | Articles: |
| - | <code> | + | * [[admin:indexing:home|Indexing Strategies]] |
| - | create table "HOUSEHOLDS" | + | * [[dev:appendix:articles:dbspe|Database-specific issues]] |
| - | physical "dat\households*.dat" | + | * [[admin:features:rollups:home|Rollup Tables]] |
| - | ( | + | |
| - | "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 ==== | ||
| - | |||
| - | 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 | | ||
| - | | [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 | | ||
| - | |||
| - | ^ Datatype ^ Length ^ Description ^ | ||
| - | | VARCHAR |# of characters| | ||
| - | | CLOB |# of characters| | ||
| - | | NCHAR |# of characters * 2| | ||
| - | | NC STRING | # of characters | | ||
| - | | NVARCHAR | # of characters | | ||
| - | | NCLOB |(# of characters | | ||
| - | | 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}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||