Omnidex SQL: PARTITION

The PARTITION statement reads a table and splits it into multiple sub partitions which can be useful when setting up an Omnidex grid. The output of the PARTITION statement is multiple files that are named as follows: table_name_<distinct_value>.dat.

PARTITION table_spec 
         [INTO n] 
         [BY <column_spec | expression>] 
         [IN path]
         [ON [INSTANCE] instance_no] 
         [WITH options]

The partition statement is used with criteria to split a table into multiple components.

The SQL CASE syntax can be used to specify the suffix used when the PARTITION statement creates the component files.

> partition companies by 
    "case when state in ('CA','CO','TX') then '01' 
          when state in ('NY','FL','WA') then '02' 
          else '03' end" ;
Partitioned 31,548 rows from COMPANIES into 3 partitions