Omnidex SQL: CREATE TABLE

Overview → Syntax → Discussion → Examples

CREATE [ < GLOBAL | LOCAL > ] TEMPORARY ] TABLE table
    [ NODE node]
        [ OPTIONS "options" ] 
        [ PHYSICAL < "filespec" | rdbms_table > ]
        [ DATA_CACHING < DYNAMIC | NONE > ]
        [ PARTITION_BY "criteria" ]
        [ INDEX_MAINTENANCE < API | DBMS > ]
    ( 
        column_specification,
        [ constraint_specification, ]
        [ index_specification, ]
        [ index_group_specification ]
    )
    [ AS statement ]
    [ IN filename ]
    [ WITH options ]

The table_name uniquely identifies the table within this database. It may be up to 32 characters long, and can contain letters, digits and the following special characters: ! @ # $ % ^ _. It must begin with a letter and is case-insensitive. The table_name must be unique within the database. If table_name is a SQL reserved word, enclose it in double quotation marks.

If you will have more than one database within this environment, consider having table names be unique across all databases. This prevents developers from having to qualify the table with the database name, using the syntax “MYDATABASE”.“MYTABLE”.

create table                 "LIST"
  ...
  in                         "list.xml"

The optional NODE clause is used to configure an Omnidex Grid. Nodes that were previously declared in the CREATE ENVIRONMENT statement can be referenced here, and the table configuration can be named for each node. The NODE clause is only required for tables within an Omnidex Grid that are partitioned and distributed across multiple nodes. If not using an Omnidex Grid, the NODE clause can be skipped and the underlying configuration can be specified for the entire table.

For simple environments, the table configuration does not require NODE clauses:

create table                 "LIST"
 physical                    "dat/list.dat"
 (
   column "NAME"             CHAR(50),
   column "ADDRESS1"         CHAR(50),
   column "ADDRESS2"         CHAR(50),
 )
 in                          "list.xml";

For Omnidex Grid environments, the table requires NODE clauses:

create table                 "LIST"
 node "GRID01"
  physical                    "dat/list1.dat"
 node "GRID02"
  physical                    "dat/list2.dat"
 node "GRID03"
  physical                    "dat/list3.dat"
 node "GRID04"
  physical                    "dat/list4.dat"
 node "GRID05"
  physical                    "dat/list*.dat"
 (
   column "NAME"             CHAR(50),
   column "ADDRESS1"         CHAR(50),
   column "ADDRESS2"         CHAR(50),
 )
 in                          "list.xml";

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 delimited files gives specific instructions for use of this OPTIONS clause.

The PHYSICAL clause specifies the location of the data that comprises this table. The PHYSICAL clause varies according to the database type as follows:

create table                 "LIST"
 physical                    "dat/list1.dat,dat/list2.dat,dat/list3*.dat"

The DATA_CACHING clause specifies whether this table is eligible for using Omnidex's internal hash tables for high-speed joins. A value of DYNAMIC makes this table eligible and a value of NONE makes this table ineligible. Tables are eligible by default.

    column_name  column_datatype 
        [ PHYSICAL "physical_name" ]
        [ USAGE usage ] 
        [ FORMAT format ]
        [ constraint_specification ] 
        [ index_specification ]
        [ AS expression ],

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 ]  > 

Normal index specification:

    [ < OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE > [INDEX] 
        ( < column | substring > [ , < column | substring > ... ] ) 
        indexing_options

Inlined index specification:

    [ < OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE > [INDEX] 
        indexing_options

indexing_options:

    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 > ]

  INDEX GROUP [ ( owner ) ] group ( index [, index ... ] ) 

See also:

• CREATE ENVIRONMENT
• CREATE DATABASE
• CREATE INDEX
• UPDATE INDEXES

Articles:

• Indexing Strategies
• Database-specific issues
• Rollup Tables