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:

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.

The PARTITION_BY clause is used in Omnidex Grids to declare the criteria used to partition this table into each node. Each node uses a different PARTITION_BY clause, with each node specifying the criteria for that node.

Partitioning criteria is limited to simple criteria consisting of equalities, IN clauses and BETWEEN clauses. Multiple criteria can be used; however they must be connected using AND operators.

create table          "HOUSEHOLDS"
 node "NODE01"
  physical            "dat\households01.dat"
  partition_by        "STATE in ('NY','PR','MA','RI','ME','NH','VT','CT','NJ',
                                 'PA','DE','DC','MD','VA','WV','NC','SC','GA',
                                 'FL','AL','TN','MS','KY','OH') and 
                       ZIP between '01000' and '45999'"
 node "NODE02"
  physical            "dat\households02.dat"
  partition_by        "STATE in ('IN','MI','IA','WI','MN','SD','ND','MT','IL',
                                 'MO','KS','NE','LA','AR','OK','TX','TX','CO',
                                 'WY','ID','UT','AZ','NM','NV','CA','HI','OR',
                                 'WA','AK') and 
                       ZIP between '46000' and '99999'"

The following article on Omnidex Grids gives specific instructions for partitioning tables.

The INDEX_MAINTENANCE clause is an optional clause that specifies how Omnidex indexes will be updated. The index_maintenance_type can be either API or DBMS.

• API - The indexes will be updated by Omnidex automatically as the database data is updated.
• DBMS - The indexes will be updated by relational database triggers in conjunction with the OdxAIM services.

This setting applies to this table, and overrides any setting made at the database level. If no setting is made, API is used as the 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