DRAFT

Omnidex SQL: PARTITION

Description

The PARTITION statement reads a table and splits it into multiple sub partitions which can be useful when setting up an Omnidex grid.

Syntax

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

Discussion

The output of the PARTITION statement is multiple files that are named as follows: table_name_<distinct_value>.dat.

Examples

Partition by Column

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
…

Partition with criteria and CASE

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
 
Back to top
dev/sql/statements/partition/home.txt · Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0