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:syntax [2010/07/07 21:02] els |
dev:sql:statements:create_table:syntax [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 8: | Line 8: | ||
| [[dev:sql:statements:create_table:home|Overview]] | **[[dev:sql:statements:create_table:syntax|Syntax]]** | [[dev:sql:statements:create_table:examples|Examples]] | [[dev:sql:statements:create_table:home|Overview]] | **[[dev:sql:statements:create_table:syntax|Syntax]]** | [[dev:sql:statements:create_table:examples|Examples]] | ||
| - | |||
| ===== Syntax ===== | ===== Syntax ===== | ||
| Line 86: | Line 85: | ||
| === OPTIONS options === | === OPTIONS options === | ||
| - | The OPTIONS clause provides options specific to the table. The primary use of the OPTIONS clause is to provide information about a delimited file or an Omnidex Standalone Table. The following article on [[dev:sql:statements:create_table:delimited|delimited files]] gives specific instructions for use of this OPTIONS clause. | + | The OPTIONS clause provides options specific to the table. The primary use of the OPTIONS clause is to provide information about a delimited file or an Omnidex Standalone Table. The following article on [[integration:rawdata:delimited:home|delimited files]] gives specific instructions for use of this OPTIONS clause. |
| - | === PHYSICAL <"filespec" | rdmbs_table> === | + | === PHYSICAL <"filespec" | rdbms_table> === |
| The PHYSICAL clause specifies the location of the data that comprises this table. The PHYSICAL clause varies according to the database type as follows: | The PHYSICAL clause specifies the location of the data that comprises this table. The PHYSICAL clause varies according to the database type as follows: | ||
| ^Database Type ^Description ^ | ^Database Type ^Description ^ | ||
| - | | FLATFILE | For fixed-length raw data files and delimited files, this clause contains one or more filenames or wildcard filesets, separated by commas. All of the files listed here will be sequentially combined to comprise the table. For Omnidex Standalone Tables (OST), this contains the name of OST file. Example: "dat/list1.dat,dat/list2.dat,dat/list3*.dat".| | + | | FILE | For fixed-length raw data files and delimited files, this clause contains one or more filenames or wildcard filesets, separated by commas. All of the files listed here will be sequentially combined to comprise the table. For Omnidex Standalone Tables (OST), this contains the name of OST file. Example: "dat/list1.dat,dat/list2.dat,dat/list3*.dat".| |
| | ORACLE | The PHYSICAL clause contains the name of the Oracle table or view. The table can be qualified by the owner as necessary. Example: "OWNER.TABLE".| | | ORACLE | The PHYSICAL clause contains the name of the Oracle table or view. The table can be qualified by the owner as necessary. Example: "OWNER.TABLE".| | ||
| | SQLSERVER | The PHYSICAL clause contains the SQL Server table or view. The table can be qualified by the database as necessary. Example: "DB.TABLE".| | | SQLSERVER | The PHYSICAL clause contains the SQL Server table or view. The table can be qualified by the database as necessary. Example: "DB.TABLE".| | ||
| Line 111: | Line 110: | ||
| create table "HOUSEHOLDS" | create table "HOUSEHOLDS" | ||
| node "NODE01" | node "NODE01" | ||
| - | physical "dat\households01.dat" | + | physical "dat/households01.dat" |
| partition_by "STATE in ('NY','PR','MA','RI','ME','NH','VT','CT','NJ', | partition_by "STATE in ('NY','PR','MA','RI','ME','NH','VT','CT','NJ', | ||
| 'PA','DE','DC','MD','VA','WV','NC','SC','GA', | 'PA','DE','DC','MD','VA','WV','NC','SC','GA', | ||
| Line 117: | Line 116: | ||
| ZIP between '01000' and '45999'" | ZIP between '01000' and '45999'" | ||
| node "NODE02" | node "NODE02" | ||
| - | physical "dat\households02.dat" | + | physical "dat/households02.dat" |
| partition_by "STATE in ('IN','MI','IA','WI','MN','SD','ND','MT','IL', | partition_by "STATE in ('IN','MI','IA','WI','MN','SD','ND','MT','IL', | ||
| 'MO','KS','NE','LA','AR','OK','TX','TX','CO', | 'MO','KS','NE','LA','AR','OK','TX','TX','CO', | ||
| Line 125: | Line 124: | ||
| </code> | </code> | ||
| - | The following article on [[admin:grids:home|Omnidex Grids]] gives specific instructions for partitioning tables. | + | The following article on [[admin:features:grids:home|Omnidex Grids]] gives specific instructions for partitioning tables. |
| === INDEX_MAINTENANCE < API | DBMS > === | === INDEX_MAINTENANCE < API | DBMS > === | ||
| Line 198: | Line 197: | ||
| Expression-based columns are the basis for creating expression-based indexes. If an Omnidex index is installed on the expression-based column, then this equates to an expression-based index. If the same expression is recognized in the SQL SELECT statement, it will recognize that this equates to the expression-based index and will optimize the query accordingly. | Expression-based columns are the basis for creating expression-based indexes. If an Omnidex index is installed on the expression-based column, then this equates to an expression-based index. If the same expression is recognized in the SQL SELECT statement, it will recognize that this equates to the expression-based index and will optimize the query accordingly. | ||
| - | The following article on [[admin:optimization:exprcolumns|expression-based columns and indexes]] describes these optimizations in more detail. | + | The following article on [[admin:features:expressioncols:home|expression-based columns and indexes]] describes these optimizations in more detail. |
| ==== constraint_specification ==== | ==== constraint_specification ==== | ||
| Line 250: | Line 249: | ||
| A table may have multiple distinct key constraints. | A table may have multiple distinct key constraints. | ||
| - | The following article on [[admin:grids:home|Omnidex Grids]] describes the use of distinct constraints in more detail. | + | The following article on [[admin:features:grids:home|Omnidex Grids]] describes the use of distinct constraints in more detail. |
| === FOREIGN KEY === | === FOREIGN KEY === | ||
| Line 262: | Line 261: | ||
| == PREJOIN == | == PREJOIN == | ||
| - | The PREJOIN keyword indicates that this table should be pre-joined within the Omnidex Indexes based on this foreign constraint. The following article on [[admin:indexing:advanced:prejoined|Omnidex Pre-joined Indexes]] describes the use of distinct constraints in more detail. | + | The PREJOIN keyword indicates that this table should be pre-joined within the Omnidex Indexes based on this foreign constraint. |
| ==== index_specification ==== | ==== index_specification ==== | ||
| Line 305: | Line 304: | ||
| [ PROXIMITY ] | [ PROXIMITY ] | ||
| [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | [ < CASE_INSENSITIVE | CASE_SENSITIVE > ] | ||
| - | [ EXCLUDED_WORDS ] | ||
| [ PHONETIC ] | [ PHONETIC ] | ||
| - | [ PREJOIN table ] | + | [ PREJOIN table_name ] |
| [ < RECORD_SPECIFIC | RECORD_COMPLEX > ] | [ < RECORD_SPECIFIC | RECORD_COMPLEX > ] | ||
| Line 320: | Line 318: | ||
| An Omnidex Index is limited to a maximum of 240 bytes of data. | An Omnidex Index is limited to a maximum of 240 bytes of data. | ||
| - | The following article on [[admin:indexing:indexes|Omnidex Index Types]] describes Omnidex Indexes in more detail. | + | The following article on [[admin:indexing:indexes:types|Omnidex Index Types]] describes Omnidex Indexes in more detail. |
| === QUICKTEXT INDEX === | === QUICKTEXT INDEX === | ||
| Line 330: | Line 328: | ||
| A QuickText Index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | A QuickText Index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | ||
| - | The following article on [[admin:indexing:indexes|Omnidex Index Types]] describes QuickText Indexes in more detail. | + | The following article on [[admin:indexing:indexes:types|Omnidex Index Types]] describes QuickText Indexes in more detail. |
| === FULLTEXT INDEX === | === FULLTEXT INDEX === | ||
| Line 340: | Line 338: | ||
| A FullText Index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | A FullText Index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | ||
| - | The following article on [[admin:indexing:indexes|Omnidex Index Types]] describes FullText Indexes in more detail. | + | The following article on [[admin:indexing:indexes:types|Omnidex Index Types]] describes FullText Indexes in more detail. |
| === CUSTOM INDEX === | === CUSTOM INDEX === | ||
| Line 350: | Line 348: | ||
| A Custom Index is limited to 240 bytes unless the Keywording option is used. If the Keywording option is used, the index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | A Custom Index is limited to 240 bytes unless the Keywording option is used. If the Keywording option is used, the index is limited to a 16 MB of data if applied to the last column in the table, and 64 KB if applied to a middle column in the table. | ||
| - | The following article on [[admin:indexing:indexes|Omnidex Index Types]] describes Custom Indexes in more detail. | + | The following article on [[admin:indexing:indexes:types|Omnidex Index Types]] describes Custom Indexes in more detail. |
| === NATIVE INDEX === | === NATIVE INDEX === | ||
| Line 367: | Line 365: | ||
| == BITMAP == | == BITMAP == | ||
| + | |||
| + | The BITMAP option causes an Omnidex or Custom index to be stored in a bitmap format rather than a B-tree format. The bitmap format is more efficient for indexes that have fewer than 32 distinct values. The B-tree format is more efficient for indexes that have greater than 32 distinct values. | ||
| + | |||
| + | The BITMAP option is frequently used on low-cardinality columns such as Yes/No flags, or columns like GENDER or MARITAL_STATUS. Using a Bitmap Index on these types of columns can significantly improve query performance. | ||
| + | |||
| + | Bitmap Indexes are not maintained when INSERT, DELETE and UPDATE statements are processed. Bitmap Indexes are rebuilt when an UPDATE INDEXES command is issued. | ||
| == STANDALONE == | == STANDALONE == | ||
| + | |||
| + | The STANDALONE option causes a non-keyworded Custom index to reside independently from the other indexes in the table. Standalone indexes can be built individually, avoiding the costs of updating all of the indexes for a given table. | ||
| == KEYWORDING == | == KEYWORDING == | ||
| + | |||
| + | The KEYWORDING option causes the data in the index to be parsed based on spaces and punctuation marks. This allows each word to be used as independent criteria. For example, the criteria "INFORMATION" would locate a row with the value of "DYNAMIC INFORMATION SYSTEMS CORPORATION". | ||
| + | |||
| + | The KEYWORDING option automatically implies the CASE_INSENSITIVE option, unless specifically overridden using the CASE_SENSITIVE option. This means that the criteria "INFORMATION" would locate a row with the value of "Dynamic Information Systems Corporation". | ||
| + | |||
| + | The KEYWORDING option does not track the location of each word in each column. The PROXIMITY option is required to track the location of each word. The KEYWORDING option simply records that the keyword was present for that row. The criteria "John Doe" would locate both a row containing "John Q. Doe" and a row containing "Doe, John Q.". | ||
| + | |||
| + | The KEYWORDING option has substantially less overhead than the PROXIMITY option and is recommended when performance is important. The KEYWORDING option is commonly used for smaller text fields such as name, address, descriptions, and brief comments. The PROXIMITY option is more commonly used on large text fields containing entire articles. | ||
| == PROXIMITY == | == PROXIMITY == | ||
| + | |||
| + | The PROXIMITY option causes the data in the index to be parsed based on spaces and punctuation marks, and also causes location information about each keyword to be recorded. This allows more sophisticated queries that compare the location of one keyword with another. The PROXIMITY option enables the use of the NEAR, BEFORE and AFTER operators, as well as the phrase searches. The PROXIMITY option also enables the use of the $SCORE and $CONTEXT functions. | ||
| + | |||
| + | The PROXIMITY option has substantially more overhead than the KEYWORDING option. The KEYWORDING option is commonly used for smaller text fields such as name, address, descriptions, and brief comments. The PROXIMITY option is more commonly used on large text fields containing entire articles. | ||
| + | |||
| + | The following article on [[admin:indexing:text:proximity|Omnidex Proximity Searches]] provides more details on performing proximity searches. | ||
| == < CASE_INSENSITIVE | CASE_SENSITIVE > == | == < CASE_INSENSITIVE | CASE_SENSITIVE > == | ||
| - | == EXCLUDED_WORDS == | + | The CASE_INSENSITIVE and CASE_SENSITIVE options cause the data in the index to be treated in either a case sensitive or a case insensitive manner. |
| == PHONETIC == | == PHONETIC == | ||
| - | == PREJOIN table == | + | The PHONETIC option causes index values to be indexed along with their phonetic equivalents. This option is required for performing phonetic searches using the $CONTAINS function in SELECT statements. |
| + | |||
| + | == PREJOIN table_name == | ||
| + | |||
| + | The PREJOIN option causes this index to be pre-joined to //table_name//. This option is used in conjunction with the RECORD_SPECIFIC and RECORD_COMPLEX options to determine how the index should be pre-joined. If neither the RECORD_SPECIFIC or RECORD_COMPLEX option is used, the RECORD_SPECIFIC option is used by default. | ||
| == < RECORD_SPECIFIC | RECORD_COMPLEX > == | == < RECORD_SPECIFIC | RECORD_COMPLEX > == | ||
| + | |||
| + | The RECORD_SPECIFIC and RECORD_COMPLEX options are used to determine how the index should be pre-joined to the table named in the PREJOIN option. | ||
| ==== index_group_specification ==== | ==== index_group_specification ==== | ||
| - | INDEX GROUP [ ( owner ) ] group ( index [, index ... ] ) | + | INDEX GROUP [ ( owner_table_name ) ] group_name ( index_name [, index_name ... ] ) |
| + | === INDEX GROUP ( owner_table_name ) group_name === | ||
| + | |||
| + | The INDEX GROUP clause is used to cause multiple indexes to be processed as a single indexes. When multiple indexes are grouped together, a search against any member of the group will automatically search the other members of the group. For example, if the columns ADDRESS1 and ADDRESS2 are combined into an Index Group, then a search for "MAIN STREET" will locate rows that have those two columns in either or both columns. | ||
| + | |||
| + | Index Groups can be created across multiple pre-joined tables; however, doing so requires using the CREATE INDEX GROUP statement. | ||
| + | |||
| + | |||
| + | ====== Additional Resources ====== | ||
| + | |||
| See also: | See also: | ||
| * [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | * [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | ||
| Line 396: | Line 431: | ||
| Articles: | Articles: | ||
| - | * [[admin:indexing:home|Indexing Strategies]] | + | * [[admin:indexing:home|Omnidex Indexing]] |
| - | * [[dev:appendix:articles:dbspe|Database-specific issues]] | + | * [[integration:home|Database-specific issues]] |
| - | * [[admin:optimization:rollups:home|Rollup Tables]] | + | * [[admin:features:rollups:home|Rollup Tables]] |
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||