Physical Database Design

7.3 Syntax Examples 129

7.3 Syntax Examples

The following examples from DB2, Informix, and Oracle illustrate the syntax used by

the different vendors.

DB2 9

CREATE TABLE sales(sale_date DATE, customer INT, …)

PARTITION BY RANGE(sale_date)

(STARTING '1/1/2006' ENDING '3/31/2008',

STARTING '4/1/2006' ENDING '6/30/2008',

STARTING '7/1/2006' ENDING '9/30/2008',

STARTING '10/1/2006' ENDING '12/31/2012');

Example showing MINVALUE and excluding ENDING in each clause.

CREATE TABLE sales(sale_date DATE, customer INT, … )

PARTITION BY RANGE(sale_date)

(PART rem STARTING MINVALUE ENDING '1/1/2000' EXCLUSIVE,

PARTITION q1 STARTING '1/1/2006',

PARTITION q2 STARTING '4/1/2006',

PARTITION q3 STARTING '7/1/2006',

PARTITION q4 STARTING '10/1/2006'

ENDING '12/31/2012'); ...