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]

This example will partition a table into multiple sub tables for each state. Note that the filename suffix is the distinct state value.

> partition households by state ;
Partitioned 1,909 rows from HOUSEHOLDS into 52 partitions
> dir
HOUSEHOLDS_AK.dat
HOUSEHOLDS_AL.dat
HOUSEHOLDS_AR.dat
HOUSEHOLDS_AZ.dat
HOUSEHOLDS_CA.dat
HOUSEHOLDS_CO.dat
HOUSEHOLDS_CT.dat
HOUSEHOLDS_DC.dat
HOUSEHOLDS_DE.dat
HOUSEHOLDS_FL.dat
HOUSEHOLDS_GA.dat
HOUSEHOLDS_HI.dat
HOUSEHOLDS_IA.dat
HOUSEHOLDS_ID.dat
HOUSEHOLDS_IL.dat
HOUSEHOLDS_IN.dat
HOUSEHOLDS_KS.dat
HOUSEHOLDS_KY.dat
HOUSEHOLDS_LA.dat
…

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