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/15 08:27] tdo |
dev:sql:statements:create_table:syntax [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 14: | Line 14: | ||
| [ NODE node] | [ NODE node] | ||
| [ OPTIONS "options" ] | [ OPTIONS "options" ] | ||
| - | [ PHYSICAL < "filespec" | rdbms_table | "{environment_variable[text]}" > ] | + | [ PHYSICAL < "filespec" | rdbms_table > ] |
| [ DATA_CACHING < DYNAMIC | NONE > ] | [ DATA_CACHING < DYNAMIC | NONE > ] | ||
| [ PARTITION_BY "criteria" ] | [ PARTITION_BY "criteria" ] | ||
| Line 85: | 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" | rdbms_table> === | === PHYSICAL <"filespec" | rdbms_table> === | ||
| Line 92: | Line 92: | ||
| ^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 110: | 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 116: | 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 124: | 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 197: | 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 249: | 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 261: | 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 318: | 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 328: | 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 338: | 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 348: | 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 385: | Line 385: | ||
| 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. | 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. | ||
| - | |||
| - | The following article on [[admin:indexing:keywording:home|Omnidex Keywording]] provides more details on the rules for parsing Omnidex keywords. | ||
| == PROXIMITY == | == PROXIMITY == | ||
| Line 394: | Line 392: | ||
| 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 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:proximity:home|Omnidex Proximity Searches]] provides more details on performing proximity searches. | + | 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 > == | ||
| Line 407: | Line 405: | ||
| 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. | 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. | ||
| - | |||
| - | The following article on [[admin:indexing:advanced:prejoined|Omnidex Pre-joined Indexes]] describes the use of distinct constraints in more detail. | ||
| == < 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. | 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. | ||
| - | |||
| - | The following article on [[admin:indexing:advanced:prejoined|Omnidex Pre-joined Indexes]] describes the use of distinct constraints in more detail. | ||
| ==== index_group_specification ==== | ==== index_group_specification ==== | ||
| Line 437: | 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}} | ||