Omnidex SQL: CREATE TABLE

The CREATE TABLE is used to specify metadata to the Omnidex Environment or to alternatively create temporary tables in the underlying data base or temporary operating system files.

Note that unlike typical RDBMS CREATE TABLE statements, the Omnidex SQL CREATE TABLE statement only manipulates the Omnidex metadata layer and does not create underlying RDBMS tables.

Even when Omnidex is used on a collection of Operating System files (POSIX or Windows/DOS files) the CREATE TABLE statement does not create the underlying operating system file or purge an existing file specified in the PHYSICAL clause of the CREATE TABLE statement.

When the GLOBAL TEMPORARY, LOCAL TEMPORARY or TEMPORARY modifiers are used before the TABLE keyword, then Omnidex will attempt to create a temporary table in the underlying RDBMS or an operating system temporary files. These files will be deleted when the current Omnidex session that created the temporary table or file terminates.

The CREATE TABLE statement can only be issued after a CREATE ENVIRONMENT and CREATE DATABASE statements have been issued first or a connection has been made to an Omnidex Environment that already contains an ENVIRONMENT and a DATABASE.

Most CREATE STATEMENTS will typically look like the following example where Omnidex indexing is specified after the column_name and column_type.

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
  );

  CREATE TABLE table
/* table options specified before the column and constraint specifications */
      [NODE node]
      [OPTIONS “options”]
      [PHYSICAL “physical_file” | "filespec" | shell_command ]
      [AUTOFILTER “criteria” ]
      [DATA_CACHING < cache_size | DYNAMIC | NONE >]
      [PARTITION_BY “criteria” ]
      [INDEX_MAINTENANCE index_maintenance ]
      
/* prefix table options to create a temporary table inserted before TABLE keyword */
           [[ <GLOBAL | LOCAL> ] TEMPORARY ]

/* column specifications */
      ( 
         column_name    column_datatype   optional_omnidex_index

/* Omnidex Index Types */
   
            [< OMNIDEX | 
               QUICKTEXT | 
               FULLTEXT | 
               CUSTOM | 
               NATIVE> [ INDEX ] 

/* column options */
            [DEFAULT < literal | niladic-function | NULL >]
            [USAGE usage]
            [FORMAT format]
    [ , ]   /* separator between column and constraint definitions */

/* Inline Constraint Specifications - native indexes and Omnidex indexes */
          [CONSTRAINT constraint]
               <[NOT] NULL | UNIQUE [KEY] | DISTINCT [KEY] | PRIMARY [KEY] | [FOREIGN [KEY]] 
                   REFERENCES table(column[, column …]) [PREJOIN]>]

/* Inline Omnidex custom index options */
               [KEYWORDING] [PROXIMITY] [<CASE_INSENSITIVE | CASE_SENSITIVE>][STANDALONE]
               [BITMAP][EXCLUDED_WORDS][PHONETIC]
               [PREJOIN table][<RECORD_SPECIFIC | RECORD_COMPLEX>]]

/* Inline Omnidex pseudo or derived columns */          
         [AS “select_item”]

/* Separately defined Constraints and Omnidex Indexes */ 
    [[,] [CONSTRAINT constraint]
            <UNIQUE [KEY] (column [, column …]) |
           DISTINCT [KEY] (column [, column …]) |
           PRIMARY [KEY] (column [, column …]) |
           FOREIGN [KEY] (column [, column …]) 
               REFERENCES table(column [, column …] [PREJOIN]>]

    [[,] <OMNIDEX | QUICKTEXT | FULLTEXT | CUSTOM | NATIVE> [INDEX] index
      (<column | substring> [, <column | substring>…])
      [KEYWORDING] [PROXIMITY] [ <CASE_INSENSITIVE | CASE_SENSITIVE> ]
      [STANDALONE][BITMAP][EXCLUDED_WORDS][PHONETIC]
      [PREJOIN table][<RECORD_SPECIFIC | RECORD_COMPLEX>]]
/* place the column in an Omnidex index group */
      [[,] INDEX GROUP [(owner)]group (index, index [, index ...])
    )]
    [AS “sql_statement”]

/* Specifics the physical Omnidex Environment file- this is the same as in the 
/* CREATE DATABASE and CREATE TABLE statements.  This has to be the last clause */
    [< IN “filename” | ON [INSTANCE] instance >]   

    [WITH options]

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.

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";
 

Here is the same CREATE TABLE statement as above but with the Omnidex Index type specified for each column.

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"
 )
 in "simple.xml";
 

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.

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";
 

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.

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.

Options

1. Omnidex

bitmap

1. Generally Omnidex indexes should be put on the Parent primary key and the child foreign key.
2. Create a composite index for group bys of multiple columns.
3. Create a composite index of all group by columns plus the aggregate columns.
4. Create a composite index of group bys in order of reduced cardinality
5. consider a catch all index of all group by and metric columns
6. add a foreign key to the composite index when grouping on a column in the snowflaked table.