This is an old revision of the document!
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