The PARTITION statement reads a table and splits it into multiple sub partitions which can be useful when setting up an Omnidex grid.
PARTITION table_spec
[INTO n]
[BY <column_spec | expression>]
[IN path]
[ON [INSTANCE] instance_no]
[WITH options]
The output of the PARTITION statement is multiple files that are named as follows: table_name_<distinct_value>.dat.
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