This shows you the differences between two versions of the page.
|
dev:sql:statements:partition:home [2010/07/08 23:04] tdo |
dev:sql:statements:partition:home [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ====== Omnidex SQL: PARTITION ====== | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | ===== Description ===== | ||
| - | 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. | ||
| - | ===== Syntax ===== | ||
| - | <code> | ||
| - | PARTITION table_spec | ||
| - | [INTO n] | ||
| - | [BY <column_spec | expression>] | ||
| - | [IN path] | ||
| - | [ON [INSTANCE] instance_no] | ||
| - | [WITH options] | ||
| - | </code> | ||
| - | ===== Discussion ===== | ||
| - | |||
| - | |||
| - | ===== 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. | ||
| - | <code> | ||
| - | > 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 | ||
| - | … | ||
| - | </code> | ||
| - | ==== 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. | ||
| - | <code> | ||
| - | > 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 | ||
| - | </code> | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||